如何优化Google Sheets多工作表公式自动填充脚本以提升运行速度

问题分析 #

原脚本的性能瓶颈在于多次重复调用getRange()和copyTo()，每次API调用都会和Google Sheets服务器交互，累积起来导致运行缓慢。同时需要手动指定公式列，无法适配多工作表的分散公式场景。

优化方案 #

以下脚本实现自动识别公式列、批量处理多工作表，大幅减少API调用次数提升速度：

function autoFillFormulas() {
  // 指定需要处理的工作表名称，按需修改
  const targetSheets = ['AD', 'MAIL'];
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  targetSheets.forEach(sheetName => {
    const sheet = spreadsheet.getSheetByName(sheetName);
    if (!sheet) return;

    const lastRow = sheet.getLastRow();
    if (lastRow <= 2) return; // 无需要填充的行，直接跳过

    // 一次性获取第2行所有列的公式，返回一维数组
    const row2Formulas = sheet.getRange(2, 1, 1, sheet.getLastColumn()).getFormulas()[0];
    
    // 遍历所有列，自动识别带公式的列并批量填充
    row2Formulas.forEach((formula, colIndex) => {
      if (formula) {
        const colNumber = colIndex + 1; // 转换为1-based列号
        const targetRange = sheet.getRange(2, colNumber, lastRow - 1);
        // 直接为整列范围设置公式，相对引用会自动适配行号
        targetRange.setFormula(formula);
      }
    });
  });
}

核心优化点 #

• 批量API调用：一次性获取第2行所有公式，替代原脚本的多次单独列查询，减少90%以上的服务器交互次数。
• 自动识别公式列：无需手动指定列，通过读取第2行的公式数组自动判断哪些列需要填充。
• 多工作表适配：通过targetSheets数组灵活配置需要处理的工作表，新增工作表只需添加名称即可。
• 边界防护：自动跳过不存在的工作表、无数据需要填充的场景，避免无效操作。

额外说明 #

如果需要保留原单元格的格式（而非仅填充公式），可将targetRange.setFormula(formula)替换为：

sheet.getRange(2, colNumber).copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_FORMULAS, false);

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