如何优化Google Sheets的Apps Script函数：从逐行到批量执行

当前函数采用逐行逐单元格遍历的方式执行，效率低下，希望改为批量处理所有行以提升执行速度。

脚本功能背景 #

当6个子工作表中的单元格被添加或编辑时，整行字体颜色会变为红色用于追踪。用户验证红色行后，可通过汇总表（Summary Sheet）上的「RESET Sheet selected」按钮触发重置操作，需完成以下动作：

• 将已编辑行的字体颜色恢复为对应背景色的预设字体色
• 将H列（Qty列）所有值重置为0
• 将O列（Suppress列）的复选框全部重置为未勾选状态

当前函数通过嵌套循环逐行逐单元格处理，执行速度慢，需要优化为批量操作。

优化后的代码 #

function resetSelectedSheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getSheetByName("Summary");
  const targetSheetName = sourceSheet.getRange(4, 10).getDisplayValue();
  const targetSheet = ss.getSheetByName(targetSheetName);
  
  // 定义需要处理的范围：第1-149行，第1-19列
  const dataRange = targetSheet.getRange(1, 1, 149, 19);
  const backgrounds = dataRange.getBackgrounds();
  
  // 预定义背景色与目标字体色的映射关系
  const bgToFontMap = {
    "#f2cda0": "#333399",
    "#969696": "#ffffff",
    "#ccffcc": "#008000",
    "#ffff99": "#993300",
    "#c9daf8": "Black",
    "#c0c0c0": "Orange",
    "#000000": "#ffffff"
  };
  
  // 批量生成目标字体色数组
  const newFontColors = backgrounds.map(row => 
    row.map(bgColor => bgToFontMap[bgColor] || row[row.indexOf(bgColor)])
  );
  
  // 批量设置所有单元格的字体颜色
  dataRange.setFontColors(newFontColors);
  
  // 批量重置H列（Qty列）值为0
  targetSheet.getRange(1, 8, 149).setValue(0.00);
  
  // 批量重置O列（Suppress列）复选框为未勾选
  const oColumnRange = targetSheet.getRange(1, 15, 149);
  oColumnRange.setValue(false);
  
  // 单独处理O列的字体颜色（匹配浅橙色背景的行）
  const oBackgrounds = oColumnRange.getBackgrounds();
  const oFontColors = oBackgrounds.map(bg => bg === "#f2cda0" ? "#333399" : "#ffffff");
  oColumnRange.setFontColors(oFontColors);
  
  ss.toast("选中工作表已完成重置");
}

优化核心要点 #

1. 减少API调用次数：原函数每次循环都单独调用getRange()、getBackgrounds()等接口，这类跨进程调用是性能瓶颈。优化后一次性获取整个目标范围的背景数据，再批量设置结果，大幅降低API交互次数。
2. 用映射表替代多条件判断：将背景色与字体色的对应关系存入对象，通过键值对直接查找，既简化代码逻辑，又提升执行效率。
3. 批量处理列操作：H列和O列的重置直接针对整列范围执行批量赋值，避免逐行遍历的冗余操作。
4. 数组化批量处理：通过map方法一次性生成所有单元格的目标字体色数组，再一次性应用到范围上，替代嵌套循环的逐单元格处理。

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