Google Apps Script跨表比对两列 匹配后返回相邻单元格值

• 需比对两个独立工作表中的两列数据，找到匹配项后返回对应相邻单元格的值
• 必须使用Apps Script实现功能，不接受公式类解决方案，后续需将该功能整合到其他代码模块
• 前期检索到的相关方案绝大多数为Sheets公式实现，少量涉及脚本的方案不符合使用场景
• 自行编写的代码存在逻辑错误，无法实现目标功能

function findDuplicate() {
  
  var sheet1 = SpreadsheetApp.getSheetByName('Working'); 
  var s1 = sheet1.getRange('A:A').getValues();
  var sheet2 = SpreadsheetApp.getSheetByName('Match'); 
  var s2 = sheet1.getRange('A:A').getValues();
  var mv = s1 === s2;
  var matchedValue = mv.getvalue();
  return matchedValue.offset(0, 1);

}

• 工作表获取方法调用错误：getSheetByName()是Spreadsheet文件对象的方法，不能直接在SpreadsheetApp全局对象上调用
• 数据源引用错误：读取Match工作表数据时错误引用了Working表对象，导致取到的数据源完全错误
• 比对逻辑错误：getValues()返回的是二维数组结构，直接用===比对两个数组永远无法得到匹配结果，数组是引用类型，直接比对判断的是内存地址是否一致，和值无关
• 方法调用逻辑错误：getValue()、offset()是Range类的实例方法，不能在布尔值、数组等非Range类型数据上调用

function matchColumnAndGetAdjacent() {
  // 获取当前绑定的表格文件
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  // 读取两个目标工作表
  const workingSheet = ss.getSheetByName('Working');
  const matchSheet = ss.getSheetByName('Match');

  // 构建匹配映射：Match表A列为匹配键，B列为需要返回的相邻值，自动过滤空行
  const matchRawData = matchSheet.getDataRange().getValues();
  const matchKeyMap = new Map();
  for (const row of matchRawData) {
    const matchKey = row[0]?.toString().trim();
    if (matchKey) {
      matchKeyMap.set(matchKey, row[1]);
    }
  }

  // 读取Working表待匹配数据，默认从第2行开始跳过表头，可根据实际表结构调整起始行
  const workingLastRow = workingSheet.getLastRow();
  if (workingLastRow < 2) return; // 无有效数据直接退出
  // 同时选中待匹配的A列、用于写结果的B列，减少读写次数
  const workingRange = workingSheet.getRange(2, 1, workingLastRow - 1, 2);
  const workingData = workingRange.getValues();

  // 逐行匹配填充结果
  for (let i = 0; i < workingData.length; i++) {
    const currentKey = workingData[i][0]?.toString().trim();
    if (currentKey && matchKeyMap.has(currentKey)) {
      // 匹配成功则将对应相邻值写入当前行B列
      workingData[i][1] = matchKeyMap.get(currentKey);
    }
  }

  // 批量将结果写回表格
  workingRange.setValues(workingData);
}

• 代码默认匹配规则为忽略文本前后空格、区分大小写，可根据实际需求修改比对逻辑
• 默认将匹配到的结果写入Working表对应行的B列，如果需要写入其他列、或者返回值给其他代码模块调用，可直接调整列索引或修改返回值逻辑
• 采用Map结构做键值匹配、批量读写表格数据的实现方式，相比双层循环逐行比对、逐单元格读写的方案，在数据量较大时性能提升明显
• 如果表头不在第1行，可自行修改getRange()方法的起始行、行列数参数适配实际表格结构
• 注意：如果工作表名称有修改，需要同步修改代码中getSheetByName()传入的表名参数，否则会触发空对象报错

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