谷歌表格脚本需求：跨工作表匹配值复制单元格背景色

我有两个谷歌工作表：

• 源工作表（VALIDATION）的某列包含带背景色的值
• 目标工作表（DATA）的对应列包含相同的值，但顺序随机

需要实现：当值匹配时，将源工作表该列的背景色复制到目标工作表对应列的对应单元格。

源列背景色示例：

期望效果：

共享表格链接：My sheet

我尝试修改了一个脚本，但它会清除整个工作表的背景色，现有脚本如下：

function copycolors() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('VALIDATION');
  const cA = sh.getRange(6,3,sh.getLastRow()).getDisplayValues().flat();//values
  const bA = sh.getRange(6,3,sh.getLastRow()).getBackgrounds().flat();//colors
  let colors = {pA:[]};
  cA.forEach((c,i) => {
    colors[c]=bA[i];
    colors.pA.push(c);
  });
  const osh = ss.getSheetByName('DATA');
  const vs = osh.getDataRange().getDisplayValues();
  const bs = osh.getDataRange().getBackgrounds();
  let bgA = vs.map((r,i) =>{
    r.forEach((c,j) =>{
      let idx = colors.pA.indexOf(c);
      if(~idx) {
        bs[i][j] = colors[c];
      }
    });
    return bs[i];
  });
    
  osh.getRange(1,1,bgA.length,bgA[0].length).setBackgrounds(bgA);
}

问题分析 #

原脚本的问题在于：

1. 遍历了目标工作表的所有单元格，而非仅目标列
2. 最终调用setBackgrounds设置了整个数据范围的背景色，即使单元格未匹配值，也会被重新设置，逻辑上没必要处理所有列

修改后的脚本 #

以下脚本仅处理指定的源列和目标列，避免影响其他列的背景色：

function copyColorsToTargetColumn() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // 配置参数：根据实际情况修改
  const sourceSheetName = 'VALIDATION';
  const sourceColumn = 3; // 源列是C列（从1开始计数）
  const sourceStartRow = 6; // 源数据从第6行开始
  
  const targetSheetName = 'DATA';
  const targetColumn = 3; // 目标列是C列（从1开始计数）
  
  // 获取源工作表的数值和对应背景色
  const sourceSheet = ss.getSheetByName(sourceSheetName);
  const sourceLastRow = sourceSheet.getLastRow();
  const sourceValues = sourceSheet.getRange(sourceStartRow, sourceColumn, sourceLastRow - sourceStartRow + 1)
    .getDisplayValues().flat();
  const sourceColors = sourceSheet.getRange(sourceStartRow, sourceColumn, sourceLastRow - sourceStartRow + 1)
    .getBackgrounds().flat();
  
  // 创建值到背景色的映射表
  const colorMap = new Map();
  sourceValues.forEach((value, index) => {
    colorMap.set(value, sourceColors[index]);
  });
  
  // 获取目标工作表的目标列数据和背景色
  const targetSheet = ss.getSheetByName(targetSheetName);
  const targetLastRow = targetSheet.getLastRow();
  const targetValues = targetSheet.getRange(1, targetColumn, targetLastRow)
    .getDisplayValues().flat();
  const targetColors = targetSheet.getRange(1, targetColumn, targetLastRow)
    .getBackgrounds();
  
  // 更新目标列的背景色：仅匹配值的单元格才修改
  targetValues.forEach((value, rowIndex) => {
    if (colorMap.has(value)) {
      targetColors[rowIndex][0] = colorMap.get(value);
    }
  });
  
  // 将更新后的背景色设置回目标列
  targetSheet.getRange(1, targetColumn, targetLastRow, 1).setBackgrounds(targetColors);
}

脚本说明 #

1. 参数配置：开头的配置项可以根据你的实际列/行位置修改，无需改动核心逻辑
2. 映射表创建：使用Map存储源值与背景色的对应关系，比数组查找更高效
3. 仅处理目标列：只操作目标工作表的指定列，完全不影响其他列的背景色
4. 最小化修改：仅更新匹配值的单元格背景色，未匹配的单元格保持原有背景

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