如何在Apps Script中按指定类别与日期区间查询参考表匹配值

• 逻辑和你写SQL做维度关联完全一致：先把参考表、业务表的全量数据一次性读到内存里，避免循环里反复读表格产生性能损耗，Apps Script的表格IO是最慢的环节，批量拉取比逐行读快几十倍
• 先给参考表做字段预处理：把结束日期为空的“当前生效”记录统一替换成一个足够远的远期日期（比如2099-12-31），这样匹配条件可以统一成条目日期 >= 生效起始 AND 条目日期 <= 生效结束，不用单独写空值判断的分支逻辑
• 遍历业务表的时候先判断当前行的匹配值列是不是已经有内容，有值就直接跳过，避免重复计算覆盖手动修改过的内容
• 触发逻辑可以按需选：要么手动跑脚本批量回填历史数据，要么给业务表绑onEdit触发器，新增/修改条目时自动匹配填充，两套场景复用同一套核心匹配逻辑就行

以下是示例默认的列对应关系，你可以根据自己表格的实际列位置在配置项里修改：

• 参考表（默认工作表名：费率参考表）
  • A列：类别
  • B列：待匹配的目标值（费率）
  • C列：生效起始日期
  • D列：生效结束日期
• 业务数据表（默认工作表名：业务流水表）
  • A列：条目日期
  • B列：类别
  • C列：脚本自动写入的匹配值列

// 配置项，根据自己的表实际修改即可，不用动核心逻辑
const CONFIG = {
  refSheetName: '费率参考表',
  dataSheetName: '业务流水表',
  // 参考表列索引（从0开始计数，A列对应0，B列对应1，以此类推）
  refCol: {
    category: 0,
    matchValue: 1,
    startDate: 2,
    endDate: 3
  },
  // 业务表列索引
  dataCol: {
    entryDate: 0,
    category: 1,
    matchValue: 2
  },
  // 空结束日期的默认填充值，代表永久生效
  defaultEndDate: new Date('2099-12-31')
}

function matchRateValue() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  // 批量读取两张表全量数据
  const refSheet = ss.getSheetByName(CONFIG.refSheetName);
  const refData = refSheet.getDataRange().getValues();
  // 去掉参考表表头，从第二行开始处理有效数据
  const refRecords = refData.slice(1).map(row => {
    let endDate = row[CONFIG.refCol.endDate];
    if (!endDate) endDate = CONFIG.defaultEndDate;
    // 日期统一转时间戳，避免时区、格式问题导致匹配错误
    return {
      category: row[CONFIG.refCol.category],
      value: row[CONFIG.refCol.matchValue],
      start: new Date(row[CONFIG.refCol.startDate]).setHours(0,0,0,0),
      end: new Date(endDate).setHours(0,0,0,0)
    }
  });

  const dataSheet = ss.getSheetByName(CONFIG.dataSheetName);
  const dataRange = dataSheet.getDataRange();
  const dataValues = dataRange.getValues();
  const writeBackList = [];

  // 遍历业务表，跳过表头行
  for (let i = 1; i < dataValues.length; i++) {
    const row = dataValues[i];
    // 已有匹配值的行直接跳过，避免覆盖手动修改的内容
    if (row[CONFIG.dataCol.matchValue]) continue;
    const entryDate = new Date(row[CONFIG.dataCol.entryDate]).setHours(0,0,0,0);
    const entryCategory = row[CONFIG.dataCol.category];
    // 匹配逻辑和SQL的join条件完全对应
    const matched = refRecords.find(ref => {
      return ref.category === entryCategory 
        && entryDate >= ref.start 
        && entryDate <= ref.end;
    });
    if (matched) {
      writeBackList.push({
        row: i+1, // 表格行号从1开始计数，数组索引从0开始，所以要+1
        col: CONFIG.dataCol.matchValue + 1,
        value: matched.value
      })
    }
  }

  // 批量回写所有匹配到的值
  writeBackList.forEach(item => {
    dataSheet.getRange(item.row, item.col).setValue(item.value);
  })
}

// 编辑触发配置：业务表修改日期、类别列时自动执行匹配
function onEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();
  // 非业务表的编辑操作不触发逻辑
  if (sheet.getName() !== CONFIG.dataSheetName) return;
  const editCol = range.getColumn();
  // 只监听日期、类别列的修改
  if (editCol !== CONFIG.dataCol.entryDate +1 && editCol !== CONFIG.dataCol.category +1) return;
  // 延迟100毫秒等单元格内容写入完成再执行匹配，避免读到空值
  Utilities.sleep(100);
  matchRateValue();
}

• 第一次使用先在脚本编辑器里选中matchRateValue函数点运行，按弹窗提示给脚本授权，否则自动触发会因为权限不足运行失败
• 如果参考表存在同类别、同日期区间的多条重复记录，脚本默认取参考表里排在最前面的那条，和SQL写limit 1的效果一致；如果需要按优先级匹配（比如最新更新的规则优先），提前给参考表排好序即可，也可以在匹配判断里加自定义优先级规则
• 表格里的日期列一定要设置成日期格式，不要存成文本字符串，否则JS转日期对象时会解析失败，导致匹配不到值
• 单表数据量超过1万行的话，可以把逐单元格回写的逻辑改成二维数组批量整区写入，性能会更高，几千行的小数据量当前写法足够用

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