Google Sheets背景色统计脚本超时问题优化咨询

原脚本超时的核心原因是不必要的公式解析操作（正则匹配+多次API调用），以及当范围较大或函数被批量调用时，重复的API请求累积导致触发执行时间上限。以下是优化后的脚本及方案：

优化后的计数脚本（countColoredCells） #

function countColoredCells(rangeA1, colorCellA1) {
  // 直接通过A1符号获取目标范围和颜色单元格
  const targetRange = SpreadsheetApp.getRange(rangeA1);
  const bgColors = targetRange.getBackgrounds();
  const targetColor = SpreadsheetApp.getRange(colorCellA1).getBackground();

  // 扁平化二维颜色数组，过滤匹配项后统计数量
  return bgColors.flat().filter(color => color === targetColor).length;
}

优化后的求和脚本（sumColoredCells） #

function sumColoredCells(rangeA1, colorCellA1) {
  const targetRange = SpreadsheetApp.getRange(rangeA1);
  const bgColors = targetRange.getBackgrounds();
  const values = targetRange.getValues();
  const targetColor = SpreadsheetApp.getRange(colorCellA1).getBackground();

  let total = 0;
  // 遍历数组，匹配颜色时累加数值（自动处理非数字值为0）
  for (let i = 0; i < bgColors.length; i++) {
    for (let j = 0; j < bgColors[i].length; j++) {
      if (bgColors[i][j] === targetColor) {
        total += Number(values[i][j]) || 0;
      }
    }
  }
  return total;
}

关键优化点 #

• 移除公式解析：直接让函数接收范围的A1符号作为参数（如"A1:C10"），省去正则匹配和活动单元格/工作表的冗余API调用，大幅提升效率。
• 高效数组处理：用flat()+filter()替代嵌套循环（计数脚本），求和脚本保留循环但简化逻辑，底层执行效率更高。
• 跨表支持：参数可传入带工作表名的A1符号（如"Sheet2!A1:C10"），兼容多工作表场景。
• 容错处理：求和脚本自动将非数字值转为0，避免计算错误。

使用方法 #

在单元格中输入公式，示例：

• 计数：=countColoredCells("A1:C10", "D1")（统计A1:C10中与D1背景色相同的单元格数量）
• 求和：=sumColoredCells("A1:C10", "D1")（统计A1:C10中与D1背景色相同的单元格数值总和）

进阶优化：批量处理方案（彻底避免超时） #

如果需要处理超大范围或大量重复调用，建议使用批量处理菜单，一次性完成所有统计，减少API调用次数：

function onOpen() {
  // 打开表格时添加自定义菜单
  SpreadsheetApp.getUi().createMenu('颜色统计工具')
    .addItem('批量计算颜色计数', 'batchCountColors')
    .addItem('批量计算颜色求和', 'batchSumColors')
    .addToUi();
}

function batchCountColors() {
  const sheet = SpreadsheetApp.getActiveSheet();
  // 可自行修改统计范围和颜色参考列
  const dataRange = sheet.getRange("A1:C10");
  const colorRefRange = sheet.getRange("D1:D5");
  const bgColors = dataRange.getBackgrounds();
  const results = [];

  // 遍历每个参考颜色，批量统计
  colorRefRange.getBackgrounds().forEach(([color]) => {
    const count = bgColors.flat().filter(c => c === color).length;
    results.push([count]);
  });

  // 将结果写入指定单元格（这里写入E1:E5）
  sheet.getRange("E1:E5").setValues(results);
}

function batchSumColors() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const dataRange = sheet.getRange("A1:C10");
  const colorRefRange = sheet.getRange("D1:D5");
  const bgColors = dataRange.getBackgrounds();
  const values = dataRange.getValues();
  const results = [];

  colorRefRange.getBackgrounds().forEach(([color]) => {
    let total = 0;
    for (let i = 0; i < bgColors.length; i++) {
      for (let j = 0; j < bgColors[i].length; j++) {
        if (bgColors[i][j] === color) {
          total += Number(values[i][j]) || 0;
        }
      }
    }
    results.push([total]);
  });

  sheet.getRange("F1:F5").setValues(results);
}

使用时，只需点击顶部菜单的「颜色统计工具」，选择对应功能即可一次性完成统计，完全避免自定义函数的重复API调用问题。

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