Google Sheets多标签页合并脚本适配新增/删除标签页需求咨询

你的原脚本仅在手动运行时生成固定范围的QUERY公式，无法感知标签页的新增/删除操作。下面提供两种解决方案，实现自动适配标签页变化的汇总功能：

方案一：自动触发更新（推荐） #

通过绑定onChange触发器，在标签页增删或表格结构变化时自动更新汇总公式。

完整脚本 #

function updateMainSheet() {
  const destinationSheetName = "main";
  const excludeSheetNames = [destinationSheetName, "Sheet2"]; // 按需修改需排除的标签页

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let targetSheet = ss.getSheetByName(destinationSheetName);
  
  // 如果目标汇总页不存在，自动创建
  if (!targetSheet) {
    targetSheet = ss.insertSheet(destinationSheetName);
    return;
  }

  // 动态收集所有需汇总的标签页数据范围
  const ranges = ss.getSheets().reduce((arr, sheet) => {
    const sheetName = sheet.getSheetName();
    if (!excludeSheetNames.includes(sheetName)) {
      // 转义标签页名称中的单引号，避免公式报错
      const escapedName = sheetName.replace(/'/g, "''");
      arr.push(`'${escapedName}'!A2:L`);
    }
    return arr;
  }, []).join(";");

  // 生成QUERY公式，无有效标签页时显示提示
  const formula = ranges.length > 0 
    ? `=QUERY({${ranges}},"select * where Col1 is not null",0)` 
    : "=IFERROR(\"无可用数据标签页\",)";

  // 清空旧数据并设置新公式
  targetSheet.clearContents();
  targetSheet.getRange("A1").setFormula(formula);
}

// 首次运行此函数创建触发器（仅需执行一次）
function createOnChangeTrigger() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // 删除重复触发器
  ScriptApp.getProjectTriggers().forEach(trigger => {
    if (trigger.getHandlerFunction() === "updateMainSheet" && trigger.getEventType() === ScriptApp.EventType.ON_CHANGE) {
      ScriptApp.deleteTrigger(trigger);
    }
  });

  // 创建新的onChange触发器
  ScriptApp.newTrigger("updateMainSheet")
    .forSpreadsheet(ss)
    .onChange()
    .create();
}

使用步骤 #

1. 打开你的Google表格，点击「工具」→「脚本编辑器」
2. 替换原有代码为上述脚本
3. 点击运行按钮执行createOnChangeTrigger，按提示完成授权
4. 之后新增/删除标签页时，汇总页main会自动更新数据

关键改进点 #

• 自动处理标签页名称含单引号的情况，避免公式语法错误
• 目标汇总页不存在时自动创建
• 无有效数据标签页时显示友好提示
• 通过onChange触发器实时响应标签页变化

方案二：手动触发更新 #

如果不需要完全自动，可添加自定义菜单，手动点击更新汇总数据：

在上述脚本基础上添加以下代码：

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu("数据汇总")
    .addItem("更新汇总数据", "updateMainSheet")
    .addToUi();
}

刷新表格后，顶部会出现「数据汇总」菜单，点击即可手动更新数据。

内容的提问来源于stack exchange，提问作者Sean Peter