如何用脚本复制Google Sheets特定颜色高亮单元格到另一工作表单列

前置准备 #

• 打开目标Google Sheets文档，点击顶部菜单栏「扩展程序」→「Apps Script」进入脚本编辑器
• 清空编辑器内默认生成的空白代码

配置说明 #

首次使用前先获取目标高亮色的色值：选中一个你要提取的带高亮色的单元格，粘贴代码后先运行getTargetColor()函数，在执行日志中就能拿到对应的十六进制色值，替换到配置项的TARGET_COLOR字段即可

• SOURCE_SHEET_NAME：存放原始数据的工作表名称
• TARGET_SHEET_NAME：用来放汇总结果的工作表名称，不存在会自动创建
• TARGET_COLUMN：汇总结果写入的列序号，A列为1，B列为2，以此类推
• TARGET_START_ROW：汇总结果从第几行开始写入

完整脚本代码 #

// 配置项 - 按需修改
const CONFIG = {
  // 要匹配的目标单元格背景色（十六进制格式，比如#ffff00是默认黄色高亮）
  TARGET_COLOR: "#ffff00",
  SOURCE_SHEET_NAME: "Sheet1",
  TARGET_SHEET_NAME: "汇总结果",
  TARGET_COLUMN: 1,
  TARGET_START_ROW: 1
}

// 辅助函数：获取当前选中单元格的背景色值
function getTargetColor() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const cell = sheet.getCurrentCell();
  console.log("当前选中单元格背景色值：", cell.getBackground());
}

// 核心汇总函数
function collectColoredCells() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getSheetByName(CONFIG.SOURCE_SHEET_NAME);
  let targetSheet = ss.getSheetByName(CONFIG.TARGET_SHEET_NAME);
  
  // 目标汇总表不存在则自动新建
  if (!targetSheet) {
    targetSheet = ss.insertSheet(CONFIG.TARGET_SHEET_NAME);
  }

  // 读取源表所有数据范围的背景色和值
  const dataRange = sourceSheet.getDataRange();
  const bgColors = dataRange.getBackgrounds();
  const allValues = dataRange.getValues();

  const resultList = [];
  // 遍历所有单元格匹配目标颜色
  for (let row = 0; row < bgColors.length; row++) {
    for (let col = 0; col < bgColors[row].length; col++) {
      if (bgColors[row][col] === CONFIG.TARGET_COLOR) {
        resultList.push([allValues[row][col]]);
      }
    }
  }

  // 清空目标列旧数据
  targetSheet.getRange(1, CONFIG.TARGET_COLUMN, targetSheet.getLastRow(), 1).clearContent();

  // 批量写入汇总结果
  if (resultList.length > 0) {
    targetSheet.getRange(CONFIG.TARGET_START_ROW, CONFIG.TARGET_COLUMN, resultList.length, 1).setValues(resultList);
    SpreadsheetApp.getUi().alert(`汇总完成，共找到${resultList.length}个符合条件的单元格`);
  } else {
    SpreadsheetApp.getUi().alert("未找到匹配指定高亮色的单元格");
  }
}

运行说明 #

• 代码粘贴完成后点击顶部保存按钮，按照提示完成Google账号授权
• 运行collectColoredCells函数即可执行汇总，执行完成后会弹出提示框告知匹配到的单元格总数
• 若需要自动更新汇总结果，可以在脚本编辑器的触发器页面，给collectColoredCells设置定时触发规则，也可以将函数绑定到表格内的绘图按钮，点击即可手动触发汇总
• 脚本每次运行会先清空目标列的旧数据，再写入最新结果，不会产生重复内容

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