如何使用Advanced Sheets Service API结合Google Script解决脚本超时错误？

前提准备：启用Google Sheets高级服务 #

• 在Google Apps Script编辑器左侧菜单栏，点击「服务」（+号图标）。
• 在弹出列表中找到「Google Sheets API」，点击「添加」完成启用。

重写后的代码（适配原第一段代码逻辑：从第2行开始处理） #

function removedWithAdvancedService() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ssId = ss.getId();
  const sheetName = 'test';
  const targetSheet = ss.getSheetByName(sheetName);
  
  // 定义待处理范围：第2行、第9列（I列）开始，共lastRow-1行，16列（I到X列）
  const startRow = 2;
  const startCol = 9;
  const numRows = targetSheet.getLastRow() - 1;
  const numCols = 16;
  const rangeA1 = `${sheetName}!R${startRow}C${startCol}:R${startRow + numRows - 1}C${startCol + numCols - 1}`;

  // 批量获取单元格的值和公式，减少服务交互次数
  const batchGetResult = Sheets.Spreadsheets.Values.batchGet(ssId, {
    ranges: [
      { range: rangeA1, valueRenderOption: 'UNFORMATTED_VALUE' },
      { range: rangeA1, valueRenderOption: 'FORMULA' }
    ]
  });

  // 提取值数组和公式数组，兼容空行情况
  const values = batchGetResult.valueRanges[0].values || [];
  const formulas = batchGetResult.valueRanges[1].values || [];

  // 按原逻辑处理数据：首列是'Removed'则保留值，否则保留公式
  const processedData = values.map((row, idx) => {
    const [firstCol, ...restCols] = row;
    if (firstCol === 'Removed') {
      return restCols;
    } else {
      // 确保公式行长度足够，避免索引越界
      return formulas[idx] ? formulas[idx].slice(1) : Array(numCols - 1).fill('');
    }
  });

  // 定义写入范围：第2行、第10列（J列）开始，共numRows行，15列
  const writeRangeA1 = `${sheetName}!R${startRow}C${startCol + 1}:R${startRow + numRows - 1}C${startCol + numCols - 1}`;

  // 批量写入处理后的数据
  Sheets.Spreadsheets.Values.update(
    { values: processedData },
    ssId,
    writeRangeA1,
    { valueInputOption: 'USER_ENTERED' }
  );
}

适配第二段代码逻辑（从第1行开始处理） #

只需修改代码中的startRow和numRows参数：

const startRow = 1;
const numRows = targetSheet.getLastRow();

为什么能解决超时问题 #

原代码多次调用getFormulas()、getValues()、setValues()，每个方法都会触发一次与Google Sheets服务的交互，数据量大时会累积延迟导致超时。改用Advanced Sheets Service后，仅通过2次API请求（批量获取、批量写入）完成所有操作，大幅减少服务交互次数，提升执行效率，避免超时。

额外注意事项 #

• 如果表格行数超过10万，建议分批次处理（比如每次处理10000行），避免单次请求数据量过大。
• valueInputOption: 'USER_ENTERED'确保公式和普通值都能按手动输入的规则正确写入。

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