使用应用程序脚本将嵌套的Json数据解析为Google表格 [英] Parse Nested Json Data to Google Sheets with App Script

问题描述

我正在尝试将JSON解析为Google表格.除了我发现JSON具有需要作为列返回的子项之外，其他所有东西都像魅力一样发挥作用.

i'm trying to parse a JSON into a Google Sheets. Everything work like a charm except that i figured out JSON has childitems that i need to return as columns.

Json示例

{
  "status": "OK",
  "items": [
    {
      "order_id": 1290,
      "date": "2019-12-24",
      "services": [
        {
          "start_at": "08:00",
          "end_at": "09:00",
          "service_id": 121,
          "assistant_id": 0
        }, 
        {
          "start_at": "12:00",
          "end_at": "13:00",
          "service_id": 122,
          "assistant_id": 0
        }
      ],
    }
  ]
}

我需要进入Google表格的是

And what i need to get into Google Sheets is

order_id|date       |services.start_at|services.end_at|services.service_id|services.assistant_id|
1290    |2019-12-24 |08:00            |09:00          |121                |0                    |
1290    |2019-12-24 |12:00            |13:00          |122                |0                    |

我已经尝试过了，但是如果有多个服务在同一个order_id下，我不知道如何获取子项并重复id和date的值.

I have trying this but i don't figure out how to get child items and repeat values for id and date if more than one service is under the same order_id.

   var response = UrlFetchApp.fetch(url, options);
   var object = JSON.parse(response.getContentText());
   var headers = Object.keys(object.items[0]);
   var values = object.items.map(function(e) {return headers.map(function(f) {return e[f]})});
   values.unshift(headers);
   var sheet = SpreadsheetApp.getActiveSheet();
   sheet.getRange(2, 1, values.length, values[0].length).setValues(values);

我希望这里的任何人都能给我一个线索，因为我已经尝试了好几天没有运气，我是一个使用JSON并在Apps Script中解析内容的菜鸟.

I hope anyone here can give me a clue because I had tried several days without luck, i'm a noob with JSON and parsing stuff in Apps Script.

祝您有美好的一天:)

推荐答案

您可能想创建一个函数，给定一个多级对象，该函数将返回一个平面的行数组，每个行都有其属性.

You might want to create a function, which, given a multilevel object, returns a flat array of rows, each with its properties.

注意:由于我不知道是否允许使用ES6语法，因此我使用了更老，更冗长但更兼容的ES5语法(这使我意识到我有多爱ES6及其快捷方式！)

演示

function buildRows(data) {
  return data.items.reduce(function(res, item) {
    var sharedProps = {}, // item properties
        items       = []; // services

    // For each item [key, value] pair
    Object.entries(item).forEach(function(pair) {
      var key = pair[0],
        value = pair[1];
      // If the value is an Array (services)
      if (value instanceof Array) {
        // Add the item, prefixed with the key ("services")
        [].push.apply(items,
          value.map(function(obj) {
            return Object.fromEntries(
              Object.entries(obj).map(function(pair) {
                return [key + '.' + pair[0], pair[1]]
              })
            );
          })
        );
      } else {
        // Otherwise, add it as a shared property
        sharedProps[key] = value;
      }
    });

    // Add the shared props to each item (service) before adding them
    return res.concat(
      items.map(function(item) {
        return Object.assign({}, sharedProps, item);
      })
    );
  }, []);
}

// For the demo
var object = {"status":"OK","items":[{"order_id":1290,"date":"2019-12-24","services":[{"start_at":"08:00","end_at":"09:00","service_id":121,"assistant_id":0},{"start_at":"12:00","end_at":"13:00","service_id":122,"assistant_id":0}]}]};

// Build the rows
var rows = buildRows(object);
// Get the headers
var headers = Object.keys(rows[0]);
// Get the values
var values = rows.map(function(row) {
  return headers.map(function(header) {
    return row[header];
  });
});
// Do what you need to do to insert them in the Sheet,
// this is just for the demo:
result.innerHTML = headers.join('\t') + '\n'
                 + values.map(function(row) {
                     return row.join('\t\t');
                   }).join('\n');

<pre id="result"></pre>

这篇关于使用应用程序脚本将嵌套的Json数据解析为Google表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！