应用程序脚本：将嵌套JSON转换为表？

我使用Google Apps Script urlFetchApp函数创建了以下嵌套JSON。如何动态从项目中获取所有键并将它们Map到各自的列标题？实际数据有更多的键，因此寻找一种动态的方式。
列应该是，

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

{
    "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);
  }
}

你可以使用这段代码来动态地从条目中获取所有的键，并将它们Map到它们自己的单独的列标题：

function JSONtoSheet() {
      const sheet = SpreadsheetApp.getActive().getSheetByName("Test");
      sheet.clear();
    
      // Get all unique keys from items
      const keys = new Set();
      const items = data.items;
      for (let i = 0; i < items.length; i++) {
        const item = items[i];
        for (const key in item) {
          if (item.hasOwnProperty(key)) {
            keys.add(key);
          }
        }
      }
    
      // Convert Set to Array and sort alphabetically
      const sortedKeys = Array.from(keys).sort();
    
      // Create headers from sorted keys
      const headers = [["Name", "Description"].concat(sortedKeys)];
    
      // Set header values
      const headerRange = sheet.getRange(1, 1, 1, headers[0].length);
      headerRange.setValues(headers);
    
      // Set item values
      for (let i = 0; i < items.length; i++) {
        const item = items[i];
        const row = [item.name, item.description];
        for (let j = 0; j < sortedKeys.length; j++) {
          const key = sortedKeys[j];
          if (item.hasOwnProperty(key)) {
            row.push(item[key]);
          } else {
            row.push("");
          }
        }
        const rowRange = sheet.getRange(i + 2, 1, 1, row.length);
        rowRange.setValues([row]);
      }
    }

应用程序脚本：将嵌套JSON转换为表？

1条答案

相关问题

热门标签

最新问答