如何在Google Sheet中批量插入分组空行并规避超时限制？

问题分析 #

你的原脚本存在两个核心问题：

• 效率极低：逐行调用getRange().getValue()会触发大量与Google服务器的交互，82000行的数据量直接导致超时。
• 逻辑错误：从下往上遍历的判断逻辑，会把空行插入到新姓氏分组的第一行之后，而非旧分组的最后一行之后，同时频繁插入行也会打乱原有排序的处理逻辑。

优化方案 #

针对大数量行的场景，推荐两种高效的脚本方案，根据你的表格需求选择：

方案1：数组重构批量写入（最快，适合无复杂格式/公式的表格） #

一次性读取所有数据，在内存中构建包含空行的新数组，再批量写入表格，全程仅2次服务器交互，彻底解决超时问题：

function insertGroupBlankRows() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();
  if (data.length <= 1) return; // 无数据或仅表头时直接返回

  // 初始化新数组，保留表头（若表格无表头，改为const newData = []）
  const newData = [data[0]];
  let currentSurname = data[1][0]; // 从第二行开始记录当前姓氏

  for (let i = 1; i < data.length; i++) {
    newData.push(data[i]);
    // 检查是否为当前分组最后一行：下一行存在且姓氏不同
    if (i < data.length - 1 && data[i+1][0] !== currentSurname) {
      newData.push([]); // 添加空行
      currentSurname = data[i+1][0];
    }
  }

  // 清除原内容并写入新数据（保留格式）
  sheet.clearContents();
  sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}

方案2：批量定位插入（保留格式/公式，适合有复杂格式的表格） #

先批量找出需要插入空行的位置，再倒序插入（避免行号偏移影响后续操作），仅需1次数据读取+N次插入（N为分组数量，远小于82000）：

function insertBlankRowsBatch() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = sheet.getLastRow();
  if (lastRow <= 1) return;

  // 读取A列姓氏数据（跳过表头，若无需跳过表头改为getRange(1,1,...)）
  const surnames = sheet.getRange(2, 1, lastRow - 1, 1).getValues().flat();
  const insertPositions = [];

  // 收集每个分组最后一行的行号
  for (let i = 0; i < surnames.length - 1; i++) {
    if (surnames[i] !== surnames[i+1]) {
      insertPositions.push(i + 2); // 转换为实际行号（i从0开始，对应行号+2）
    }
  }

  // 倒序插入空行，避免行号偏移
  for (const pos of insertPositions.reverse()) {
    sheet.insertRowAfter(pos);
  }
}

关键优化点 #

1. 减少服务器交互：用getDataRange().getValues()一次性读取所有数据，避免逐行调用getRange()。
2. 逻辑修正：从上往下遍历，准确识别分组的最后一行，确保空行插入到分组末尾。
3. 批量操作：要么批量构建数组写入，要么批量定位后倒序插入，大幅提升执行速度。

内容的提问来源于stack exchange，提问作者C. V.