Consuming JSON Web Data Using Google Sheets

Note: This article has been superseded with Fetching, Filtering, and Sorting JSON APIs in Google Sheets: The Missing Functions which provides a set of Google Sheets functions with additional capabilities.

I’ve said it before, and I’ll say it again.  JSON has become the lingua franca for exchanging data on the web, and we (as developers) need to know how to process JSON data received from RESTful web services so we can be positioned for the present and for the future.

My article on Consuming Node.js Microservices Created with stdlib provided a high-level tour covering several methods of parsing and processing JSON data. Last time, we focused on how to consume JSON data using curl and jq. Today, we’re zooming in and learning more about consuming Web API JSON data using Google Sheets. Once again, we’ll be consuming data from a microservice created with the excellent stdlib platform. Our learning, however, will be universally applicable for consuming Web API JSON data from any http endpoint. I am very impressed with the power and versatility of the Google Sheets platform for consuming JSON data (among many other things).  Let’s get started and experience it firsthand!

Article contents

Weather web API overview – current weather conditions

I created a Weather microservice (see documentation) using stdlib that we’ll use for our tutorial today. Let’s first focus on consuming JSON data using Google Sheets to retrieve the current weather conditions:

The current function accepts two parameters:

• loc: location (example: San Diego, CA)
• deg: degree type (F or C) Default is F.

Example: Return a JSON object with the current weather info (deg F) in San Diego

https://thisdavej.azurewebsites.net/api/weather/current?loc=San Diego,CA&deg=F

This will return a JSON object with the following structure:

{
  "temperature": "54",
  "skycode": "29",
  "skytext": "Partly Cloudy",
  "date": "2017-01-21",
  "observationtime": "06:20:00",
  "observationpoint": "San Diego, CA",
  "feelslike": "54",
  "humidity": "69",
  "winddisplay": "15 mph West",
  "day": "Saturday",
  "shortday": "Sat",
  "windspeed": "15 mph",
  "imageUrl": "http://blob.weather.microsoft.com/static/weather4/en-us/law/29.gif",
  "degType": "F"
}

Nice and simple. Let’s use Google Sheets to retrieve the current weather from this cloud-based microservice!

Using built-in IMPORTDATA function

In this first example, we use the built-in IMPORTDATA function to retrieve data from our weather service and display the results. Go ahead and create a Google Sheet as shown here:

	A	B
1	City
2	San Diego, CA	=IMPORTDATA(“https://thisdavej.azurewebsites.net/api/weather/current?loc=” & substitute(A2, ” “, “%20”))

Make sure that several cells are empty below cell B2 because the IMPORTDATA function will populate these with the lines of JSON text returned. If the cells below cell B2 are not empty (feel free to try it), Sheets will yield an error indicating that the array could not be expanded because it would overwrite data in the cells below.

After entering the formula in B2, you should see results that look like this:

	A	B
1	City
2	San Diego, CA	{
3		temperature: “54”
4		skycode: “29”
5		skytext: “Partly Cloudy”
6		date: “2017-01-21”
7		observationtime: “06:20:00”
8		observationpoint: “San Diego
9		feelslike: “54”
10		humidity: “69”
11		winddisplay: “15 mph West”
12		day: “Saturday”
13		shortday: “Sat”
14		windspeed: “15 mph”
15		imageUrl: “http://blob.weather.microsoft.com/static/weather4/en-us/law/29.gif”
16		degType: “F”
17		}

Next, change the city in cell A2. Google Sheets will re-invoke our weather microservice and show us the results for this new city. We are off to a great start!

Note: If you are using Microsoft Excel instead of Google Sheets, Excel (2013 and higher) ships with the WEBSERVICE function which can be used to invoke URLs and receive JSON data from microservices.

Create CURRENTWEATHER function

Next, let’s create our own custom function in Google Sheets so we can abstract away the URL and make it easier to invoke our weather service. We’ll also start parsing the JSON data returned to retrieve individual fields of data. To accomplish this, click on Tools from the menu and select Script editor.

Google Sheets utilizes JavaScript for developing functions so let’s create a function to retrieve the current weather using JavaScript:

/**
 * Retrieve the current weather for a given city.
 *
 * @param {"San Diego, CA"} location
 *        Location of city where weather information is desired. Can also be a 5 digit zip code.
 * @customfunction
 */
function CURRENTWEATHER(location) {
    location = encodeURI(location);
    var response = UrlFetchApp.fetch("https://thisdavej.azurewebsites.net/api/weather/current?loc=" + location);
    var w = JSON.parse(response.getContentText());
    return w.temperature + '°' + w.degType + ' ' + w.skytext;
}

This function uses the built-in UrlFetchApp.fetch method to fetch the data from our weather service. After fetching the raw JSON text, we parse it, and return some fields from the resulting JSON object.

We declare our function name in upper case which is the standard convention for Google Sheets functions. Finally, you will notice that we include a JSDOC style comment above our function. Sheets uses the JSDOC comment to provide a tooltip on function usage when editing a cell and using the function.

Press Ctrl+S to save your function. Let’s start using it! Modify the spreadsheet as follows:

	A	B
1	City
2	San Diego, CA	=CURRENTWEATHER(A2)

You should see the results in the following format:

	A	B
1	City
2	San Diego, CA	54°F Partly Cloudy

Create JSON Handlebars template function

We could declare victory at this point, but we are capable of more. 🙂 Let’s create a custom function that will enable us to use a Handlebars-style templating syntax with the JSON object returned from our weather service. Once again, click on Tools from the menu and select Script editor. Add the following custom function:

/**
 * Parse JSON and render the results in a string template.
 *
 * @param {"url"} url
 *        JSON API data URL
 * @param {"templateText"} template
 *        string template for rendering results
 * @customfunction
 */
function JSONTEMPLATE(url, template) {
    url = encodeURI(url);
    var response = UrlFetchApp.fetch(url);
    var obj = JSON.parse(response.getContentText());

    var result = template.replace(/\{\{\s*(.*?)\s*\}\}/g, function(match, varName) {
        return obj[varName];
    });

    return result;
}

As before, we use the built-in UrlFetchApp.fetch method to fetch the data from our weather service. We parse the JSON object and use regular expressions to substitute in the various attributes of our JSON object using a template. As a side note, regex101.com is very helpful site for creating and testing regular expressions.

Let’s give this templating function a test run to see this awesomeness in action. Modify your sheet as follows:

	A	B
1	City	{{temperature}} °{{degType}} {{skytext}}
2	San Diego, CA	=JSONTEMPLATE(“https://thisdavej.azurewebsites.net/api/weather/current?loc=” & A2,B1)

Please note our template in cell B1. The JSON attributes of interest are enclosed in double curly braces (Handlebars syntax). Invoke the function and you will see something like this:

	A	B
1	City	{{temperature}} °{{degType}} {{skytext}}
2	San Diego, CA	54°F Partly Cloudy

Weather web API overview – forecast weather conditions

Let’s now create some custom functions to help us with forecast weather conditions. This will not only help us in our personal life to understand the upcoming weather expected, but it will also teach us about handling JSON object arrays since the new forecast Web API we will use returns an array of JSON objects, one for each of the five days in the upcoming weather forecast.

The forecast function accepts two parameters:

• loc: location (example: San Diego, CA)
• deg: degree type (F or C) Default is F.

Example: Return a JSON object with weather forecast information (deg F)

https://thisdavej.azurewebsites.net/api/weather/forecast?loc=San Diego,CA&deg=F

This will return a JSON object with the following structure:

[
  {
    "low": "53",
    "high": "59",
    "skycodeday": "29",
    "skytextday": "Partly Cloudy",
    "date": "2017-01-20",
    "day": "Friday",
    "shortday": "Fri",
    "precip": "0",
    "degType": "F"
  },
  {
    "low": "54",
    "high": "60",
    "skycodeday": "34",
    "skytextday": "Mostly Sunny",
    "date": "2017-01-21",
    "day": "Saturday",
    "shortday": "Sat",
    "precip": "20",
    "degType": "F"
  },
  {
    "low": "55",
    "high": "61",
    "skycodeday": "26",
    "skytextday": "Cloudy",
    "date": "2017-01-22",
    "day": "Sunday",
    "shortday": "Sun",
    "precip": "100",
    "degType": "F"
  },
  {
    "low": "50",
    "high": "57",
    "skycodeday": "11",
    "skytextday": "Rain Showers",
    "date": "2017-01-23",
    "day": "Monday",
    "shortday": "Mon",
    "precip": "100",
    "degType": "F"
  },
  {
    "low": "48",
    "high": "57",
    "skycodeday": "34",
    "skytextday": "Mostly Sunny",
    "date": "2017-01-24",
    "day": "Tuesday",
    "shortday": "Tue",
    "precip": "90",
    "degType": "F"
  }
]

How awesome is that? Let’s use Google Sheets to render information based on this weather forecast API.

Display rows of JSON data using a template

We’ll create a Google Sheets function to display the next five days of the weather forecast and format each line using a template. As before, click on Tools from the menu and select Script editor. Add the following custom function:

function JSONROWSTEMPLATE(url, template) {
    url = encodeURI(url);
    var response = UrlFetchApp.fetch(url);
    var obj = JSON.parse(response.getContentText());

    var results = obj.map(function(row) {
        return template.replace(/\{\{\s*(.*?)\s*\}\}/g, function(match, varName) {
            return row[varName];
        });
    });
    return results;
}

As in previous functions, we use the built-in UrlFetchApp.fetch method to fetch the data from our weather service. We also parse the JSON object and use regular expressions to substitute in the various attributes of our JSON object using a template. Since the JSON data is returned as an array, we use the JavaScript map function to process our template and substitute in values for each forecast day. Google made an excellent design choice by including JavaScript as the language for creating custom Sheets functions! This provides lots of power and flexibility.

Let’s try our new function. Make these changes to your sheet:

	A	B
1	City	{{shortday}} {{low}}-{{high}}°{{degType}} {{skytextday}} ~ Rain: {{precip}}%
2	San Diego, CA	=JSONROWSTEMPLATE(“https://thisdavej.azurewebsites.net/api/weather/forecast?loc=” & A2,B1)

Once again, we create a template in cell B1. The JSON attributes of interest for each forecast day (including rain probability) are enclosed in double curly braces (Handlebars syntax). Let’s give it a try! Make sure you have a few cells free below B2 since our function will occupy a cell for each forecast day. You should see something like this:

	A	B
1	City	{{shortday}} {{low}}-{{high}}°{{degType}} {{skytextday}} ~ Rain: {{precip}}%
2	San Diego, CA	Fri 53-59°F Partly Cloudy ~ Rain: 0%
3		Sat 54-60°F Mostly Sunny ~ Rain: 20%
4		Sun 55-61°F Cloudy ~ Rain: 100%
5		Mon 50-57°F Rain Showers ~ Rain: 100%
6		Tue 48-57°F Mostly Sunny ~ Rain: 90%

Fantastic! We are successfully processing arrays of JSON objects and rendering the results using a Handlebars-like syntax.

Create function to handle JSON object containing either an array of objects or a single object

We can make a very small change to the custom function we created in the previous section to handle JSON objects containing either an array of objects or a single object. Replace the JSONTEMPLATE we created a while back with the following code:

/**
 * Parse JSON and render the results in a string template.
 *
 * @param {"http://path/to/myapi"} url
 *        JSON API data URL
 * @param {"This is {{item1}}."} template
 *        string template for rendering results
 * @customfunction
 */
function JSONTEMPLATE(url, template) {
    url = encodeURI(url);
    var response = UrlFetchApp.fetch(url);
    var obj = JSON.parse(response.getContentText());

    if (!Array.isArray(obj)) {
        obj = [obj];
    }

    return obj.map(function(row) {
        return template.replace(/\{\{\s*(.*?)\s*\}\}/g, function(match, varName) {
            return row[varName];
        });
    });
}

Please note lines 15-17. If the JSON returned does not include an array of objects, we “array-ify the object (i.e. create an array containing the one object) so it can be processed using the map function in the next lines. This one small change gives us a big win since it enables our JSONTEMPLATE to be more versatile and able to handle both arrays of objects and single objects!

Create function to sort JSON objects

Why not sort JSON objects while we are at it? Add the following custom code in the Script Editor:

var sort_by = function(field, reverse, primer) {
    var key = primer ? function(x) { return primer(x[field]) } : function(x) { return x[field] };
    reverse = !reverse ? 1 : -1;
    return function(a, b) {
        return a = key(a), b = key(b), reverse * ((a > b) - (b > a));
    }
}

/**
 * Parse and sort JSON object and render the results in a string template.
 *
 * @param {"url"} url
 *        JSON API data URL
 * @param {"template"} template
 *        string template for rendering results
 * @param {"sortField"} sortField
 *        name of field to use for sorting
 * @param {"decendingOrder"} descendingOrder
 *        set to true for descending order. Default: false
 * @param {"primer"} primer
 *        function to apply to values before sorting. Example: parseFloat
 * @param {"takeNum"} takeNum
 *       the number of sorted results to return. Default: all
 * @customfunction
 */
function JSONTEMPLATE_SORTED(url, template, sortField, descendingOrder, primer, takeNum) {
    url = encodeURI(url);
    var response = UrlFetchApp.fetch(url);
    var obj = JSON.parse(response.getContentText());

    if (!Array.isArray(obj)) {
        obj = [obj];
    }

    var fnPrimer = eval(primer)

    obj.sort(sort_by(sortField, descendingOrder, fnPrimer));

    var sortedObj = obj.map(function(row) {
        return template.replace(/\{\{\s*(.*?)\s*\}\}/g, function(match, varName) {
            return row[varName];
        });
    });

    var result;
    if (takeNum === undefined)
        result = sortedObj;
    else
        result = sortedObj.slice(0, takeNum);
    return result;
}

We’ve added several additional concepts. A sort_by function is utilized to help us sort the array of objects. After retrieving and parsing the JSON data, we sort the objects in the array and render the results using our template.

Let’s see this new function in action. Pay attention to the parameters supplied to the function as this will help solidify your understanding of the custom function code above. Make these changes to your Sheet:

	A	B
1	City	{{shortday}} {{low}}-{{high}}°{{degType}} {{skytextday}} ~ Rain: {{precip}}%
2	San Diego, CA	=JSONTEMPLATE_SORTED(“https://thisdavej.azurewebsites.net/api/weather/forecast?loc=” & A2,B1,”low”,false,”parseFloat”)

We sort the forecast weather data based on low temperatures in ascending order. The parseFloat function is used as a “primer” in our function to sort the values numerically rather than alphabetically. Here are some sample results returned:

	A	B
1	City	{{shortday}} {{low}}-{{high}}°{{degType}} {{skytextday}} ~ Rain: {{precip}}%
2	San Diego, CA	Tue 48-57°F Mostly Sunny ~ Rain: 90%
3		Mon 50-57°F Rain Showers ~ Rain: 100%
4		Fri 53-59°F Partly Cloudy ~ Rain: 0%
5		Sat 54-60°F Mostly Sunny ~ Rain: 20%
6		Sun 55-61°F Cloudy ~ Rain: 100%

Looking good! The JSON rows are sorted based on the low field and returned. Think about the possibilities afforded by this Google Sheets function for any type of JSON object returned containing arrays of objects!

Create function to return min and max values

Finally, we are positioned to create a couple more functions to return the min and max values. Add the following code in the Script Editor:

/**
 * Parse JSON object and return the object containing the max value of a selected field rendered in a string template.
 *
 * @param {"url"} url
 *        JSON API data URL
 * @param {"template"} template
 *        string template for rendering results
 * @param {"sortField"} sortField
 *        name of field to use for sorting
 * @param {"primer"} primer
 *        function to apply to values before sorting. Example: parseFloat
 * @customfunction
 */
function JSONTEMPLATE_MAX(url, template, sortField, primer) {
    return JSONTEMPLATE_SORTED(url, template, sortField, true, primer, 1);
}


/**
 * Parse JSON object and return the object containing the min value of a selected field rendered in a string template.
 *
 * @param {"url"} url
 *        JSON API data URL
 * @param {"template"} template
 *        string template for rendering results
 * @param {"sortField"} sortField
 *        name of field to use for sorting
 * @param {"primer"} primer
 *        function to apply to values before sorting. Example: parseFloat
 * @customfunction
 */
function JSONTEMPLATE_MIN(url, template, sortField, primer) {
    return JSONTEMPLATE_SORTED(url, template, sortField, false, primer, 1);
}

We leverage the previous JSONTEMPLATE_SORTED function we created and supply a takeNum value of 1 as the final parameter to sort ascending (or descending) and return only one value representing the minimum or maximum value respectively.

Let’s retrieve the maximum “high” temperature from the array of forecast weather objects:

	A	B
1	City	{{shortday}} {{low}}-{{high}}°{{degType}} {{skytextday}} ~ Rain: {{precip}}%
2	San Diego, CA	=JSONTEMPLATE_MAX(“https://thisdavej.azurewebsites.net/api/weather/forecast?loc=” & A2,B1,”high”,”parseFloat”)

Here are the results:

	A	B
1	City	{{shortday}} {{low}}-{{high}}°{{degType}} {{skytextday}} ~ Rain: {{precip}}%
2	San Diego, CA	Sun 55-61°F Cloudy ~ Rain: 100%

We have experienced success, and we are able to show the maximum and minimum values of various fields in our JSON data set.

Conclusion

Google Sheets provides amazing versatility for consuming JSON data as we demonstrated with the weather service. The ability to use JavaScript to create custom functions results in a huge win! Join us next time as we use Google Sheets to parse the weather data and send us an email in the morning if rain is expected for the day!

Follow @thisDaveJ (Dave Johnson) on Twitter to stay up to date with the latest tutorials and tech articles.

Additional articles

Consuming Web API JSON Data Using curl and jq
Creating Node.js Microservices with Ease Using Stdlib
Consuming Node.js Microservices Created with Stdlib
Getting Started with YAML in Node.js using js-yaml