求助：Google Sheets脚本实现跨表双值匹配并高亮指定列

需求说明 #

需要在Form工作表中，对照Sheet2的数据实现以下高亮规则：

• 若Form的A列（code）和C列（project）与Sheet2的A列、B列同时匹配：高亮Form的A列和C列
• 仅Form的A列（code）与Sheet2的A列匹配：高亮Form的A列
• 仅Form的C列（project）与Sheet2的B列匹配：高亮Form的C列
• 无任何匹配：保持单元格白色背景

原脚本存在的问题 #

1. 效率低下：内层循环中每次都重新读取Sheet2的全部数据，大量重复操作拖慢执行速度
2. 逻辑混乱：将三种匹配场景混在一个判断条件中，无法区分不同匹配类型，仅处理了A列的高亮，完全忽略了C列的规则
3. 结果覆盖：内层循环的每一次迭代都会覆盖当前行的背景色设置，若后续行不匹配，会把之前匹配的高亮改回白色

优化后的脚本实现 #

function checkDups() {
  const ss = SpreadsheetApp.getActive();
  const formSheet = ss.getSheetByName('Form');
  const targetSheet = ss.getSheetByName('Sheet2');

  // 一次性读取目标表数据，避免重复IO操作
  const targetLastRow = targetSheet.getLastRow();
  if (targetLastRow < 2) return; // 目标表无数据时直接退出
  const targetData = targetSheet.getRange(2, 1, targetLastRow - 1, 2).getValues();

  // 预存匹配所需的集合，提升查找效率
  const codeSet = new Set();
  const projectSet = new Set();
  const codeProjectMap = new Set(); // 存储"code|project"格式的组合键

  targetData.forEach(row => {
    const code = row[0];
    const project = row[1];
    if (code) codeSet.add(code);
    if (project) projectSet.add(project);
    if (code && project) codeProjectMap.add(`${code}|${project}`);
  });

  // 读取Form表数据
  const formLastRow = formSheet.getLastRow();
  if (formLastRow < 2) return; // Form表无数据时直接退出
  const formRange = formSheet.getRange(2, 1, formLastRow - 1, 3); // 仅读取A-C列数据
  const formData = formRange.getValues();

  // 准备背景色数组，批量设置提升效率
  const bgColors = formData.map(row => {
    const code = row[0];
    const project = row[2];
    const isCodeMatch = codeSet.has(code);
    const isProjectMatch = projectSet.has(project);
    const isFullMatch = codeProjectMap.has(`${code}|${project}`);

    // 根据匹配规则设置对应列的背景色
    if (isFullMatch) {
      return ['red', null, 'red']; // A列和C列高亮，B列保持原样
    } else if (isCodeMatch) {
      return ['red', null, null]; // 仅A列高亮
    } else if (isProjectMatch) {
      return [null, null, 'red']; // 仅C列高亮
    } else {
      return ['white', null, 'white']; // 无匹配，A、C列设为白色
    }
  });

  // 批量设置背景色，比逐个单元格设置快得多
  formRange.setBackgrounds(bgColors);
}

关键优化点说明 #

1. 预加载数据：一次性读取Sheet2的所有数据并存储到集合中，避免循环内重复读取表格，大幅提升执行效率
2. 明确匹配逻辑：通过三个布尔变量分别判断全匹配、仅code匹配、仅project匹配的场景，清晰对应需求规则
3. 批量操作：使用setBackgrounds批量设置背景色，替代原脚本中逐个单元格调用setBackground的方式，减少API调用次数，提升速度
4. 边界处理：增加了表中无数据时的退出逻辑，避免无效执行

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