Google Sheets脚本：根据列转换公式为值时超时求助

问题概述 #

现有两个Google Sheets脚本，功能为：当第9列（I列）值为“Removed”时，将对应行后续15列转为值，否则保留公式。脚本在处理少于6000行时正常运行，但行数超过6000行时触发超时错误，错误信息为Exception: Service Spreadsheets timed out while accessing document with id xxx。

原脚本 #

脚本1（起始行1）

function removed() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test');
  var range = sheet.getRange(1, 9, sheet.getLastRow(), 16);
  var formulas = range.getFormulas().map(r => r.splice(1));
  var values = range.getValues().map(([a, ...b], i) => a == 'Removed' ? b : formulas[i]);
  range.offset(0, 1, values.length, 15).setValues(values);
}

脚本2（起始行2）

function removed() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test');
  var range = sheet.getRange(2, 9, sheet.getLastRow()-1, 16);
  var formulas = range.getFormulas().map(r => r.splice(1));
  var values = range.getValues().map(([a, ...b], i) => a == 'Removed' ? b : formulas[i]);
  range.offset(0, 1, values.length, 15).setValues(values);
}

错误信息 #

Nov 3, 2022, 3:23:12 PM Error   Exception: Service Spreadsheets timed out while accessing document with id xxx
    at removed(Remove Formula:6:41)

解决方案 #

超时核心原因是一次性处理大规模数据时，单次getValues()、getFormulas()和setValues()操作的资源负载过高，加上数组处理的内存占用触发了服务超时限制。以下是针对性优化方案：

• 分批处理数据：将大拆分成小批次，降低单次操作的资源消耗
• 精准范围定位：只处理有效行，避免读取无关空行或数据
• 优化数组操作：避免修改原数组的不安全操作，减少内存冗余

优化后的代码示例：

function removed() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test');
  const startRow = 2; // 根据实际需求调整起始行
  const lastRow = sheet.getLastRow();
  const batchSize = 1000; // 每批处理1000行，可根据性能调整

  // 按批次循环处理数据
  for (let i = startRow; i <= lastRow; i += batchSize) {
    const endRow = Math.min(i + batchSize - 1, lastRow);
    const rowCount = endRow - i + 1;
    
    // 获取当前批次的目标范围（I列到Z列，共16列）
    const range = sheet.getRange(i, 9, rowCount, 16);
    const formulas = range.getFormulas();
    const values = range.getValues();
    
    // 构建要写入的数据集
    const output = [];
    for (let j = 0; j < rowCount; j++) {
      const isRemoved = values[j][0] === 'Removed';
      // 标记为Removed则取单元格值，否则保留公式
      output.push(isRemoved ? values[j].slice(1) : formulas[j].slice(1));
    }
    
    // 写入当前批次数据到J列开始的15列
    sheet.getRange(i, 10, rowCount, 15).setValues(output);
  }
}

额外优化建议 #

• 启用V8运行时：在脚本编辑器设置中开启V8引擎，可显著提升代码执行效率
• 过滤空行：如果表格存在大量空行，可先通过getValues()过滤掉空行后再处理，减少无效计算
• 避免splice操作：原脚本中splice会修改原数组，改用slice更安全且性能更优

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