使用Apps Script高亮两个Google Sheets匹配单元格并定位行列位置

我有两个工作表需要动态比对匹配值，无论列在工作表中的位置如何，脚本都可正常运行。
我需要在for loop中获取两个工作表里匹配单元格的row（行）和column（列）位置，并对这些单元格执行高亮操作。
目前我已经实现了CASES工作表的值匹配高亮，但无法获取IMPORT工作表中匹配单元格的行号，无法完成该表的背景高亮设置。
我不希望通过conditional formatting（条件格式）实现该需求。

你代码中已经通过newImportValues.indexOf(newCasesValues[i])拿到了匹配值在newImportValues数组中的索引matchedCase，由于newImportValues是从IMPORT表第2行开始读取的，匹配值对应的实际行号为matchedCase + 2，替换原有占位符即可。

同时补充了两处优化：

• 循环前提前获取工作表实例、列索引，避免循环内重复调用方法浪费性能
• 新增IMPORT表旧高亮批量清空逻辑，避免历史高亮残留

function compareSheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  // 提前获取公共参数，避免循环内重复调用
  var casesColIndex = getColumnIndex("Case Number", "CASES");
  var importColIndex = getColumnIndex("Case Number", "IMPORT");
  var casesSheet = ss.getSheetByName("CASES");
  var importSheet = ss.getSheetByName("IMPORT");

  // 加载两个表Case Number列的值
  var importValues = getColumnValues("Case Number", "IMPORT");
  var casesValues = getColumnValues("Case Number", "CASES");
  // 转换为一维数组
  var newImportValues = importValues.map(function (row) { return row[0]; });
  var newCasesValues = casesValues.map(function (row) { return row[0]; });

  // 先清空IMPORT表Case Number列的旧高亮
  importSheet.getRange(2, importColIndex, importSheet.getLastRow() - 1, 1).setBackground(null);

  for (var i in newCasesValues) {
    var matchedCase = newImportValues.indexOf(newCasesValues[i])
    var casesRow = +i + 2;
    if (matchedCase !== -1) {
      // 高亮CASES表匹配单元格
      casesSheet.getRange(casesRow, casesColIndex).setBackground("orange");
      Logger.log("Case match found in CASES sheet at row: " + casesRow + " & column: " + casesColIndex);

      // 高亮IMPORT表匹配单元格
      var importRow = matchedCase + 2;
      Logger.log("Case match found in IMPORT sheet at row: " + importRow + " & column: " + importColIndex);
      importSheet.getRange(importRow, importColIndex).setBackground("green");

    } else {
      // 未匹配则清空CASES表对应单元格格式
      casesSheet.getRange(casesRow, casesColIndex).setBackground(null);
    }
  }
}

// 加载指定列的值
function getColumnValues(label, sheetName) {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var colIndex = getColumnIndex(label, sheetName);
  var numRows = ss.getLastRow() - 1;
  var colValues = ss.getRange(2, colIndex, numRows, 1).getValues();
  return colValues;
}

// 根据列名获取列索引
function getColumnIndex(label, sheetName) {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var lc = ss.getLastColumn();
  var lookupRangeValues = ss.getRange(1, 1, 1, lc).getValues()[0];
  var index = lookupRangeValues.indexOf(label) + 1;
  return index;
}

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