JSON Array to Google Sheets Table / CSV using Google Apps Script

I freaking did it! I got this code to work, and now it’s yours.

Situation: Often, when downloading data from a service, it comes as a JSON array.

But then, when uploading this data to a new service, it’s rare that the new service will be able to understand the JSON array format.

Similarly, it’s hard to manipulate the data in base form, because while JSON is human-readable, it’s not easy to manipulate.

So, I wanted to convert the JSON array to a Google Sheets table. To do this, I wrote a Google Apps Script to help me parse it.

The Problem — JSON is Hard to Read for Humans

A few services I’ve used recently that have outputted JSON array data have been:

• The Azure API, giving me a list of services it has on offer
• The Ghost blog exporter, exporting either lists of posts, or redirections, or other things
• The Spotify data exporter, when I decided to export all my data and use another service

To be able to use this JSON array data, I needed it to generally be a CSV. So, I had to convert the JSON array to a table, then to CSV format.

A JSON array is an array of JSON objects. Generally, the objects all have the same structure.

Here’s what some JSON data I downloaded looks like in base form. The data below is a 301 redirections table from Ghost, but it doesn’t matter wht it is, as long as it’s a JSON Array, and not an object. I just pasted it into the Google Sheet without formatting.

[
    {
        "from": "/best-looking-motorcycles/",
        "to": "https://motofomo.com/best-looking-motorcycles-of-2019/",
        "permanent": true
    },
    {
        "from": "/most-beautiful-motorcycles-2018/",
        "to": "https://motofomo.com/best-looking-motorcycles-of-2019/",
        "permanent": true
    },
    {
        "from": "/ghost-2-0-vs-wordpress-5-0/",
        "to": "/ghost-vs-wordpress/",
        "permanent": true
    },
    {
        "from": "/shoei-rf-1200-epic-review/",
        "to": "https://motofomo.com/shoei-rf-1200-review/",
        "permanent": true
    },
...

OK, now to convert it into a table, so I can mess with it.

One gotcha — make sure the table is still a standard JSON Array. Here’s a general definition of a JSON Array at w3schools.

What is a “JSON Array”?

Data services rarely send a complete JSON object. They just send an array.

Here are the main features of a JSON array:

• It starts and ends with square brackets, “[” and “]”.
• Usually, all the keys are the same. Some values may be blank. Or at least, the keys come from a limited list of possible keys.
• The values are usually strings, numbers, or boolean.

For example, here’s an example JSON array of a few employees.

[
  {
    "Name": "Andrew",
    "Role": "Senior Software Engineer",
    "Start date": "2012-06-15",
    "Battle weakness": "Heel"
  },
  {
    "Name": "Bahman",
    "Role": "Very Senior Software Engineer",
    "Start date": "2014-03-22",
    "Battle weakness": "Kryptonite"
  },
  {
    "Name": "Carlos",
    "Role": "Underpaid Operations Guy",
    "Start date": "2010-11-05",
    "Battle weakness": "Gets bored quickly"
  },
  {
    "Name": "Dewei",
    "Role": "Secretly the CFO",
    "Start date": "2013-08-19",
    "Battle weakness": "Extremely Small"
  }
]

That’s some good data. I’ll use it in my example sheet (linked here).

One “gotcha”: Make sure that the data pastes properly into Google Sheets. Sometimes, Google Sheets may do things like remove quote marks, which will make your JSON invalid.

The Code to Convert a JSON Array to a Table

Below is the code for the Google Apps Script to convert JSON to table format.

I’ve left some debugging lines in there, which you can uncomment to see if anything’s going wrong.

And instead of throwing an error, I have it return the error to Google Sheets so you can see it easily.

The way it works is:

1. It gets all the data and joins it into one string
2. It parses that string, and creates a JSON object
3. It parses the JSON object to make a table.

// usage: =JSONtoTable("SheetName")
// Output goes into a table starting from that cell

function JSONtoTable(sheetName) {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet;

  if(sheetName) {
    sheet = spreadsheet.getSheetByName(sheetName);
    if(!sheet) {
      return "Sheet not found: "+ sheetName;
    }
  }

  // retrieve all data
  var data = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();
  
  // Concatenate all rows to form a complete JSON string
  var jsonString = data.map(row => row.join("")).join("");

  // Parse the JSON string to get the JSON object
  var jsonObject;
  try {
    // return jsonString;
    jsonObject = JSON.parse(jsonString);
  } catch (e) {
    return 'Error parsing JSON: ' + e;
  }

  // If you want to flatten the JSON object into a table, you can do so here
  try {
    // return JSON.stringify(jsonObject, null, 2);
    var flattenedJson = flattenJson(jsonObject);
    return flattenedJson;
  } catch (e) {
    return 'Error in flattening JSON or setting values:' + e;
  }
}

function flattenJson(data) {
  if (!Array.isArray(data) || data.length === 0) {
    return "Input data is not a valid non-empty array";
  }
  var result = [];
  // Getting the union of all keys in the data array objects to ensure we cover all fields
  var headers = [...new Set(data.flatMap(Object.keys))];

  result.push(headers);

  data.forEach(function(row) {
    var rowData = [];
    headers.forEach(function(header) {
      rowData.push(row[header] === undefined ? "" : row[header]);
    });
    result.push(rowData);
  });

  return result;
}

Using the Code — Testing

In Google Sheets

• In the menu bar, click on “Extensions” then select “Apps Script”.
• Paste in the script above
• Give it a name, e.g. JSON to Table
• Save

Then, in Google Sheets, you can use the function JSONtoTable().

I’ve tested this with some data and you can copy it here if you want to play with it yourself.

Wrap up

If you like this, I’d appreciate a note of thanks, or a “hello”!