Working With JSON in Google Apps Script

Subscribe to my newsletter and never miss my upcoming articles

Companies publish API's that give outsiders access to some of their data. For example, you can monitor and fetch live data from the aviation industry using the large number of API's currently available. You could also track the price of Bitcoin using the Coindesk API .

Usually, this data is structured in a format known as JSON. To access this data, it's very useful to know how JSON works, and how you can access this data using Google Apps Script.

I've shared three examples below that cover a basic understanding of fetching information from an API / endpoint (URL).

1. Access Parent Element

You can fetch data that is in the top level of the hierarchy. In the example below, you are trying to find out the value stored in element "year".

Sample JSON data:

{
"dataset": "Sports",
"year": "2020",
"month": "January",
}

Apps Script Code:

var url = "www.api-url.com";
var response = UrlFetchApp.fetch(url);
var dataset = JSON.parse(response);

var  a = dataset.year;
Logger.log(a);

Variable a will store the value "2020".

2. Access Nested JSON Data

In some cases, the data you need will be nested inside a parent category. In the example below, "parameters" is the parent category, and you are trying to find out the value stored in element "app".

Sample JSON data:

{
"dataset": "Sports",
"year": "2020",
"month": "January",
"parameters":
{
"access": "open",
"format": "json",
"app": "google sheets"
}
}

Apps Script Code:

var url = "www.api-url.com";
var response = UrlFetchApp.fetch(url);
var dataset = JSON.parse(response);

var  a = dataset.parameters.app;
Logger.log(a);

Variable a will store the value "google sheets".

3. Access JSON Data in an Array

When there are multiple data elements with a similar structure, the JSON data is stored in the form of an array. In the example below, you are trying to fetch data for the first player Wayne Rooney.

Sample JSON data:

{
"dataset": "Sports",
"year": "2020",
"month": "January",

"parameters":
{
"access": "open",
"format": "json",
"app": "google sheets"
},

"players":
[
{
"name": "Wayne Rooney",
"sport": "Football",
"retired": "Yes"
},
{
"name": "Sebastien Vettel",
"sport": "F1",
"retired": "No"
},
{
"name": "Michael Jordan",
"sport": "Basketball",
"retired": "Yes"
}
]
}

Apps Script Code:

var url = "www.api-url.com";
var response = UrlFetchApp.fetch(url);
var dataset = JSON.parse(response);

var  a = dataset.players[0].name;
var  b = dataset.players[0].sport;
var  c = dataset.players[0].retired;
Logger.log(a, b, c);

Readability

JSON data can often be difficult to read, especially when there are many variables involved. You can easily format the data and make it easier to read by using a live editor online such as JSON Viewer .

Here's a sample JSON dataset you can get started with, showing the live price of Bitcoin updated every minute: api.coindesk.com/v1/bpi/currentprice.json

No Comments Yet