如何合并多范围避免Google Apps Script中VLOOKUP逻辑的数据覆盖

问题分析 #

你当前的代码确实存在循环覆盖数据的问题：每次遍历子工作簿时，都会重新生成匹配结果并写入主表的同一列（G列开始），导致前一次的匹配数据被后一次覆盖。

将所有子工作簿的数据合并成一个统一的查找对象是完全可行的解决方案，这样可以一次性完成所有ID的匹配，避免重复写入覆盖的问题。

修复后的代码 #

function updateMaster() {
  const mss = SpreadsheetApp.getActiveSpreadsheet();
  const msh = mss.getSheetByName('Data');
  const mDB = msh.getRange("A2:A" + msh.getLastRow()).getValues(); // 获取主表所有ID

  const ish = mss.getSheetByName('Sheet IDs'); 
  const ivs = ish.getRange('A1:A' + ish.getLastRow()).getValues().flat(); 

  // 初始化全局查找对象，存储所有子工作簿的ID对应数据
  const globalLookupObj = {};

  ivs.forEach(id => {
    try {
      const ss = SpreadsheetApp.openById(id);
      const sh = ss.getSheetByName('Sep 22');
      const vs = sh.getRange("A2:L" + sh.getLastRow()).getValues();
      
      // 将当前子工作簿的数据合并到全局查找对象
      vs.forEach(row => {
        const [recordId, , , , , , , , , , , ...targetData] = row;
        // 若ID已存在，此处逻辑为覆盖原有值，可根据需求调整
        if (recordId) {
          globalLookupObj[recordId] = targetData.slice(0, 2); // 保留2列数据，与原逻辑一致
        }
      });
    } catch (e) {
      Logger.log(`处理工作簿ID ${id} 时出错：${e.message}`);
    }
  });

  // 生成主表待写入的结果数组
  const values = mDB.map(([id]) => {
    return globalLookupObj[id] || ["", ""]; // 无匹配时返回空值
  });

  // 一次性写入主表，避免多次写入导致的覆盖问题
  msh.getRange(2, 7, values.length, values[0].length).setValues(values);
}

关键优化点 #

• 全局查找对象：创建globalLookupObj统一存储所有子工作簿的ID-数据映射，避免循环内重复写入主表
• 合并逻辑：遍历每个子工作簿时，将数据合并到全局对象，ID重复时可自定义覆盖/保留规则
• 单次写入：所有数据处理完成后一次性写入主表，既提升执行效率，又彻底解决覆盖问题
• 错误捕获：增加try-catch处理单个工作簿访问失败的情况，避免整个脚本中断

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