如何用Google Apps Script将嵌套JSON动态转换为表格？

我通过Google Apps Script的UrlFetchApp函数获取了嵌套JSON数据，现有代码是静态实现，但实际数据包含更多键，想知道如何动态提取items中的所有键并映射为独立列标题。

期望生成的列标题格式如下：

name, description, owner_display_name, owner_external_urls, owner_type, urls_site, images_1_height, images_1_url, images_2_height, images_2_url

嵌套JSON数据示例 #

{    
  "href":"https://api.example.com/",    
  "items":[
    {      
      "name":"test",      
      "description":"desc",      
      "owner":{
          "display_name":"name",          
          "external_urls":{"site":"www.example.com"},          
          "type":"user"      
      },      
      "urls":{"site":"www.google.com"},      
      "images":[
          {"height":640,"url":"www.google.com"},          
          {"height":300,"url":"www.google.com"}        
      ]    
    },
    {      
      "name":"test2",      
      "description":"desc2",      
      "owner":{
          "display_name":"name2",          
          "external_urls":{"site":"www.example.com"},          
          "type":"user2"      
      },      
      "urls":{"site":"www.google.com"},      
      "images":[
          {"height":640,"url":"www.google.com"},          
          {"height":300,"url":"www.google.com"}        
      ]    
    }  
  ]
}

我的静态实现代码 #

function JSONtoSheet() {  
  const json = '{ "href":"https://api.example.com/", "items":[ { "name":"test", "description":"desc", "owner":{ "display_name":"name", "external_urls":{"site":"www.example.com"}, "type":"user" }, "urls":{"site":"www.google.com"}, "images":[ {"height":640,"url":"www.google.com"}, {"height":300,"url":"www.google.com"} ] },{ "name":"test2", "description":"desc2", "owner":{ "display_name":"name2", "external_urls":{"site":"www.example.com"}, "type":"user2" }, "urls":{"site":"www.google.com"}, "images":[ {"height":640,"url":"www.google.com"}, {"height":300,"url":"www.google.com"} ] } ] }';  
  const data = JSON.parse(json);  
  const sheet = SpreadsheetApp.getActive().getSheetByName('Test');  
  sheet.clear();  
  var headers = [["Name","Description","Owner Display Name","Owner Type","Owner External URL","Site URL","Image Height 1","Image URL 1","Image Height 2","Image URL 2"]];  
  var range = sheet.getRange("A1:J1");  
  range.setValues(headers);  
  const items = data.items;  
  for (let i = 0; i < items.length; i++) {    
    const item = items[i];    
    const name = item.name;    
    const desc = item.description;    
    const owner = item.owner;    
    const ownerDisplayName = owner.display_name;    
    const ownerType = owner.type;    
    const ownerExternalURL = owner.external_urls.site;    
    const siteURL = item.urls.site;    
    const images = item.images;    
    const imageHeight1 = images[0].height;    
    const imageURL1 = images[0].url;    
    const imageHeight2 = images[1].height;    
    const imageURL2 = images[1].url;    
    sheet.getRange(i+2, 1).setValue(name);    
    sheet.getRange(i+2, 2).setValue(desc);    
    sheet.getRange(i+2, 3).setValue(ownerDisplayName);    
    sheet.getRange(i+2, 4).setValue(ownerType);    
    sheet.getRange(i+2, 5).setValue(ownerExternalURL);    
    sheet.getRange(i+2, 6).setValue(siteURL);    
    sheet.getRange(i+2, 7).setValue(imageHeight1);    
    sheet.getRange(i+2, 8).setValue(imageURL1);    
    sheet.getRange(i+2, 9).setValue(imageHeight2);    
    sheet.getRange(i+2, 10).setValue(imageURL2);  
  }
}

要实现动态提取嵌套JSON的键并生成对应列，核心是递归扁平化JSON对象，把嵌套结构转换成key: value的扁平格式，其中嵌套的键用下划线拼接（如owner_display_name）、数组元素用索引拼接（如images_1_height），自动覆盖所有可能的键。

动态实现代码 #

function JSONtoSheetDynamic() {
  // 替换为UrlFetchApp.fetch获取的实际JSON数据
  const json = '{ "href":"https://api.example.com/", "items":[ { "name":"test", "description":"desc", "owner":{ "display_name":"name", "external_urls":{"site":"www.example.com"}, "type":"user" }, "urls":{"site":"www.google.com"}, "images":[ {"height":640,"url":"www.google.com"}, {"height":300,"url":"www.google.com"} ] },{ "name":"test2", "description":"desc2", "owner":{ "display_name":"name2", "external_urls":{"site":"www.example.com"}, "type":"user2" }, "urls":{"site":"www.google.com"}, "images":[ {"height":640,"url":"www.google.com"}, {"height":300,"url":"www.google.com"} ] } ] }';
  const data = JSON.parse(json);
  const sheet = SpreadsheetApp.getActive().getSheetByName('Test');
  sheet.clear();

  // 递归扁平化单个item，处理嵌套对象和数组
  const flattenItem = (item, parentKey = '') => {
    let result = {};
    for (const key in item) {
      const currentKey = parentKey ? `${parentKey}_${key}` : key;
      if (typeof item[key] === 'object' && item[key] !== null && !Array.isArray(item[key])) {
        // 递归处理嵌套对象
        Object.assign(result, flattenItem(item[key], currentKey));
      } else if (Array.isArray(item[key])) {
        // 处理数组：给每个元素加1开始的索引
        item[key].forEach((element, index) => {
          Object.assign(result, flattenItem(element, `${currentKey}_${index + 1}`));
        });
      } else {
        // 基础类型直接赋值
        result[currentKey] = item[key];
      }
    }
    return result;
  };

  // 扁平化所有items数据
  const flattenedItems = data.items.map(item => flattenItem(item));

  // 提取所有唯一列标题（合并所有item的键）
  const allHeaders = [...new Set(flattenedItems.flatMap(item => Object.keys(item)))];

  // 写入表头
  sheet.getRange(1, 1, 1, allHeaders.length).setValues([allHeaders]);

  // 按表头顺序整理数据行，空值用空字符串填充
  const dataRows = flattenedItems.map(item => allHeaders.map(header => item[header] || ''));
  // 一次性写入所有数据（比逐个setValue效率高）
  sheet.getRange(2, 1, dataRows.length, dataRows[0].length).setValues(dataRows);
}

关键逻辑说明 #

• 递归扁平化：flattenItem遍历item的每个键，遇到嵌套对象就递归拼接键名，遇到数组就为元素添加索引后缀，最终把多层嵌套结构转换成单层键值对。
• 自动生成表头：收集所有扁平化后的键并去重，确保所有可能的字段都被列为表头。
• 高效写入数据：一次性批量写入表头和数据，避免循环调用setValue带来的性能问题。

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