不同长度数组比对异常：Google Apps Script支票数据校验错误

需求说明 #

需要比对两个工作表的C列（支票号）与D列（支票金额），找出以下两类行的位置：

• C列支票号在另一表中不存在的行
• C列支票号匹配，但D列金额不一致的行

问题现象 #

当两个表数据几乎一致仅手动修改1-2处时，脚本运行正常；但比对CashedChecks与OpenChecks工作表时，所有CashedChecks的行都被标记为无效，实际存在匹配的支票号却未被统计为有效。

原代码 #

function CHK_BothChecks_V5_StackOverFlow() {
  const ss = SpreadsheetApp.getActive();
  const sh1 = ss.getSheetByName("OpenChecks");
  const sh2 = ss.getSheetByName("CashedChecks");
  var rg1 = sh1.getRange("C1:D" + sh1.getLastRow()).getValues();
  var rg2 = sh2.getRange("C1:D" + sh2.getLastRow()).getValues();
  var correctChecks = 0;
  var invalidChecks = 0;
  var invalidAmounts = 0; 
  for (var i = 0; i < rg2.length; i++) {  
    if (rg1[i][0] == rg2[i][0]) {
      correctChecks++;
    } 
    if(rg1[i][0] == rg2[i][0] && rg1[i][1] !== rg2[i][1]) {
      invalidAmounts++;
      var amtIndex = sh1.getRange(i + 1, 2).getRow();
      console.log("Incongruent amound found at row " + amtIndex);
    } if (rg1[i][0] !== rg2[i][0]) {
      var index = sh1.getRange(i + 1, 2).getRow();
      invalidChecks++;
      console.log(index); 
    }
  }
  console.log("Total number of valid check numbers is " + correctChecks);
  console.log("Total number of invalid check numbers is " + invalidChecks);
  console.log("Total number of invalid amounts is " + invalidAmounts);
}

问题根源 #

原代码采用按数组索引逐行比对的逻辑，默认两个工作表的行顺序完全一致，支票号必须在同一行才能匹配。但实际场景中，两个表的行顺序可能不同，导致即使支票号存在于另一表中，只要不在同一行就会被判定为无效。

修正方案 #

改用对象映射存储OpenChecks的支票号与金额，通过支票号直接查找匹配记录，不受行顺序影响：

function CHK_BothChecks_Fixed() {
  const ss = SpreadsheetApp.getActive();
  const openSheet = ss.getSheetByName("OpenChecks");
  const cashedSheet = ss.getSheetByName("CashedChecks");
  
  // 获取OpenChecks的C列（支票号）和D列（金额）数据，构建映射表
  const openCheckData = openSheet.getRange("C1:D" + openSheet.getLastRow()).getValues();
  const openCheckMap = {};
  openCheckData.forEach(row => {
    const checkNumber = row[0];
    const amount = row[1];
    if (checkNumber) { // 跳过空的支票号
      openCheckMap[checkNumber] = amount;
    }
  });
  
  // 获取CashedChecks的对应数据并遍历比对
  const cashedCheckData = cashedSheet.getRange("C1:D" + cashedSheet.getLastRow()).getValues();
  let correctChecks = 0;
  let invalidChecks = 0;
  let invalidAmounts = 0;
  
  cashedCheckData.forEach((row, index) => {
    const checkNumber = row[0];
    const cashedAmount = row[1];
    const rowNum = index + 1; // 工作表行号从1开始
    
    if (!checkNumber) return; // 跳过空行
    
    if (openCheckMap.hasOwnProperty(checkNumber)) {
      // 支票号存在，检查金额
      correctChecks++;
      const openAmount = openCheckMap[checkNumber];
      if (openAmount !== cashedAmount) {
        invalidAmounts++;
        console.log(`金额不一致：CashedChecks第${rowNum}行，支票号${checkNumber}`);
      }
    } else {
      // 支票号不存在
      invalidChecks++;
      console.log(`支票号无效：CashedChecks第${rowNum}行，支票号${checkNumber}`);
    }
  });
  
  console.log(`有效支票号总数：${correctChecks}`);
  console.log(`无效支票号总数：${invalidChecks}`);
  console.log(`金额不一致总数：${invalidAmounts}`);
}

修正说明 #

1. 映射表优化：将OpenChecks的支票号作为键、金额作为值存入对象，实现快速查找，避免逐行遍历比对的低效问题
2. 行顺序无关：遍历CashedChecks时，直接通过支票号查找OpenChecks的记录，无需依赖行位置对应
3. 性能提升：减少不必要的getRange调用，直接通过数组索引计算行号，降低Google Apps Script的服务调用开销
4. 空值处理：跳过空的支票号行，避免无效统计

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