如何使用Google Apps Script优化处理10万行级大数据集的函数性能

• 关闭不必要的表格功能减少性能开销
  在函数执行开头先禁用事件触发、自动重算，执行完成后再恢复，避免表格实时渲染、公式重算占用大量时间：

// 函数开头添加
const ss = SpreadsheetApp.getActiveSpreadsheet();
const originalRecalcSetting = ss.getAutoRecalculation();
ss.setAutoRecalculation(SpreadsheetApp.RecalculationSetting.OFF);
SpreadsheetApp.disableAllEvents();

// 函数末尾添加恢复逻辑
ss.setAutoRecalculation(originalRecalcSetting);
SpreadsheetApp.enableAllEvents();

• 匹配目标范围与源数据尺寸，避免无效操作
  原代码中目标范围按目标表原有行数定义，既可能出现范围和源数据尺寸不匹配的报错，也会处理多余的空白单元格。直接按源数据的长宽定义目标范围即可：

// 原代码
// var destinationRng = sheet.getRange(1, 1, sheet.getLastRow(), 9);
// 替换为
const destinationRng = sheet.getRange(1, 1, sourceValues.length, sourceValues[0].length);

如果需要清空原有超出新数据长度的旧内容，可以在写入前加一行：

if(sheet.getLastRow() > sourceValues.length) {
  sheet.getRange(sourceValues.length + 1, 1, sheet.getLastRow() - sourceValues.length, 9).clearContent();
}

• 使用Sheets高级服务提升读写速度
  SpreadsheetApp原生读写接口对超10万行的数据集支持较差，开启Sheets API高级服务后调用批量接口，速度可提升3~10倍，基本可在6分钟超时限制内完成10万行级别的数据写入。
  操作步骤：在Apps Script编辑器左侧「服务」栏找到「Google Sheets API」添加即可，核心写入代码如下：

// 替换原有的setValues代码
Sheets.Spreadsheets.Values.update({
  values: sourceValues
}, ss.getId(), 'Ativ.!A:I', {
  valueInputOption: 'RAW'
});

• 可选优化：数据分片处理
  如果开启API后仍然偶发超时，可以将源数据按每2万行一组分片写入，每写完一组执行一次SpreadsheetApp.flush()释放内存，避免单次操作负载过高。

完整优化后代码示例 #

const SOURCE_FILE_ID = 'ID';

function getData() {
  // 禁用不必要的功能降低开销
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const targetSheet = ss.getSheetByName('Ativ.');
  const originalRecalcSetting = ss.getAutoRecalculation();
  ss.setAutoRecalculation(SpreadsheetApp.RecalculationSetting.OFF);
  SpreadsheetApp.disableAllEvents();

  try {
    // 读取源数据
    const sourceSheet = SpreadsheetApp.openById(SOURCE_FILE_ID).getSheetByName('ativcopiar');
    const sourceValues = sourceSheet.getRange(1, 1, sourceSheet.getLastRow(), 9).getValues();

    // 清空目标区域原有内容
    targetSheet.getRange(1, 1, targetSheet.getMaxRows(), 9).clearContent();

    // 二选一写入即可：1.原生写法（无需额外开启服务）
    const targetRng = targetSheet.getRange(1, 1, sourceValues.length, sourceValues[0].length);
    targetRng.setValues(sourceValues);

    // 2.API写法（需要先添加Sheets API服务，性能更高，注释掉上面原生写法再启用）
    // Sheets.Spreadsheets.Values.update({
    //   values: sourceValues
    // }, ss.getId(), 'Ativ.!A:I', {
    //   valueInputOption: 'RAW'
    // });
  } catch (e) {
    console.error('执行出错：', e);
  } finally {
    // 恢复原有设置
    ss.setAutoRecalculation(originalRecalcSetting);
    SpreadsheetApp.enableAllEvents();
  }
}

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