比较 Google Sheets 中两列数据的差异：检查号码和金额一致性

function compareCheckData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet1 = ss.getSheetByName("OpenCheckDataBase"); // 第一个 Sheet 的名称
  const sheet2 = ss.getSheetByName("IncomingChecks"); // 第二个 Sheet 的名称

  // 获取两个 Sheet 中 C 列和 D 列的数据，并去除空行
  const data1 = sheet1.getRange("C1:D").getValues().filter(row => row[0] !== "");
  const data2 = sheet2.getRange("C1:D").getValues().filter(row => row[0] !== "");

  let inconsistentCheckNumbers = 0;
  let inconsistentAmounts = 0;

  // 将 sheet2 的数据存储到 Map 中，方便查找
  const checkDataMap = new Map();
  data2.forEach(row => {
    checkDataMap.set(row[0], row[1]);
  });

  // 遍历 sheet1 的数据，与 sheet2 的数据进行比较
  data1.forEach((row, index) => {
    const checkNumber = row[0];
    const amount = row[1];

    if (!checkDataMap.has(checkNumber)) {
      // 情况 1：支票号码在 sheet2 中不存在
      console.log(`Row C${index + 1} in OpenCheckDataBase: Inconsistent check number - ${checkNumber}`);
      inconsistentCheckNumbers++;
    } else {
      const amount2 = checkDataMap.get(checkNumber);
      if (amount !== amount2) {
        // 情况 3：支票号码存在，但金额不一致
        console.log(`Row C${index + 1} in OpenCheckDataBase: Inconsistent check amount for check number ${checkNumber} - Expected: ${amount2}, Actual: ${amount}`);
        inconsistentAmounts++;
      }
    }
  });

  console.log(`Total inconsistent check numbers: ${inconsistentCheckNumbers}`);
  console.log(`Total inconsistent amounts: ${inconsistentAmounts}`);
}