多工作簿ID匹配回填指定列数据的Google Apps Script修改需求

以下是修改后的代码，实现ID匹配并更新J、K列的功能，同时添加了onOpen触发逻辑：

// 主功能函数：匹配ID并更新J、K列
function updateMatchingIDData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dataSheet = ss.getSheetByName('Data');
  const idSheet = ss.getSheetByName('Sheet IDs');
  
  // 1. 获取Master Data表的所有数据（假设ID在A列，从第2行开始是数据行）
  const masterData = dataSheet.getRange(2, 1, dataSheet.getLastRow() - 1, dataSheet.getLastColumn()).getValues();
  // 记录需要更新的J(第10列)、K(第11列)的数组索引（数组从0开始，需减1）
  const colJIndex = 9;
  const colKIndex = 10;
  // 记录ID所在列的数组索引（假设ID在A列即索引0，若不是请修改）
  const idColIndex = 0;

  // 2. 收集所有目标工作簿的ID-JK映射
  const idToJKMap = {};
  const targetSheetName = 'Sep 2022'; // 目标工作表名称，可按需修改
  const workbookIDs = idSheet.getRange('A1:A').getValues().flat().filter(id => id);

  for (const workbookID of workbookIDs) {
    try {
      const targetSS = SpreadsheetApp.openById(workbookID);
      const targetSheet = targetSS.getSheetByName(targetSheetName);
      if (!targetSheet) {
        console.log(`工作簿${workbookID}中未找到${targetSheetName}工作表，跳过`);
        continue;
      }
      // 获取目标表的数据（从第2行开始，排除表头）
      const targetData = targetSheet.getRange(2, 1, targetSheet.getLastRow() - 1, targetSheet.getLastColumn()).getValues();
      // 构建ID到J、K值的映射
      targetData.forEach(row => {
        const id = row[idColIndex];
        if (id) { // 确保ID不为空
          idToJKMap[id] = [row[colJIndex], row[colKIndex]];
        }
      });
    } catch (e) {
      console.log(`处理工作簿${workbookID}时出错：${e.message}`);
    }
  }

  // 3. 更新Master Data表的J、K列数据
  masterData.forEach(row => {
    const id = row[idColIndex];
    if (idToJKMap[id]) {
      row[colJIndex] = idToJKMap[id][0];
      row[colKIndex] = idToJKMap[id][1];
    }
    // 未找到匹配ID的行，保持原数据不变
  });

  // 4. 将更新后的数据写回Data表
  dataSheet.getRange(2, 1, masterData.length, masterData[0].length).setValues(masterData);
  SpreadsheetApp.getUi().alert('数据更新完成');
}

// onOpen触发函数：打开工作簿时添加自定义菜单
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('数据更新')
    .addItem('匹配ID并更新J、K列', 'updateMatchingIDData')
    .addToUi();
}

关键修改说明 #

• 数据逻辑重构：从原有的合并追加逻辑改为构建ID映射表，通过对象键值对快速匹配ID，大幅提升处理效率，避免多层嵌套循环的冗余操作
• 列索引明确化：直接定义J、K列和ID列的数组索引（注意Google Sheets列从1开始，数组索引从0开始），如果你的ID列不是A列，修改idColIndex即可
• 容错机制添加：加入try-catch块和工作表存在性检查，避免单个工作簿出错导致整个流程中断，同时通过日志记录异常信息
• onOpen触发优化：通过自定义菜单实现手动触发（直接在onOpen中运行主函数可能引发性能问题），打开工作簿时自动注册菜单，操作更灵活
• 原数据保留：未找到匹配ID的行，J、K列将保持原有值，不会被清空

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