如何在Google Apps Script中通过外键关联两个Google Sheets表格

针对需求，这里提供两种通用解决方案，无需手动逐行硬编码，适配不同数据量与列结构：

方案一：利用内置公式批量关联（简洁高效） #

通过脚本调用Google Sheets的VLOOKUP函数批量匹配数据，最后转为静态值避免依赖原表：

function mergeSheetsWithFormulas() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const nameSheet = ss.getSheetByName("NAME");
  const ageSheet = ss.getSheetByName("AGE");
  const targetSheet = ss.getSheetByName("MERGED") || ss.insertSheet("MERGED");
  
  // 清空目标表原有内容
  targetSheet.clearContents();
  
  // 设置合并表表头
  targetSheet.getRange(1, 1, 1, 3).setValues([["Name", "id", "age"]]);
  
  const ageDataRowCount = ageSheet.getLastRow() - 1; // 排除表头
  
  // 批量设置公式匹配数据
  targetSheet.getRange(2, 2, ageDataRowCount, 1).setFormula('=AGE!A2');
  targetSheet.getRange(2, 1, ageDataRowCount, 1).setFormula('=VLOOKUP(B2, NAME!A:B, 2, FALSE)');
  targetSheet.getRange(2, 3, ageDataRowCount, 1).setFormula('=AGE!B2');
  
  // 将公式结果转为静态值
  targetSheet.getDataRange().copyTo(
    targetSheet.getDataRange(),
    SpreadsheetApp.CopyPasteType.PASTE_VALUES,
    false
  );
}

说明 #

• 自动适配任意数据行数，无需手动指定范围
• 转静态值后，合并表不再依赖原表数据，修改原表不会影响合并结果

方案二：纯脚本通用关联（灵活无公式依赖） #

如果需要脱离Sheet公式功能，用纯脚本实现通用关联逻辑，适配任意列位置（只要存在id关联列）：

function mergeSheetsGeneric() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const nameSheet = ss.getSheetByName("NAME");
  const ageSheet = ss.getSheetByName("AGE");
  const targetSheet = ss.getSheetByName("MERGED") || ss.insertSheet("MERGED");
  
  // 获取表头和数据（自动识别范围）
  const [nameHeaders, ...nameRows] = nameSheet.getDataRange().getValues();
  const [ageHeaders, ...ageRows] = ageSheet.getDataRange().getValues();
  
  // 找到id列的索引（通用适配列位置）
  const nameIdColIndex = nameHeaders.indexOf("id");
  const ageIdColIndex = ageHeaders.indexOf("id");
  
  // 构建id到NAME表数据的映射
  const idNameMap = {};
  nameRows.forEach(row => {
    const rowId = row[nameIdColIndex];
    idNameMap[rowId] = row;
  });
  
  // 生成合并表头（NAME表头 + AGE表头去重id）
  const mergedHeaders = [
    ...nameHeaders,
    ...ageHeaders.filter(header => header !== "id")
  ];
  
  // 生成合并数据
  const mergedRows = ageRows.map(row => {
    const rowId = row[ageIdColIndex];
    const matchedNameRow = idNameMap[rowId] || [];
    // 合并行数据，去掉重复的id列
    return [...matchedNameRow, ...row.filter((_, idx) => idx !== ageIdColIndex)];
  });
  
  // 写入目标表
  targetSheet.clearContents();
  targetSheet.getRange(1, 1, 1, mergedHeaders.length).setValues([mergedHeaders]);
  if (mergedRows.length > 0) {
    targetSheet.getRange(2, 1, mergedRows.length, mergedHeaders.length).setValues(mergedRows);
  }
}

说明 #

• 无需硬编码列位置或列数量，只要两张表都有id列即可关联
• 自动合并NAME表的所有列和AGE表中除id外的所有列，扩展性强
• 纯脚本处理，适合后续需要对合并数据做额外加工的场景

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