使用Google Apps Script替代VLOOKUP/IMPORTRANGE跨表填充数据

核心逻辑说明 #

1. 优先用哈希对象存储主库ID映射，查找复杂度为O(1)，远高于循环匹配的效率，适配100万行量级的主库查询需求
2. 仅读取两个表格的有效数据范围，避免整列读取大量空行浪费性能
3. 所有操作批量完成，减少Spreadsheet API调用次数，整体执行时间可以控制在10秒以内

完整可运行代码 #

function autoFillChildDB() {
  // 主数据库配置
  const masterSS = SpreadsheetApp.openById('ABC');
  const masterSH = masterSS.getSheetByName("MasterDB");
  // 仅读取主库有效数据范围的A-C列，避免读取整列空行
  const masterLastRow = masterSH.getLastRow();
  const masterData = masterSH.getRange(2, 1, masterLastRow - 1, 3).getValues();
  
  // 构建ID映射哈希表
  const idMap = {};
  masterData.forEach(row => {
    const id = row[0];
    if (id) { // 跳过ID为空的行
      idMap[id] = [row[1], row[2]];
    }
  });

  // 子数据库配置
  const childSS = SpreadsheetApp.openById('XYZ');
  const childSH = childSS.getSheetByName("ChildDB");
  const childLastRow = childSH.getLastRow();
  if (childLastRow < 2) return; // 子库无有效数据直接退出
  // 读取子库A列所有ID
  const childIds = childSH.getRange(2, 1, childLastRow - 1, 1).getValues();
  // 构建待写入的B、C列结果数组
  const output = childIds.map(row => {
    const id = row[0];
    return id && idMap[id] ? idMap[id] : ['', '']; // 未匹配到则留空
  });

  // 批量写入子库B、C列
  childSH.getRange(2, 2, output.length, 2).setValues(output);
}

优化配置建议 #

• 自动触发设置：在Apps Script编辑器的「触发」页面添加可安装触发，选择onChange事件触发，子库A列新增/修改ID时会自动填充对应字段，无需手动执行
• 性能进阶优化：如果主库数据更新频率低，可以将idMap存入PropertiesService做缓存，每次执行先判断主库是否有更新，无更新直接读缓存，进一步减少主库读取耗时
• 异常兼容：可以根据需求在未匹配到ID时填充#N/A或者自定义提示，只需修改上述代码中['', '']的部分即可

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