Flattening deeeply nested objects to 2d table format

[Bruce Mcpherson]

Today's snippet.

Translating JSON data to a format suitable for display in sheets can be tricky if the data contains nested objects and arrays. What to do with header names in arrays?  

The usual approach is to make then into something like propa.1.propb.1.value , propa.1.propb.2.value etc. and repeat ad nauseam, but that isn't how we'd typically represent such data in a 2D table such as a sheet, and the result is pretty useless for any kind of analysis. Intead we'd repeat the common data on a separate row, in a format similar to the result of a complex SQL join.

For those of you who use my cUseful library, it's published in there, and you can use it like this, (where films is a complex object with lots of nested properties and arrays), and the result is a sheet populated with a flattened and expanded rows.

function test() {
  // open a sheet and write this stuff to it
  const sheet = SpreadsheetApp.openById('xxxxxxx').getSheetByName('films');
  const values = cUseful.Unnest.table({ ob: films });
  // clear it
  sheet.clear()
  // write the flattened data
  sheet.getRange(1, 1,values.length, values[0].length).setValues(values);

}

Here's a link to both an ES6 and an Apps Script version.

https://github.com/brucemcpherson/desktopliberation/issues/30

[--Hyde]

Hi Bruce,

Thank you for sharing this. Looks really handy for many use cases.

Cheers --Hyde