Google Apps Script多列VLOOKUP批量填充整表方法问询

原有代码问题 #

你之前写的版本存在3个核心问题，导致只能处理单行、运行效率低：

• 逐单元格调用getValue/setValue读写数据，Google表格API调用开销极大，数据量稍大就会超时
• 仅写死了第2行的处理逻辑，没有遍历所有待匹配行
• 数组处理逻辑存在bug：indexOf传入数组参数、join+split的数组转换方式容易出现类型不匹配导致匹配失败

优化后可直接运行的代码 #

核心逻辑是全量读入数据到内存、用哈希表做快速匹配、批量一次性写入结果，万行级数据也能在数秒内跑完：

function khalookup(){
  // 配置参数，可根据实际表结构调整
  const SOURCE_SHEET_ID = "mysheetid"; // 替换为你的源表格ID
  const SOURCE_START_ROW = 3; // 源表数据从第3行开始
  const SOURCE_MATCH_COL = 0; // 源表匹配键在A列，对应数组索引0
  const SOURCE_FETCH_COLS = [4,5,6,7,12]; // 要取的E/F/G/H/M列，对应数组索引4/5/6/7/12
  const TARGET_START_ROW = 2; // 目标表数据从第2行开始
  const TARGET_MATCH_COL = 1; // 目标表匹配键在B列，对应数组索引1
  const TARGET_WRITE_COL = 10; // 结果从K列开始写入，K列对应数组索引10

  // 获取工作表实例
  const targetSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const sourceSheet = SpreadsheetApp.openById(SOURCE_SHEET_ID).getSheetByName("data");

  // 一次性读取源表全量有效数据，构建匹配映射
  const sourceLastRow = sourceSheet.getLastRow();
  const sourceRange = sourceSheet.getRange(SOURCE_START_ROW, 1, sourceLastRow - SOURCE_START_ROW + 1, 13);
  const sourceData = sourceRange.getValues();
  const lookupMap = new Map();
  for (const row of sourceData) {
    const matchKey = row[SOURCE_MATCH_COL];
    if (!matchKey) continue; // 跳过空ID行
    const fetchValues = SOURCE_FETCH_COLS.map(colIdx => row[colIdx]);
    lookupMap.set(matchKey, fetchValues);
  }

  // 一次性读取目标表待匹配ID
  const targetLastRow = targetSheet.getLastRow();
  const targetIdRange = targetSheet.getRange(TARGET_START_ROW, TARGET_MATCH_COL + 1, targetLastRow - TARGET_START_ROW + 1, 1);
  const targetIds = targetIdRange.getValues();

  // 内存中批量完成所有匹配
  const result = targetIds.map(row => {
    const key = row[0];
    return lookupMap.get(key) || Array(SOURCE_FETCH_COLS.length).fill("");
  });

  // 一次性写入所有匹配结果
  targetSheet.getRange(TARGET_START_ROW, TARGET_WRITE_COL + 1, result.length, SOURCE_FETCH_COLS.length).setValues(result);
}

使用说明 #

• 使用前先把代码里的mysheetid替换为你实际的源表格ID
• 如果表的起始行、匹配列、提取列有调整，直接修改代码开头的配置参数即可，不用改核心逻辑
• 匹配不到的ID会自动填充空值，不会抛出报错中断运行

效果参考 #

源表结构 #

目标表填充后效果 #

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