如何使用Google Script基于关联键合并两个Google Sheet的数据？

需求说明 #

需要修改现有Google Apps Script代码，将list_film表中的id字段与list_review表中的id_film字段关联，生成包含电影信息对应评论信息的合并JSON数据，输出格式如下：

[
  {
    "id": "1",
    "year": "2019",
    "title": "The Aeronauts",
    "img": "null",
    "id_review": "1",
    "id_film": "1",
    "review": "It's an eye opener movie for the youngsters who are on the path of terrorism for the greed of fake Hoors."
  },
  {
    "id": "2",
    "year": "2019",
    "title": "Bluebird",
    "img": "null",
    "id_review": "2",
    "id_film": "2",
    "review": "What is thought provoking in this movie?"
  }
]

修改后的完整代码 #

function doGet(e) {
  var page = e.parameter.page || 1;
  var limit = e.parameter.limit || 10;
  var search = e.parameter.search || "";
  var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/10S8Igzt1tpqUBgJHVKpny-2s6G3Y3-vFsLMvlZVqpkc/edit#gid=12237511399");
  var filmSheet = ss.getSheetByName("list_film");
  var reviewSheet = ss.getSheetByName("list_review");
  return getMergedData(filmSheet, reviewSheet, page, limit, search);
}

function getMergedData(filmSheet, reviewSheet, page, limit, search){
  var lowCaseSearch = search.toString().toLowerCase();
  
  // 获取电影数据并过滤搜索条件（跳过表头）
  var filmRows = filmSheet.getDataRange().getValues();
  var filteredFilms = filmRows.slice(1).filter(([,,c]) => c.toString().toLowerCase().includes(lowCaseSearch));
  
  // 获取评论数据并构建id_film到评论的映射（跳过表头）
  var reviewRows = reviewSheet.getDataRange().getValues().slice(1);
  var reviewMap = reviewRows.reduce((map, [idReview, idFilm, review]) => {
    if(!map[idFilm]) map[idFilm] = [];
    map[idFilm].push({id_review: idReview, id_film: idFilm, review: review});
    return map;
  }, {});
  
  // 合并电影和评论数据
  var mergedArray = filteredFilms.flatMap(([filmId, year, title, img]) => {
    var reviews = reviewMap[filmId] || [];
    // 无评论时保留电影基础信息
    if(reviews.length === 0){
      return [{
        id: filmId,
        year: year,
        title: title,
        img: img || "null",
        id_review: "null",
        id_film: filmId,
        review: "null"
      }];
    }
    // 有评论时每条评论对应一条合并数据
    return reviews.map(review => ({
      id: filmId,
      year: year,
      title: title,
      img: img || "null",
      ...review
    }));
  });
  
  // 分页处理
  var startIndex = limit * (page - 1);
  var paginatedData = mergedArray.slice(startIndex, startIndex + parseInt(limit));
  
  // 返回JSON格式结果
  return ContentService.createTextOutput(JSON.stringify(paginatedData))
    .setMimeType(ContentService.MimeType.JSON);
}

关键修改说明 #

• 双表读取：新增读取list_review表数据，不再仅处理单表
• 评论映射构建：通过reduce方法将评论数据按id_film分组，实现电影与评论的快速匹配
• 数据合并：使用flatMap完成电影数据与对应评论的一对多合并，无评论的电影也会保留基础信息
• 分页逻辑优化：针对合并后的全量数据执行分页操作，保证分页结果准确
• 字段格式对齐：修正原代码中字段名不一致问题（将titles调整为title），确保输出格式与需求完全匹配

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