Google Apps Script实现Sheets多表指定数据自动汇总至主表

适用场景 #

• 针对包含4个工作表的企业数据存储Google Spreadsheet设计：1张MASTER SHEET汇总主表，3张SHEET1/SHEET2/SHEET3业务分表
• 自动提取3张分表中NAME、ID、TOTAL DELIVERIES三个指定列的数据，按ID匹配后汇总写入主表
• 适配单表上千行、上百列的大数据量场景，仅读取目标字段，执行效率高
• 运行结果和手动整理的预期效果完全一致

可直接运行的Apps Script代码 #

function syncDeliveryDataToMaster() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  // 工作表名称配置
  const SHEET_NAMES = {
    master: "MASTER SHEET",
    sub: ["SHEET1", "SHEET2", "SHEET3"]
  };
  // 需要从分表提取的目标字段
  const NEED_FIELDS = ["NAME", "ID", "TOTAL DELIVERIES"];

  const masterSheet = ss.getSheetByName(SHEET_NAMES.master);
  if (!masterSheet) throw new Error("找不到MASTER SHEET，请确认表名完全匹配");

  // 用ID做key存储全量汇总数据，避免重复匹配
  const dataPool = new Map();

  // 循环读取3个分表的目标数据
  SHEET_NAMES.sub.forEach((sheetName, sheetIndex) => {
    const currentSheet = ss.getSheetByName(sheetName);
    if (!currentSheet) throw new Error(`找不到${sheetName}，请确认表名完全匹配`);

    // 读取表头，自动定位目标字段所在列，不受列顺序调整影响
    const headerRow = currentSheet.getRange(1, 1, 1, currentSheet.getLastColumn()).getValues()[0];
    const colPos = {};
    NEED_FIELDS.forEach(field => {
      const position = headerRow.findIndex(cellVal => String(cellVal).trim() === field);
      if (position === -1) throw new Error(`${sheetName}中缺少${field}列，请检查表头`);
      colPos[field] = position;
    });

    // 读取分表所有有效数据行
    const allRows = currentSheet.getRange(2, 1, currentSheet.getLastRow() - 1, currentSheet.getLastColumn()).getValues();
    allRows.forEach(row => {
      const rowId = String(row[colPos["ID"]]).trim();
      if (!rowId) return; // 跳过ID为空的无效行
      const rowName = String(row[colPos["NAME"]]).trim();
      const deliveryCount = row[colPos["TOTAL DELIVERIES"]];

      // 初始化新ID对应的数据条目
      if (!dataPool.has(rowId)) {
        dataPool.set(rowId, {
          name: rowName,
          deliveryData: [null, null, null]
        });
      }
      // 把当前分表的配送量写入对应位置
      dataPool.get(rowId).deliveryData[sheetIndex] = deliveryCount;
    });
  });

  // 构造写入主表的二维数组，第一行是固定表头
  const writeData = [["NAME", "ID", "SHEET1", "SHEET2", "SHEET3"]];
  dataPool.forEach((item, id) => {
    writeData.push([item.name, id, ...item.deliveryData]);
  });

  // 清空主表旧内容后批量写入新数据，减少API调用提升速度
  masterSheet.clearContents();
  masterSheet.getRange(1, 1, writeData.length, writeData[0].length).setValues(writeData);
}

使用步骤 #

• 打开目标Google Spreadsheet，点击顶部菜单栏「扩展程序」-「Apps Script」进入脚本编辑器
• 删除编辑器中默认的空白代码，把上面的脚本完整粘贴进去
• 点击顶部保存按钮，给脚本项目任意命名（比如「配送数据汇总」）
• 点击运行按钮，首次执行会弹出Google账号授权提示，按页面指引完成授权即可
• 脚本执行完成后返回表格，查看MASTER SHEET即可得到和手动整理完全一致的汇总结果

*注意：首次运行触发授权时，Google会弹出「此应用未经验证」的安全提示，属于Apps Script个人项目的正常提示，点击「高级」-「继续前往（你的项目名）」即可完成授权，脚本不会上传任何你的表格数据。

脚本特性 #

• 自动识别列位置：就算后续分表调整列顺序，只要表头名称不变就可以正常运行
• 高执行效率：采用批量读写逻辑，避免逐单元格操作，单表上万行数据也可在数秒内完成
• 自动去重：以ID为唯一标识汇总数据，不会出现重复条目
• 自动清理旧数据：每次执行会清空主表旧内容，不会产生冗余重复数据

内容的提问来源于stack exchange，提问作者sohail khan