Parse Nested Json Data to Google Sheets with App Script

Question

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 Example

{
  "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
        }
      ],
    }
  ]
}

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                    |

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);

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.

Have a nice day :)

I think that there might be an error in your sample value of "Json Example". Can you confirm the property of services again? — Tanaike
– Tanaike, Commented Jul 7, 2020 at 1:02
I just edited the json in order to keep it simple, and made a mistake. — Saul Da Silva
– Saul Da Silva, Commented Jul 7, 2020 at 1:18
Thank you for replying and updating it. I noticed that an answer has already been posted. In this case, I would like to respect the existing answer. I think that it will resolve your issue. — Tanaike
– Tanaike, Commented Jul 7, 2020 at 1:48

blex · Accepted Answer · 2020-07-07 01:37:24Z

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

Note: since I don't know if ES6 syntax is allowed, I used the older, more verbose but more compatible ES5 syntax (and that makes me realize how much I love ES6 and its shortcuts!)

Demo

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>

Thank you, indeed worked as expected. Flattening results was the key to solve it. Have a great day.

Collectives™ on Stack Overflow

Parse Nested Json Data to Google Sheets with App Script

1 Answer 1

Demo

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Demo

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related