如何修改Google Apps Script实现无序行的工作表差异高亮？

问题描述 #

我有一个包含两个工作表的Google表格，之前靠StackOverflow用户帮忙写了一段Google Apps脚本，能把两个工作表里不同的值标红。但从SQL导出数据到表格时，行的顺序不固定，原来逐行对比的脚本会把几乎所有值都判定为不同，完全失效了。有没有办法修改脚本，让它不受行顺序影响，还能正确高亮数据差异？

原脚本如下：

function menuItem1() {
  var ui = SpreadsheetApp.getUi();
  var result1 = ui.prompt("Please enter 1st Sheet Name");
  var result2 = ui.prompt("Please enter 2nd Sheet Name");
  compare(result1.getResponseText(),result2.getResponseText());
}
 
function compare(result1,result2) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName(result1);
  var sheet2 = ss.getSheetByName(result2);
  var values1 = sheet1.getDataRange().getValues();
  var range1 = sheet1.getDataRange();
  var range2 = sheet2.getDataRange();
  var values2 = range2.getValues();
  var maxRow = values1.length > values2.length ? values1.length : values2.length;
  var maxCol = values1[0].length > values2[0].length ? values1[0].length : values2[0].length;
  var backgrounds = [...Array(maxRow)].map((_, i) => [...Array(maxCol)].map((_, j) => {
    if (values1[i] && values1[i][j] && values2[i] && values2[i][j]) {
      return values1[i][j] == values2[i][j] ? 'white' : 'red';
    }
    return 'red', (values2[i] && values2[i][j]) ? 'red' : 'white'; // If you want to set "white" when values2[i][j] is empty, please use return (values2[i] && values2[i][j]) ? 'red' : 'white';
 
  }));
  range1.offset(0, 0, backgrounds.length, backgrounds[0].length).setBackgrounds(backgrounds);
  range2.offset(0, 0, backgrounds.length, backgrounds[0].length).setBackgrounds(backgrounds);
}

修改后的脚本 #

核心思路是基于唯一标识列（比如数据的主键列，这里默认第一列，可自行调整）来匹配两个工作表中的行，而不是按行号逐行对比。

function menuItem1() {
  var ui = SpreadsheetApp.getUi();
  var result1 = ui.prompt("请输入第一个工作表名称");
  var result2 = ui.prompt("请输入第二个工作表名称");
  compare(result1.getResponseText(), result2.getResponseText());
}

function compare(sheetName1, sheetName2) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet1 = ss.getSheetByName(sheetName1);
  const sheet2 = ss.getSheetByName(sheetName2);
  
  if (!sheet1 || !sheet2) {
    SpreadsheetApp.getUi().alert("工作表名称有误，请检查后重试");
    return;
  }

  // 假设第一列是唯一标识列，若不是则修改此处的索引（从0开始）
  const keyIndex = 0;
  const values1 = sheet1.getDataRange().getValues();
  const values2 = sheet2.getDataRange().getValues();

  // 创建数据映射：用唯一标识作为键，存储整行数据
  const map1 = new Map();
  const map2 = new Map();

  values1.forEach(row => {
    if (row[keyIndex]) map1.set(row[keyIndex], row);
  });
  values2.forEach(row => {
    if (row[keyIndex]) map2.set(row[keyIndex], row);
  });

  // 处理第一个工作表的背景色
  const backgrounds1 = values1.map(row => {
    const key = row[keyIndex];
    const compareRow = map2.get(key);
    // 如果没有匹配到对应行，整行标红
    if (!compareRow) return row.map(() => 'red');
    // 逐个单元格对比
    return row.map((cell, colIndex) => {
      return cell === compareRow[colIndex] ? 'white' : 'red';
    });
  });

  // 处理第二个工作表的背景色
  const backgrounds2 = values2.map(row => {
    const key = row[keyIndex];
    const compareRow = map1.get(key);
    // 如果没有匹配到对应行，整行标红
    if (!compareRow) return row.map(() => 'red');
    // 逐个单元格对比
    return row.map((cell, colIndex) => {
      return cell === compareRow[colIndex] ? 'white' : 'red';
    });
  });

  // 应用背景色到工作表
  sheet1.getDataRange().setBackgrounds(backgrounds1);
  sheet2.getDataRange().setBackgrounds(backgrounds2);
}

关键说明 #

1. 唯一标识列：脚本默认使用第一列作为匹配的唯一标识（比如ID、订单号等不会重复的字段），如果你的数据唯一标识在其他列，修改keyIndex的值即可（索引从0开始）。
2. 行匹配逻辑：先把两个工作表的数据转换成以唯一标识为键的映射表，之后遍历每个工作表的行，通过唯一标识找到另一个表中对应的行再做对比。
3. 差异标记规则：
   • 如果某行在另一个工作表中没有匹配项，整行标红
   • 如果单元格值与匹配行的对应单元格不同，标红；相同则设为白色
4. 错误处理：新增了工作表名称校验，输入错误时会弹出提示。

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