寻求更高效的OfficeScript列匹配查找脚本优化方案

原脚本的核心问题 #

原脚本性能低下的主要原因是频繁的Excel单元格级交互和冗余的嵌套循环：

• 每次调用getCell()、getText()、setValue()都会触发与Excel引擎的通信，这类操作开销极大，数据量较大时会直接拖慢执行速度。
• 三层嵌套循环逐单元格处理，加上反复的Excel对象调用，导致内存占用飙升。
• 未实现lookupWord与matchedCategory的关联映射，仅填充静态值，未完成核心需求。

优化方案思路 #

1. 批量读取数据到内存：一次性读取两张表格的所有数据到数组，全程在内存中处理，大幅减少Excel交互次数（这是OfficeScript性能优化的核心原则）。
2. 构建快速查找映射：将参考表的lookupWord和matchedCategory转换为键值对（Map对象），实现O(1)时间复杂度的查找，避免反复遍历整个参考列表。
3. 批量处理+批量写入：遍历交易表数据，标记需要填充的分类值，最后一次性更新到表格，避免逐单元格写入的开销。
4. 动态匹配列索引：通过表头获取列的位置，避免硬编码列序号，增强脚本的鲁棒性。

优化后的完整代码 #

function main(workbook: ExcelScript.Workbook) {
  // 获取两张目标表格
  const transactionsTable = workbook.getTable("table_transactions");
  const referenceTable = workbook.getTable("table_autoCatReference");

  // 1. 批量读取参考表数据，构建lookupWord -> matchedCategory的映射表
  const referenceData = referenceTable.getRange().getValues();
  const lookupMap = new Map<string, string>();
  // 跳过表头行，遍历参考表数据
  for (let i = 1; i < referenceData.length; i++) {
    const lookupWord = referenceData[i][0] as string; // lookupWord列
    const matchedCategory = referenceData[i][1] as string; // matchedCategory列
    if (lookupWord) {
      // 统一转为小写+去空格，避免大小写/空格导致的匹配失败
      lookupMap.set(lookupWord.trim().toLowerCase(), matchedCategory);
    }
  }

  // 2. 批量读取交易表数据（包含表头）
  const transactionsData = transactionsTable.getRange().getValues();
  const headers = transactionsData[0] as string[];
  // 通过表头名获取对应列的索引，避免硬编码列位置
  const descColIndex = headers.indexOf("description");
  const catColIndex = headers.indexOf("category");

  // 校验必要列是否存在
  if (descColIndex === -1 || catColIndex === -1) {
    console.log("交易表缺少必要列：description或category");
    return;
  }

  // 3. 遍历交易表数据，处理空分类的行
  for (let rowIndex = 1; rowIndex < transactionsData.length; rowIndex++) {
    const categoryValue = transactionsData[rowIndex][catColIndex];
    // 仅处理category为空的行
    if (!categoryValue || categoryValue.toString().trim() === "") {
      const description = (transactionsData[rowIndex][descColIndex] as string).toLowerCase();
      // 查找匹配的词汇，找到第一个匹配后终止循环
      for (const [word, category] of lookupMap.entries()) {
        if (description.includes(word)) {
          transactionsData[rowIndex][catColIndex] = category;
          break;
        }
      }
    }
  }

  // 4. 批量写入更新后的数据到交易表
  transactionsTable.getRange().setValues(transactionsData);
  console.log("分类填充完成");
}

额外优化细节说明 #

• 大小写不敏感匹配：将描述和查找词统一转为小写，避免因大小写差异导致匹配失败。
• 去空格处理：对查找词做trim处理，避免参考表中多余空格影响匹配精度。
• 提前终止匹配：找到第一个匹配的词后就停止循环，减少不必要的遍历操作。
• 列索引动态匹配：通过表头名获取列位置，即使表格列顺序调整，脚本依然能正常工作。

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