Parsing JSON Data Array by Expanding Header Row using Google Apps Script

GasTips / spreadsheet
Google Apps Script / spreadsheet

Gists

This is a sample script for parsing JSON data array by expanding the header row using Google Apps Script.

Sample script

function myFunction() {
  const obj = [
    { key1: "value1", key2: "value2", key3: "value3" },
    { key4: "value1", key5: "value2", key6: "value3" },
    { key7: "value1", key8: "value2", key9: "value3" },
  ];

  const headers = Object.keys(
    obj.reduce((o, e) => (Object.keys(e).forEach((k) => (o[k] = true)), o), [])
  );
  const values = [headers, ...obj.map((o) => headers.map((k) => o[k] || null))];

  SpreadsheetApp.getActiveSheet()
    .clearContents()
    .getRange(1, 1, values.length, values[0].length)
    .setValues(values);
}

Testing

When this script is run, the following result is obtained. It is found that the header row is expanded by including all keys.

 Share!