Google Apps Script循环执行过慢，如何优化透视表数据批量处理？

原脚本执行缓慢的核心原因是在循环中频繁调用Spreadsheet服务的读写方法——Google Apps Script与电子表格的交互属于高耗时操作，3000次循环会产生3000次网络请求，这是导致耗时超10分钟的根本原因。此外原脚本还存在数组格式错误、目标范围行数不匹配的问题。

优化后的代码 #

function optimizedDuplicateData() {
  const spreadsheet = SpreadsheetApp.getActive();
  const pivotSheet = spreadsheet.getSheetByName("Pivot Table 1");
  const targetSheet = spreadsheet.getSheetByName("Sheet5");

  // 一次性读取透视表第一列所有数据
  const lastRow = pivotSheet.getLastRow();
  const pivotData = pivotSheet.getRange(1, 1, lastRow).getValues();

  // 在内存中构建目标二维数组：每个值重复12次
  const targetData = [];
  pivotData.forEach(row => {
    const value = row[0];
    for (let i = 0; i < 12; i++) {
      targetData.push([value]);
    }
  });

  // 一次性写入目标列
  const targetRange = targetSheet.getRange(1, 3, targetData.length);
  targetRange.setValues(targetData);
}

核心优化点 #

• 批量读取数据：用getRange(1,1,lastRow).getValues()一次性读取透视表第一列所有数据，仅触发1次Spreadsheet交互，替代原循环中3000次的getValue()调用。
• 内存内数组构建：所有数组操作在本地内存完成，同时确保生成setValues要求的二维数组格式（每个子数组对应一行数据），避免原脚本的数组填充逻辑错误。
• 批量写入数据：通过setValues一次性写入36000行数据，仅触发1次Spreadsheet交互，彻底消除循环写入的耗时。
• 修正目标范围：根据最终数组的实际长度设置目标单元格范围，解决原脚本中行数不匹配的问题。

进阶优化（可选） #

如果后续数据量进一步增大，可启用SpreadsheetApp.enableAllDataExecution()（需额外授权），进一步提升批量操作的执行效率。

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