Google Apps Script批量填充公式遇Spreadsheets超时问题求助

核心优化方向：减少Spreadsheet API调用次数 #

超时问题本质是频繁调用Spreadsheet服务导致的，尤其是处理数万行数据时，逐列/逐行操作的效率极低。以下是针对你场景的具体解决方案：

1. 批量生成公式数组，一次性写入 #

直接构造所有目标行的公式文本数组，一次性写入目标区域，仅需1-2次Spreadsheet调用，效率提升显著。

示例代码：

function batchFillFormulas() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const targetSheet = ss.getSheetByName("你的目标工作表名称"); // 替换为实际表名
  const startRow = 12;
  const endRow = 36000;
  const startCol = 9; // I列对应列号
  const endCol = 136; // DH列对应列号
  
  // 获取第11行的公式模板
  const formulaTemplates = targetSheet.getRange(11, startCol, 1, endCol - startCol + 1).getFormulas()[0];
  
  // 构造全量公式数组：替换模板中的行号为当前行
  const formulaArray = [];
  for (let row = startRow; row <= endRow; row++) {
    const rowFormulas = formulaTemplates.map(formula => {
      // 区分绝对引用（带$）和相对引用，仅替换不带$的11
      return formula.replace(/\$?11/g, match => {
        return match.startsWith('$') ? match : row;
      });
    });
    formulaArray.push(rowFormulas);
  }
  
  // 一次性写入所有公式
  targetSheet.getRange(startRow, startCol, endRow - startRow + 1, endCol - startCol + 1).setFormulas(formulaArray);
  
  // 将公式转为值（按需执行）
  const dataRange = targetSheet.getRange(startRow, startCol, endRow - startRow + 1, endCol - startCol + 1);
  dataRange.setValues(dataRange.getValues());
}

2. 分批次处理（应对超大规模数据） #

如果一次性写入3万行仍超时，可拆分批次处理，每批次处理5000行左右，批次间加入短暂延迟避免API限流。

示例代码片段：

function batchFillInChunks() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const targetSheet = ss.getSheetByName("你的目标工作表名称");
  const startRow = 12;
  const endRow = 36000;
  const startCol = 9;
  const endCol = 136;
  const chunkSize = 5000; // 每批次处理行数
  
  const formulaTemplates = targetSheet.getRange(11, startCol, 1, endCol - startCol + 1).getFormulas()[0];
  
  for (let i = startRow; i <= endRow; i += chunkSize) {
    const currentEndRow = Math.min(i + chunkSize - 1, endRow);
    const chunkRows = currentEndRow - i + 1;
    
    const formulaChunk = [];
    for (let row = i; row <= currentEndRow; row++) {
      const rowFormulas = formulaTemplates.map(formula => {
        return formula.replace(/\$?11/g, match => match.startsWith('$') ? match : row);
      });
      formulaChunk.push(rowFormulas);
    }
    
    targetSheet.getRange(i, startCol, chunkRows, endCol - startCol + 1).setFormulas(formulaChunk);
    SpreadsheetApp.flush(); // 强制刷新缓存
    Utilities.sleep(1000); // 延迟1秒避免API调用过于密集
  }
  
  // 转为值
  const dataRange = targetSheet.getRange(startRow, startCol, endRow - startRow + 1, endCol - startCol + 1);
  dataRange.setValues(dataRange.getValues());
}

3. 优化公式本身（降低计算负载） #

你的公式中RECHERCHEV（VLOOKUP）每列重复计算，会大幅增加计算量。可以提前将VLOOKUP结果批量计算到辅助列，再让原公式引用辅助列，减少重复计算：

• 新增辅助列（如DI列），批量写入公式：=SIERREUR(RECHERCHEV($E11;'0_BASE_PROJET'!$B:$BW;55;FAUX);0)
• 原公式修改为：=SIERREUR(SI($D11=K$10;$G11;0)/$DI11;0)

4. 使用高级Sheets API（可选） #

启用Google Sheets Advanced Service可进一步提升API调用效率，适合极端大数据场景：

1. 在脚本编辑器中点击「资源」→「高级Google服务」，启用「Google Sheets API」
2. 使用以下示例代码批量写入：

function advancedBatchFill() {
  const ssId = SpreadsheetApp.getActiveSpreadsheet().getId();
  const sheetName = "你的目标工作表名称";
  const startRow = 12;
  const endRow = 36000;
  const startCol = 9;
  const endCol = 136;
  
  const targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const formulaTemplates = targetSheet.getRange(11, startCol, 1, endCol - startCol + 1).getFormulas()[0];
  
  const requests = [];
  for (let row = startRow; row <= endRow; row++) {
    const rowFormulas = formulaTemplates.map(formula => {
      return formula.replace(/\$?11/g, match => match.startsWith('$') ? match : row);
    });
    requests.push({
      updateCells: {
        range: {
          sheetId: targetSheet.getSheetId(),
          startRowIndex: row - 1,
          endRowIndex: row,
          startColumnIndex: startCol - 1,
          endColumnIndex: endCol
        },
        rows: [{values: rowFormulas.map(f => ({userEnteredValue: {formulaValue: f}}))}],
        fields: "userEnteredValue"
      }
    });
  }
  
  // 分批次发送请求，每1000个请求一批
  const batchSize = 1000;
  for (let i = 0; i < requests.length; i += batchSize) {
    const batch = requests.slice(i, i + batchSize);
    Sheets.Spreadsheets.batchUpdate({requests: batch}, ssId);
    Utilities.sleep(500);
  }
  
  // 转为值
  const dataRange = targetSheet.getRange(startRow, startCol, endRow - startRow + 1, endCol - startCol + 1);
  dataRange.setValues(dataRange.getValues());
}

额外注意事项 #

• 尽量在表格空闲时运行脚本，避免多人编辑或同步时操作
• 可将「公式转值」步骤单独拆分，或延迟几秒执行，确保公式计算完成
• 若手动运行仍超时，可部署为时间驱动触发器，后台运行超时时间更长（手动6分钟，触发器30分钟）

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