Google Sheets脚本问题：支票号与金额对比逻辑缺失排查

在Google Sheets中需要对比两张工作表的支票号（C列）与金额（D列），实现以下校验逻辑并输出到控制台：

• 当当前工作表C列的支票号在另一工作表C列中不存在时
• 当当前工作表C列+D列的组合在另一工作表的C、D列组合中不存在时
• 当两张工作表C列支票号匹配，但D列金额不一致时

期望控制台输出示例：

Row C54 has an inconsistent check number
Row C42 has an inconsistent check amount

现有脚本CHK_BothChecks_V3仅能检测错误支票号，无法处理金额校验，代码如下：

function CHK_BothChecks_V3() {

  const ss = SpreadsheetApp.getActive();
  const s1 = ss.getSheetByName("OpenCheckDataBase");
  const s2 = ss.getSheetByName("IncomingChecks");

    var range1 = s1.getRange("C1:D").getValues();
    var range2 = s2.getRange("C1:D").getValues();
    var correctChecks = 0;
    var invalidChecks = 0;
    var invalidAmounts = 0;

    for(var i = 0; i < range1.length;i++) {
        if(range1[i][0] == range2[i][0]) {
          correctChecks++;
        }
               else if(range1[i][0] !== range2[i][0]) {
          var index = s1.getRange(i+1, 2).getRow();
          invalidChecks++; 
          console.log(index); /** This is the row locations of where inconsistant check numbers will show up */
        }

    }
        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);

 } 

原代码的核心问题是仅按行索引逐行对比两张表的C列，而非在另一张表的所有行中查找匹配项，因此无法覆盖需求中的三个校验逻辑。以下是修改后的完整代码，同时实现所有校验要求：

function CHK_BothChecks_V3() {
  const ss = SpreadsheetApp.getActive();
  const s1 = ss.getSheetByName("OpenCheckDataBase");
  const s2 = ss.getSheetByName("IncomingChecks");

  // 获取两张表的C-D列数据，并过滤掉空行（避免处理无数据的行）
  const data1 = s1.getRange("C1:D").getValues().filter(row => row[0] !== "");
  const data2 = s2.getRange("C1:D").getValues().filter(row => row[0] !== "");

  // 构建s2的索引：用支票号做键，存储对应的金额（支持一个支票号对应多个金额的场景）
  const checkToAmounts = new Map();
  data2.forEach(row => {
    const checkNum = row[0];
    const amount = row[1];
    if (!checkToAmounts.has(checkNum)) {
      checkToAmounts.set(checkNum, new Set());
    }
    checkToAmounts.get(checkNum).add(amount);
  });

  let correctChecks = 0;
  let invalidChecks = 0;
  let invalidAmounts = 0;

  // 遍历s1的每一行数据
  data1.forEach((row, index) => {
    const currentRowNum = index + 2; // 因为从C1开始，过滤后索引对应原表行号+1（空行已过滤）
    const currentCheck = row[0];
    const currentAmount = row[1];

    // 逻辑1：检查支票号是否在s2中存在
    if (!checkToAmounts.has(currentCheck)) {
      console.log(`Row C${currentRowNum} has an inconsistent check number`);
      invalidChecks++;
      return; // 支票号不存在，无需继续检查金额
    }

    const targetAmounts = checkToAmounts.get(currentCheck);
    // 逻辑3：支票号存在，但金额不匹配
    if (!targetAmounts.has(currentAmount)) {
      console.log(`Row C${currentRowNum} has an inconsistent check amount`);
      invalidAmounts++;
    } else {
      // 逻辑2：支票号+金额组合存在，校验通过
      correctChecks++;
    }
  });

  // 输出统计结果
  console.log(`Total number of valid check+amount pairs is ${correctChecks}`);
  console.log(`Total number of invalid check numbers is ${invalidChecks}`);
  console.log(`Total number of invalid amounts is ${invalidAmounts}`);
}

关键逻辑说明 #

1. 数据预处理：过滤空行，避免处理无支票号的无效行，提升效率。
2. 构建索引表：用Map存储s2中支票号对应的所有金额（Set去重），这样可以O(1)时间复杂度查找支票号和金额是否存在，比逐行遍历更高效。
3. 逐行校验：
   • 先检查支票号是否存在于s2的索引中，不存在则输出支票号错误
   • 支票号存在时，检查当前金额是否在该支票号对应的金额集合中，不存在则输出金额错误
   • 两者都匹配则计数为有效项

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