Google App Script隔行批量插入行执行慢超时优化方法

问题描述 #

需求为在谷歌工作表A列标注为“03”的每一行前插入2行，初始编写的循环插入代码如下：

for( var i = 3; i <= row; i = i + 5 ) {
    sheet.insertRowAfter( i + count );
    count = count + 2;   
}

• 原始数据示例：
  
• 预期实现效果：
  

当前问题：工作表总数据量达18652行时，上述代码执行耗时远超预期，同时触发执行报错：

性能差的核心原因 #

原有代码存在两个明显的性能问题：

• 逐行调用插入API：每次调用insertRowAfter都会触发工作表端的行重排、范围重算，单次API调用开销在百毫秒级，循环上千次后总耗时会直接触发Apps Script的6分钟执行时长上限
• 遍历逻辑冗余：从前往后插入行时，后续行号会持续偏移，需要额外维护count变量修正偏移，容易出现逻辑误差，也增加了不必要的计算

优化方案 #

方案1：倒序遍历+批量插入（改动量最小） #

调整遍历方向为从最后一行往第一行查找目标行，插入行时直接使用insertRowsBefore一次插入2行，不需要额外维护偏移量，API调用次数直接减半。
示例代码：

function insertRowsOpt1() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const lastRow = sheet.getLastRow();
  // 从最后一行倒序遍历到第3行（和原代码起始行一致）
  for (let i = lastRow; i >=3; i--) {
    // 判断A列值是否为"03"
    if (sheet.getRange(i, 1).getValue() === "03") {
      // 在目标行前一次插入2行，不需要计算偏移
      sheet.insertRowsBefore(i, 2);
    }
  }
}

注意：如果数据量超过2万行，这个方案依然可能存在耗时过长的问题，优先选择方案2

方案2：内存重组数据一次性写入（性能最高，推荐万行以上数据使用） #

完全避免逐行调用插入API，所有数据重组操作在本地内存完成，仅做1次读、1次写两次API调用，10万行级数据也能在数秒内执行完成。
实现逻辑：

1. 一次性读取工作表全量数据到二维数组
2. 遍历数组，遇到A列为"03"的行时，先在结果数组中推入2个空行，再推入当前行数据；非目标行直接推入结果数组
3. 清空工作表原有内容，将重组完成的数组一次性写入
   示例代码：

function insertRowsOpt2() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const lastRow = sheet.getLastRow();
  const lastCol = sheet.getLastColumn();
  // 一次性读取全量数据
  const allData = sheet.getRange(3, 1, lastRow - 2, lastCol).getValues();
  const newData = [];
  // 内存重组数据
  allData.forEach(row => {
    if (row[0] === "03") {
      // 目标行前插入2个空行
      newData.push(new Array(lastCol).fill(""));
      newData.push(new Array(lastCol).fill(""));
    }
    newData.push(row);
  });
  // 清空原有数据区域，一次性写入新数据
  sheet.getRange(3, 1, lastRow - 2, lastCol).clearContent();
  sheet.getRange(3, 1, newData.length, lastCol).setValues(newData);
}

这个方案不会触发插入行导致的公式重算、工作表重绘问题，执行效率是逐行插入的上百倍

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