Google Sheets多工作表ID匹配、校验及字母数字值提取技术问询

需求概述 #

我正在使用包含3个工作表的Google Sheets：

• 10000行的「Deposit report」工作表
• CSV导入的26000行「payment」工作表（表名：rps_243_payments_datetime_since=2025-01-01&datetime_until=2025-01-01）
• 用于展示匹配结果的「Comparison」输出工作表

需要实现：

1. 按ID匹配前两个工作表的数据，检查payment表中的ID是否存在于Deposit report表中
2. 将ID中的字母数字值提取到单独列

现有代码问题 #

当前的AppScript代码存在以下问题：

• 内层循环无终止条件，会触发无限循环
• 逐单元格调用setValue和setBackground，面对几万行数据时性能极差
• 匹配逻辑错误：只要单次不匹配就标记「Mismatch」，未遍历完所有Deposit ID就给出结果
• 未实现字母数字提取的需求

优化后的代码实现 #

function compareAndExtract() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  // 获取三个工作表
  const paymentSheet = spreadsheet.getSheetByName("rps_243_payments_datetime_since=2025-01-01&datetime_until=2025-01-01");
  const depositSheet = spreadsheet.getSheetByName("deposit-report");
  const comparisonSheet = spreadsheet.getSheetByName("Comparison");

  // 获取有效数据范围（避免读取空行）
  const paymentData = paymentSheet.getRange(2, 1, paymentSheet.getLastRow() - 1, 1).getValues().flat();
  const depositIds = depositSheet.getRange(2, 9, depositSheet.getLastRow() - 1, 1).getValues().flat();

  // 将Deposit的ID存入Set，提升查找效率
  const depositIdSet = new Set(depositIds.filter(id => id !== ""));

  // 准备输出数据：[原ID, 提取的字母数字值, 匹配状态]
  const outputData = paymentData.map(id => {
    if (!id) return ["", "", ""];
    // 提取字母数字值（移除非字母数字字符）
    const alphanumericValue = id.toString().replace(/[^a-zA-Z0-9]/g, "");
    // 检查是否匹配
    const matchStatus = depositIdSet.has(id) ? "Match" : "Mismatch";
    return [id, alphanumericValue, matchStatus];
  });

  // 清空Comparison表原有数据（保留表头）
  comparisonSheet.getRange(2, 1, comparisonSheet.getLastRow(), 3).clearContent().clearFormat();

  // 批量写入数据，提升性能
  if (outputData.length > 0) {
    comparisonSheet.getRange(2, 1, outputData.length, 3).setValues(outputData);
    // 根据匹配状态设置背景色
    const statusRange = comparisonSheet.getRange(2, 3, outputData.length, 1);
    const backgrounds = outputData.map(row => row[2] === "Match" ? ["green"] : ["red"]);
    statusRange.setBackgrounds(backgrounds);
  }
}

使用说明 #

1. 确保「Comparison」工作表表头已设置：A列=原ID，B列=提取的字母数字值，C列=匹配状态
2. 运行compareAndExtract函数即可完成所有操作
3. 批量处理逻辑大幅降低API调用次数，几万行数据也能高效运行

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