如何加速Google Sheets单元格迭代，基于对比表高效修改背景色？

针对你处理大型工作表时的性能问题，核心优化方向是减少对Spreadsheet服务的频繁调用——原代码逐单元格操作的方式会触发大量API请求，这是速度慢和超时的主要原因。以下是优化后的实现代码：

function updateHighlights() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const reqSheet = ss.getSheetByName('Requests Form');
  const changesSheet = ss.getSheetByName('ChangesSheet');
  
  // 获取两个表的有效数据范围（避免处理空行空列）
  const reqRange = reqSheet.getDataRange();
  const changesRange = changesSheet.getDataRange();
  
  // 一次性获取所有背景色和值（关键：批量操作替代逐单元格调用）
  let reqBackgrounds = reqRange.getBackgrounds();
  const changesValues = changesRange.getValues();
  
  const numRows = reqBackgrounds.length;
  const numCols = reqBackgrounds[0].length;
  
  // 第一步：清除原有黄色高亮（保留其他颜色）
  for (let row = 0; row < numRows; row++) {
    for (let col = 0; col < numCols; col++) {
      if (reqBackgrounds[row][col] === '#ffff00') {
        reqBackgrounds[row][col] = 'white';
      }
    }
  }
  
  // 第二步：根据ChangesSheet的非空单元格设置黄色
  for (let row = 0; row < numRows; row++) {
    // 确保ChangesSheet当前行有数据（避免越界）
    if (!changesValues[row]) continue;
    for (let col = 0; col < numCols; col++) {
      // 检查单元格是否非空（处理null/undefined情况）
      if (changesValues[row][col] !== '' && changesValues[row][col] !== null && changesValues[row][col] !== undefined) {
        reqBackgrounds[row][col] = 'yellow';
      }
    }
  }
  
  // 一次性将修改后的背景色应用到工作表（仅一次API调用）
  reqRange.setBackgrounds(reqBackgrounds);
}

关键优化点： #

• 批量读写替代逐单元格操作：用getBackgrounds()和setBackgrounds()替代循环里的getBackground()和setBackground()，把上百次API请求压缩成2次读取+1次写入，大幅降低服务调用开销。
• 内存内处理逻辑：所有判断和修改都在内存数组中完成，避免频繁与工作表交互。
• 有效范围限定：用getDataRange()获取实际有数据的范围，跳过空行空列的无效遍历。
• 修复原代码错误：原代码中cell2.getValues()是错误用法（返回二维数组），优化后直接通过数组索引访问单元格值，逻辑更准确。

额外性能建议： #

• 如果两个表的行列数不一致，建议取两者的最小行列数进行遍历，避免数组越界。
• 移除不必要的日志输出（原代码中的Logger.log(row)会增加运行耗时）。

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