如何更快调用电子表格多工作表并在条件判断中高效执行操作

问题背景 #

现有脚本功能为复制指定Spreadsheet模板，依据"Campaign Info"工作表中的复选框（Y/N状态），对副本内的工作表执行隐藏或删除操作。当前代码存在大量重复API调用、冗余逻辑，执行效率低且维护繁琐，需要更高效的实现方案。

优化思路 #

1. 批量读取数据：一次性读取"Campaign Info"中所有复选框数据，减少Spreadsheet API调用次数（Google Apps脚本中API调用是核心性能瓶颈）。
2. 配置化映射：将营销目标（如Awareness、Video Views）、平台（如Meta、Snapchat）与对应工作表组做成配置对象，用配置替代重复的if/for逻辑，代码更简洁易维护。
3. 预存工作表映射：一次性获取副本的所有工作表，以工作表名为键存入对象，避免重复调用getSheetByName。
4. 复用处理逻辑：封装通用函数处理工作表组的隐藏/删除操作，避免重复编写相同逻辑。

优化后完整代码 #

function template() {
  // 获取源表格和Campaign Info工作表
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const campaignInfoSheet = ss.getSheetByName('Campaign Info');
  
  // 复制模板表格
  const templateId = "INSERT SPREADSHEET ID";
  const templateFile = DriveApp.getFileById(templateId);
  const copyFile = templateFile.makeCopy();
  const copySs = SpreadsheetApp.openById(copyFile.getId());
  
  // 批量读取所有复选框数据（A2:C9，包含目标和平台的Y/N状态）
  const checkboxData = campaignInfoSheet.getRange("A2:C9").getValues();
  
  // 预存副本中所有工作表的映射（工作表名 => 工作表对象）
  const sheetMap = {};
  copySs.getSheets().forEach(sheet => {
    sheetMap[sheet.getName()] = sheet;
  });

  // 配置：营销目标 -> 对应平台及工作表组
  const targetConfig = {
    "Awareness": [
      {
        platformColIndex: 2, // 对应C列（checkboxData数组索引）
        sheets: [
          'Meta Awareness Campaign',
          'Meta Awareness Ad Set',
          'FB Awareness Ad Set',
          'IG Awareness Ad Set'
        ]
      },
      {
        platformColIndex: 2,
        sheets: [
          'Snapchat Awareness Campaign',
          'Snapchat Awareness Ad Set'
        ]
      },
      {
        platformColIndex: 2,
        sheets: [
          'TikTok Awareness Campaign',
          'TikTok Awareness Ad Set'
        ]
      },
      {
        platformColIndex: 2,
        sheets: [
          'Twitter Awareness Campaign',
          'Twitter Awareness Ad Set'
        ]
      },
      {
        platformColIndex: 2,
        sheets: [
          'Youtube Awareness Campaign',
          'Youtube Awareness Ad Set'
        ]
      },
      {
        platformColIndex: 2,
        sheets: [
          'Google Awareness Campaign',
          'Google Awareness Ad Set'
        ]
      },
      {
        platformColIndex: 2,
        sheets: [
          'LinkedIn Awareness Campaign',
          'LinkedIn Awareness Ad Set'
        ]
      },
      {
        platformColIndex: 2,
        sheets: [
          'Programmatic Awareness Campaign',
          'Programmatic Awareness Ad Set'
        ]
      }
    ],
    // 可按需添加其他营销目标的配置，比如Video Views、Engagements等
    "Video Views": [
      // 参照Awareness格式填写对应平台和工作表
    ]
    // ... 其他目标配置
  };

  // 封装工作表处理函数
  const processSheets = (sheetNames, action) => {
    sheetNames.forEach(name => {
      const sheet = sheetMap[name];
      if (sheet) {
        action === 'hide' ? sheet.hideSheet() : copySs.deleteSheet(sheet);
      }
    });
  };

  // 遍历处理所有营销目标
  checkboxData.forEach((row) => {
    const targetStatus = row[0]; // A列：目标的Y/N状态
    const targetName = row[1]; // B列：目标名称
    const config = targetConfig[targetName];
    
    if (!config) return; // 无对应配置则跳过

    if (targetStatus === "N") {
      // 目标为N：删除该目标下所有工作表
      config.forEach(item => processSheets(item.sheets, 'delete'));
    } else if (targetStatus === "Y") {
      // 目标为Y：根据平台状态处理对应工作表
      config.forEach(item => {
        const platformStatus = row[item.platformColIndex];
        processSheets(item.sheets, platformStatus === "Y" ? 'hide' : 'delete');
      });
    }
  });
}

关键优化点说明 #

• 降低API调用量：原代码多次调用getRange和getSheetByName，优化后仅调用2次getRange和1次getSheets，大幅减少API交互次数，提升执行速度。
• 配置化管理：targetConfig对象可轻松添加/修改营销目标与工作表的对应关系，无需改动核心逻辑，维护成本显著降低。
• 逻辑复用：processSheets函数统一处理工作表的隐藏/删除逻辑，避免冗余代码，核心逻辑更清晰。

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