Unlocking Your Data: Importing JSON Into Google Sheets With Google Apps Script

Ever found yourself staring at a JSON file, wishing you could just… plop it into a Google Sheet? You know, make it all neat and tidy in rows and columns, ready for analysis or sharing? It’s a common desire, especially when you're pulling data from an API or dealing with structured information that just begs to be spreadsheet-friendly.

Well, I’ve been there, and thankfully, it’s entirely achievable with a little help from Google Apps Script. Think of it as your personal bridge between the structured world of JSON and the familiar grid of Google Sheets.

Let's say you've successfully fetched some JSON data, perhaps from a web service. You've got it parsed into a JavaScript object, and you're looking at something like this:

[
  { "id": 3428, "type": "SUPER", "firstName": "Nikhil", "lastName": "Dingane", ... },
  { "id": 3439, "type": "NONE", "firstName": "NikhilUpdated", "lastName": "Dingane", ... }
]

This is a classic array of objects, and the goal is to get each object's properties into a row, with the property names as headers.

The Direct Approach: setValues()

If your JSON is already in a format that looks like a 2D array (or can be easily converted), the SpreadsheetApp.getActiveSheet().getRange().setValues() method is your best friend. It’s designed to take a two-dimensional array and blast it directly into your sheet.

Here’s a snippet that shows how you might do this, assuming dataAll holds your parsed JSON:

var ss = SpreadsheetApp.getActive();
var sheet = ss.getActiveSheet();

// Assuming dataAll is an array of arrays, where each inner array is a row
// For example: [['id', 'type', 'firstName'], [3428, 'SUPER', 'Nikhil'], ...]
var range = sheet.getRange(1, 1, dataAll.length, dataAll[0].length);
range.setValues(dataAll);

This is super efficient if your data is already in that perfect tabular shape. But what if it’s not quite there yet?

Transforming JSON into a 2D Array

Often, your JSON will be an array of objects, like the example above. To use setValues(), you need to convert this into a 2D array. This is where JavaScript's array methods come in handy.

One elegant way to do this is by combining Object.keys() and Array.map(). The idea is to first get the headers (the keys from the first object) and then map each object to an array of its values.

Consider this approach:

const data = [
  { "id": 3428, "type": "SUPER", "firstName": "Nikhil", ... },
  { "id": 3439, "type": "NONE", "firstName": "NikhilUpdated", ... }
];

// Get the headers from the first object
const headers = Object.keys(data[0]);

// Map each object to an array of its values
const rows = data.map(obj => Object.values(obj));

// Combine headers and rows into a single 2D array
const outputArray = [headers, ...rows];

// Now you can use setValues with outputArray
// SpreadsheetApp.getActive().getSheets()[0].getRange(1, 1, outputArray.length, outputArray[0].length).setValues(outputArray);

This method is quite clean. Object.keys(data[0]) grabs all the property names from the first JSON object, giving you your column headers. Then, data.map(obj => Object.values(obj)) iterates through each object in your JSON array and extracts its values, creating an array of arrays for your data rows. Finally, we combine the headers and rows into a single outputArray ready for setValues().

A More Manual, Yet Flexible, Iteration

Sometimes, you might prefer a more explicit loop, especially if you need to handle nested JSON or perform transformations along the way. The reference material also shows a loop-based approach:

var array = [];
for (var i = 0; i < json.length; i++) {
  var newJson = Object.keys(json[i]).map(function(key) {
    return [key, json[i][key]];
  });
  for (var j = 0; j < newJson.length; j++) {
    array.push(newJson[j]);
  }
}
// ... then use setValues with this 'array'

While this example pushes key-value pairs as separate elements, the core idea of iterating and building an array is sound. You'd adapt this to create your desired 2D structure. For instance, you might build each row as a single array within the outer loop.

Putting It All Together

So, the general workflow looks like this:

1. Fetch and Parse: Use UrlFetchApp.fetch() to get your data and JSON.parse() to convert the response text into a JavaScript object.
2. Transform: Convert your parsed JSON (likely an array of objects) into a 2D array suitable for setValues(). This is where Object.keys(), Object.values(), and Array.map() shine, or you can use explicit loops.
3. Write to Sheet: Get the active spreadsheet and sheet, determine the correct range, and use range.setValues() to populate it.

It’s a powerful combination that can save you a ton of manual data entry and manipulation. Give it a try – you might be surprised at how smoothly your JSON data flows into your spreadsheets!