Google Sheets多工作表自动汇总计算的App Script实现方法

基础场景适配约定 #

• Summary工作表规则：从第7行开始登记关联项目的工作表Tab名称，最多支持20个项目登记范围，脚本会自动读取该范围内的有效Tab名开展后续数据匹配，无需手动维护项目列表
• 项目工作表统一格式：第1列存储资源名称，表头行存储每周起始日期，有效数据统计范围固定为第36行至第61行；不同项目的周起始时间可独立设置，资源参与项目无强制跨项目要求，仅参与单个项目的资源也可正常纳入统计
• 特殊标记规则：资源行内Remote列值为Y时，对应工时条目判定为远程工时；Remote值为空时判定为非远程onsite工时

核心计算输出规则 #

脚本会自动生成四类按「资源+周」维度汇总的视图，具体规则如下：

• 拼接展示视图：汇总所有匹配项目的对应工时数据，同一资源同一周跨多个项目时，不同项目条目用换行分隔；远程工时时需在数值前添加R标识；无有效周日期、无有效资源名称的行自动跳过计算，不纳入统计

  场景示例：Mary在Project2的1月11日当周有1天onsite工时（Remote为空）、2天远程工时，最终拼接视图对应单元格会展示两个换行分隔的独立条目：

  Project2: 1
  Project2: R2
  

• 总工时视图：仅统计对应周下所有工时的数值总和，不附带所属项目名称、不区分远程/非远程属性
• 非远程工时视图：仅统计Remote标记为空的onsite工时数据，远程工时不纳入统计范围
• 非远程成本视图：在非远程工时的统计结果基础上，将对应工时值乘以预设的成本系数，输出最终成本值

核心脚本实现代码 #

以下代码可直接复制到Google Sheets的脚本编辑器中运行，支持根据实际列位置调整配置项：

// 全局配置项，可根据实际表格结构调整
const CONFIG = {
  SUMMARY_SHEET_NAME: "Summary",
  PROJECT_TAB_START_ROW: 7, // Summary表中项目Tab名的起始行
  MAX_PROJECT_COUNT: 20, // 最大支持登记的项目数量
  PROJECT_VALID_DATA_START_ROW: 36, // 项目表有效数据起始行
  PROJECT_VALID_DATA_END_ROW: 61, // 项目表有效数据结束行
  REMOTE_FLAG_COLUMN_OFFSET: 1, // Remote标记列相对工时列的偏移量，可按实际位置调整
  COST_RATE: 100 // 非远程工时对应的成本系数，可替换为实际取值
}

/**
 * 跨项目工时自动计算主函数
 * 自动识别Summary表登记的项目范围，完成四类视图的数值计算
 */
function calculateCrossProjectWorkload() {
  const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  
  // 第一步：读取Summary表登记的所有有效项目Tab名
  const summarySheet = activeSpreadsheet.getSheetByName(CONFIG.SUMMARY_SHEET_NAME);
  const projectNameRange = summarySheet.getRange(
    CONFIG.PROJECT_TAB_START_ROW,
    1,
    CONFIG.MAX_PROJECT_COUNT,
    1
  ).getValues();
  const validProjectTabs = projectNameRange.flat().filter(tabName => tabName.trim() !== "");

  // 初始化存储结构：{资源名: {周时间戳: {拼接展示内容, 总工时, 非远程工时, 非远程成本}}}
  const resourceWeekAggregate = {};

  // 第二步：遍历每个项目表，匹配对应工时数据
  validProjectTabs.forEach(tabName => {
    const projectSheet = activeSpreadsheet.getSheetByName(tabName);
    if (!projectSheet) return; // 跳过不存在的无效Tab
    const fullSheetData = projectSheet.getDataRange().getValues();
    // 提取表头行的所有周起始日期
    const weekHeaders = fullSheetData[0].slice(1).filter(colCell => colCell instanceof Date);

    // 遍历项目表的有效数据行
    for (let rowIndex = CONFIG.PROJECT_VALID_DATA_START_ROW - 1; rowIndex < CONFIG.PROJECT_VALID_DATA_END_ROW; rowIndex++) {
      const currentRow = fullSheetData[rowIndex];
      const resourceName = currentRow[0].trim();
      if (!resourceName) continue; // 跳过无有效资源名的行

      // 逐列匹配周维度的工时数据
      weekHeaders.forEach((weekDate, columnIndex) => {
        const workHourValue = currentRow[columnIndex + 1];
        if (!workHourValue) return; // 跳过无有效工时的单元格
        const isRemoteWork = currentRow[columnIndex + 1 + CONFIG.REMOTE_FLAG_COLUMN_OFFSET] === "Y";

        // 初始化资源-周维度的存储结构
        if (!resourceWeekAggregate[resourceName]) resourceWeekAggregate[resourceName] = {};
        const weekKey = weekDate.getTime();
        if (!resourceWeekAggregate[resourceName][weekKey]) {
          resourceWeekAggregate[resourceName][weekKey] = {
            displayList: [],
            totalWorkHours: 0,
            nonRemoteWorkHours: 0,
            nonRemoteCost: 0
          }
        }

        // 生成带R标识的远程工时展示条目
        const displayLabel = isRemoteWork ? `R${workHourValue}` : `${workHourValue}`;
        resourceWeekAggregate[resourceName][weekKey].displayList.push(
          `${tabName}: ${displayLabel}`
        );

        // 累计总工时
        resourceWeekAggregate[resourceName][weekKey].totalWorkHours += Number(workHourValue);

        // 累计非远程工时及对应成本
        if (!isRemoteWork) {
          const nonRemoteValue = Number(workHourValue);
          resourceWeekAggregate[resourceName][weekKey].nonRemoteWorkHours += nonRemoteValue;
          resourceWeekAggregate[resourceName][weekKey].nonRemoteCost = nonRemoteValue * CONFIG.COST_RATE;
        }
      })
    }
  })

  // 此处可追加逻辑，将resourceWeekAggregate的计算结果写入指定的输出工作表，完成四类视图的自动填充
  return resourceWeekAggregate;
}

代码使用说明：可根据表格实际的Remote标记列位置、成本系数取值调整CONFIG中的配置项，无需改动核心计算逻辑。

内容的提问来源于stack exchange，提问作者Joyce Brown