如何用Apps Script基于ORDER列合并两表格生成第三张表

已通过Google Apps Script获取到两份报告的有效数据数组，需要基于共同的ORDER列合并生成目标格式的报告3，以下是完整实现方案：

完整代码实现 #

function mergeReports() {
  var report1_ID = "1keGWvdMoNLPR1S2n_66QOyy-c3euZs0HOcn9qLLt9L8";
  var report2_ID = "1HpDkgpXHTY5aI7uKkcY5a6ZEQ--T860aGaRhlwhRT0P";

  var report1_page = "report1";
  var report2_page = "report2";

  var report1_range = "A6:D";
  var report2_range = "A6:D";

  // 获取并过滤空行数据
  var report1_values = SpreadsheetApp.openById(report1_ID).getSheetByName(report1_page).getRange(report1_range).getValues();
  var report2_values = SpreadsheetApp.openById(report2_ID).getSheetByName(report2_page).getRange(report2_range).getValues();

  var report1 = report1_values.filter(r => r.join("").length > 0);
  var report2 = report2_values.filter(r => r.join("").length > 0);

  // 将report2数据转换为以ORDER为键的映射表，快速查找
  var report2Map = {};
  report2.forEach(row => {
    var order = row[2]; // report2的ORDER列是第3列（索引2）
    report2Map[order] = {
      type: row[0],
      subtype: row[1],
      requests: row[3]
    };
  });

  // 将report1数据转换为以ORDER为键的映射表，快速查找
  var report1Map = {};
  report1.forEach(row => {
    var order = row[2]; // report1的ORDER列是第3列（索引2）
    report1Map[order] = {
      house: row[0],
      subtype: row[1],
      requests: row[3]
    };
  });

  // 收集所有ORDER值并排序，确保不遗漏任何记录
  var allOrders = [...new Set([...report1.map(r => r[2]), ...report2.map(r => r[2])])].sort((a, b) => a - b);

  // 构建合并后的报告数据
  var mergedReport = [["ORDER", "TYPE", "SUBTYPE", "HOUSE", "REQUESTS"]]; // 表头
  allOrders.forEach(order => {
    var type = report2Map[order]?.type || "";
    var subtype = report2Map[order]?.subtype || report1Map[order]?.subtype || "";
    var house = report1Map[order]?.house || "";
    var requests = report2Map[order]?.requests || report1Map[order]?.requests || "";
    mergedReport.push([order, type, subtype, house, requests]);
  });

  // 将合并后的数据写入新表格（默认写入当前脚本绑定的表格，可按需修改）
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("report3") || SpreadsheetApp.getActiveSpreadsheet().insertSheet("report3");
  sheet.clear();
  sheet.getRange(1, 1, mergedReport.length, mergedReport[0].length).setValues(mergedReport);

  Logger.log("合并完成，已生成report3");
}

关键逻辑说明 #

• 构建数据映射表：把两份报告的数据转换成以ORDER为键的对象，通过ORDER值直接调取对应行数据，避免嵌套循环，提升处理效率。
• 收集全量ORDER值：合并两份报告的ORDER值并去重排序，确保不会遗漏仅在单份报告中存在的记录。
• 组装合并数据：遍历所有ORDER值，从两个映射表中提取对应字段，缺失字段用空字符串填充，严格匹配目标格式。
• 写入结果表格：自动创建或复用目标表格，清空原有内容后写入合并数据。

原始报告数据 #

报告1数据 #

报告2数据 #

期望合并结果 #

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