如何通过循环脚本批量更新基于同一模板的多个Spreadsheet

原代码核心问题 #

• 变量大小写不统一：声明的Template、Destination变量，后续调用时写成了小写的template、destination，直接触发未定义报错
• ID读取逻辑错误：map(SP =>[0])把读取到的表格ID全部替换成了0，完全无法识别目标表格
• 循环内未打开目标表格：拿到ID后没有调用SpreadsheetApp.openById()打开对应表格，所有操作还是指向索引表本身
• 语法错误：公式、工作表名称的字符串中间随意换行，触发语法解析失败
• 工作表名称大小写不匹配：复制生成的表名为Index，后续索引生成时调用的是小写的index，找不到对应工作表
• 大量重复代码：所有工作表复制逻辑重复编写，可维护性差

修正后可运行代码 #

function Update_Sheet_test() {
  // 配置项：可根据实际情况修改
  const TEMPLATE_ID = "1KAOgYHPHqFKhIr3Jb0Le0h9EB0VSt2Wos3l6dPY39P8";
  const ID_COLUMN = 6; // 目标ID存储在F列（第6列），如果存在其他列可修改
  const ID_START_ROW = 2; // 目标ID从第2行开始存储
  // 需要从模板复制的工作表配置：[表名, 是否隐藏]
  const COPY_SHEET_CONFIG = [
    ["Index", true],
    ["import_Sheet", true],
    ["Formulas", true],
    ["Main_Leaders", true],
    ["Proposed_Leaders", true],
    ["Assistant_Leaders", true],
    ["Group_Helpers", true],
    ["Occasional_Helpers", true],
    ["Trainee_Helpers", true],
    ["Volunteers", false],
    ["Safer Recruitment", false],
    ["SR overview by role", false],
    ["Dashboard_calculations", true],
    ["Dashboard", false]
  ];

  const templateSS = SpreadsheetApp.openById(TEMPLATE_ID);
  const indexSS = SpreadsheetApp.getActiveSpreadsheet();
  // 读取所有目标ID，过滤空值
  const targetIds = indexSS
    .getRange(ID_START_ROW, ID_COLUMN, indexSS.getLastRow() - ID_START_ROW + 1, 1)
    .getValues()
    .flat()
    .filter(id => id && id.toString().trim() !== "");

  // 循环处理每个目标表格
  targetIds.forEach(targetId => {
    try {
      const destSS = SpreadsheetApp.openById(targetId.trim());

      // 1. 删除目标表内除Setup Sheet外的所有工作表
      const oldSetupSheet = destSS.getSheetByName("Setup Sheet");
      if (oldSetupSheet) oldSetupSheet.showSheet(); // 避免隐藏状态删除报错
      const allSheets = destSS.getSheets();
      allSheets.forEach(sheet => {
        if (sheet.getSheetName() !== "Setup Sheet") {
          destSS.deleteSheet(sheet);
        }
      });

      // 2. 处理Setup Sheet：保留原有D12、G12数值
      const templateSetup = templateSS.getSheetByName("Setup Sheet");
      const newSetupTemp = templateSetup.copyTo(destSS);
      // 复制原有数值到新的Setup Sheet
      if (oldSetupSheet) {
        oldSetupSheet.getRange("D12").copyTo(newSetupTemp.getRange("D12"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
        oldSetupSheet.getRange("G12").copyTo(newSetupTemp.getRange("G12"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
        // 删除旧Setup Sheet，重命名新表
        destSS.deleteSheet(oldSetupSheet);
      }
      newSetupTemp.setName("Setup Sheet");
      newSetupTemp.hideSheet();

      // 3. 批量复制其余模板工作表
      COPY_SHEET_CONFIG.forEach(([sheetName, isHidden]) => {
        const templateSheet = templateSS.getSheetByName(sheetName);
        const copiedSheet = templateSheet.copyTo(destSS);
        copiedSheet.setName(sheetName);
        if (isHidden) copiedSheet.hideSheet();
      });

      // 4. 设置Volunteers表的公式
      const volunteerSheet = destSS.getSheetByName("Volunteers");
      volunteerSheet.getRange("D6:D6").setFormula('=ArrayFormula(If(G6:G="","",ADDRESS(MATCH(F6:F,\'Safer Recruitment\'!E1:E,0),8,4)))');

      // 5. 生成工作表索引
      const indexSheet = destSS.getSheetByName("Index");
      indexSheet.getRange("A3:B30").clearContent();
      const indexData = [["Sheet Name", "Link"]];
      destSS.getSheets().forEach(s => {
        const url = Utilities.formatString('%s#gid=%s', destSS.getUrl(), s.getSheetId());
        indexData.push([s.getName(), url]);
      });
      indexSheet.getRange(3, 1, indexData.length, indexData[0].length).setValues(indexData);
      indexSheet.hideSheet();
    } catch (e) {
      // 出错时打印日志，方便排查
      console.log(`处理ID为${targetId}的表格时出错：${e.message}`);
    }
  });
}

注意事项 #

• 运行脚本的账号需要拥有所有目标Spreadsheet的编辑权限，否则会触发权限报错
• 首次运行建议先在索引表中只填1-2个测试ID，确认逻辑正常后再批量处理所有表格
• 脚本内置错误日志捕获，可在Apps Script编辑器的「执行记录」中查看处理失败的表格ID和错误原因
• 如遇配额不足的报错，可分批处理目标表格，避免触发Google Apps Script的单日调用限制

内容的提问来源于stack exchange，提问作者Ben Williams