Fetching, Filtering, and Sorting JSON APIs in Google Sheets: The Missing Functions

Do you need to fetch, filter, and sort JSON Web APIs in Google Sheets? This tutorial describes how you can add some functions to supplement the built-in Sheets functions and accomplish the goal with ease.

I've written about Consuming JSON Web Data Using Google Sheets previously, but my readers have provided me with some additional use cases I had not considered. I have significantly expanded the Google Sheets custom functions I created previously and have published them on GitHub. Let's get started so you can learn how to fetch, filter, and sort JSON data from external sources in your Google Sheets projects!

Article contents

Installation

To get started:

• Launch Google Sheets and create a new spreadsheet.
• Go to Tools > Script editor
• Copy and paste the code from the getJSON-functions.gs file into the Code.gs file. (Or choose File > New > Script file to create a separate .gs file and paste it there.)
• Return to the Google sheet you created and type =GETJSON in a cell to launch the autocomplete formula prompt and start using the functions.

You are now ready to work through some examples from each of the functions documented below.

GETJSON Function

Purpose

Retrieve JSON from a Web API and render the results using a template

Usage

=GETJSON(url, template, [takeNum], [filterExpression])

Note: square brackets denote optional parameters.

Parameters

• url - The Web API URL containing the JSON object or array of objects to process
• template - The string template for rendering results. Use "{{Field1}}" to retrieve the value associated with a JSON attribute called "Field1". Use || to split the result into multiple spreadsheet columns.
• takeNum - The number of sorted results to return. Use -1 to return all rows.
• filterExpression - The filter expression to apply on the array of JSON objects before returning the results. Objects that evaluate to true are returned. Objects are referenced as "x" in the expression. For example: x.Title === 'VP' (This will return true if the Object in the JSON array being filtered has an attribute named "Title" with a value of "VP".)

To save frustration, the JSON object attributes supplied in template and filterExpression are not case sensitive.

Examples (ISS Position)

The Web API for retrieving the position of the ISS (http://api.open-notify.org/iss-now.json) returns results in the following JSON format:

{
  "timestamp": 1554485189,
  "iss_position": { "longitude": "-129.7349", "latitude": "-36.4637" },
  "message": "success"
}

Example: Return the timestamp of the current ISS location

In this example, we use a template value of "{{timestamp}}" to dynamically substitute the timestamp attribute from the JSON object retrieved.

Spreadsheet input

	A	B
1	URL	http://api.open-notify.org/iss-now.json
2	Template	{{timestamp}}
3
4	Timestamp
5	=GETJSON(B1,B2)

Spreadsheet result

After pressing the Enter key in cell A5, the GETJSON function fetches the JSON URL and retrieves the timestamp attribute of the incoming JSON object. In my opinion, this is pretty easy and powerful also! 😄

	A	B
1	URL	http://api.open-notify.org/iss-now.json
2	Template	{{timestamp}}
3
4	Timestamp
5	1554485189

Side note: The ISS Web API retrieves the timestamp in UNIX time format. As described here, you can convert the UNIX time format to the Google Sheets time format using the following function in cell B5:

	A	B
1	URL	http://api.open-notify.org/iss-now.json
2	Template	{{timestamp}}
3
4	Timestamp	Time from Unix
5	=GETJSON(B1,B2)	=A5/60/60/24 + DATE(1970,1,1)

You can then format cell B5 as a date time from the Google Sheets menu using Format > Number > Date time.

Example: Return the latitude and longitude of the ISS in separate columns of the spreadsheet

In this example, we use a template of "{{iss_position.latitude}} deg || {{iss_position.longitude}} deg" to dynamically substitute the longitude of the ISS followed by the units of "deg" that we added ourselves. The "||" is used as a column separator to split the returned results into separate columns.

We return nested JSON properties (e.g. "{{iss_position.latitude}}") using a dot notation.

Spreadsheet input

	A	B
1	URL	http://api.open-notify.org/iss-now.json
2	Template	{{iss_position.latitude}} deg || {{iss_position.longitude}} deg
3
4	Latitude	Longitude
5	=GETJSON(B1,G2)

Spreadsheet result

After pressing the Enter key in cell A5, the GETJSON function fetches the JSON URL and retrieves the latitude and longitude associated with the incoming JSON object and distributed the results into two columns.

	A	B
1	URL	http://api.open-notify.org/iss-now.json
2	Template	{{timestamp}}
3
4	Latitude	Longitude
5	-36.4637 deg	-129.7349 deg

Examples (Fish Tank IoT Data)

The Web API for retrieving the temperature of fish tanks (https://thisdavej.com/api/tanks.php) returns results in the following JSON format:

[
  {
    "Tank": "Tank1",
    "watertemp": {
      "time": "2019-04-06 16:05 GMT",
      "value": 72.84
    }
  },
  {
    "Tank": "Tank2",
    "watertemp": {
      "time": "2019-04-06 16:05 GMT",
      "value": 72.39
    }
  },
  {
    "Tank": "Tank1",
    "watertemp": {
      "time": "2019-04-06 16:04 GMT",
      "value": 72.7
    }
  },
  {
    "Tank": "Tank2",
    "watertemp": {
      "time": "2019-04-06 16:04 GMT",
      "value": 72.81
    }
  },
  {
    "Tank": "Tank1",
    "watertemp": {
      "time": "2019-04-06 16:03 GMT",
      "value": 72.56
    }
  },
  {
    "Tank": "Tank2",
    "watertemp": {
      "time": "2019-04-06 16:03 GMT",
      "value": 72.22
    }
  }
]

Example: Return nested JSON properties

We are seeking to return the tank name, water temperature timestamp, and water temperature value, separated in three columns in the spreadsheet. We can return nested JSON properties using a dot notation. For example: {{watertemp.value}}

Spreadsheet input

	A	B	C
1	URL	https://thisdavej.com/api/tanks.php
2	Template	{{Tank}} || {{watertemp.time}} || {{watertemp.value}}
3
4	Tank	Time	Temp (°F)
5	=GETJSON(B1,B2)

Spreadsheet result

	A	B	C
1	URL	https://thisdavej.com/api/tanks.php
2	Template	{{Tank}} || {{watertemp.time}} || {{watertemp.value}}
3
4	Tank	Time	Temp (°F)
5	Tank1	2019-04-06 16:05 GMT	72.84
6	Tank2	2019-04-06 16:05 GMT	72.39
7	Tank1	2019-04-06 16:04 GMT	72.7
8	Tank2	2019-04-06 16:04 GMT	72.81
9	Tank1	2019-04-06 16:03 GMT	72.56
10	Tank2	2019-04-06 16:03 GMT	72.22

Beautiful! We have successfully retrieved three columns of data for each tank.

Example: Filter JSON retrieved based on one criterion

In this example we use the filterExpression parameter to return only Tank1 values. Individual objects in the JSON array are referenced as "x" in the filter expression; therefore, we use an expression of x.Tank === 'Tank1' to only return tanks with an attribute of Tank and an associated value of Tank1.

We supply a takeNum (row count) of -1 to return all rows rather than limiting the number of rows returned as a result of the filter expression.

Spreadsheet input

	A	B	C
1	URL	https://thisdavej.com/api/tanks.php
2	Template	{{Tank}} || {{watertemp.time}} || {{watertemp.value}}
3	Row count	-1
4	Filter expression	x.Tank === 'Tank1'
5
6	Tank	Time	Temp (°F)
7	=GETJSON(B1,B2,B3,B4)

Spreadsheet result

Sure enough - only Tank1 data is returned.

	A	B	C
1	URL	https://thisdavej.com/api/tanks.php
2	Template	{{Tank}} || {{watertemp.time}} || {{watertemp.value}}
3	Row count	-1
4	Filter expression	x.Tank === 'Tank1'
5
6	Tank	Time	Temp (°F)
7	Tank1	2019-04-06 16:05 GMT	72.84
8	Tank1	2019-04-06 16:04 GMT	72.7
9	Tank1	2019-04-06 16:03 GMT	72.56

Example: Filter JSON retrieved based on multiple criteria

This time, we use the filterExpression parameter to only return Tank1 values that have a water temperature exceeding 72.6. Here we go!

Spreadsheet input

	A	B	C
1	URL	https://thisdavej.com/api/tanks.php
2	Template	{{Tank}} || {{watertemp.time}} || {{watertemp.value}}
3	Row count	-1
4	Filter expression	x.Tank === 'Tank1' && x.watertemp.value > 72.6
5
6	Tank	Time	Temp (°F)
7	=GETJSON(B1,B2,B3,B4)

Spreadsheet result

Success! Two rows are returned that match the criteria:

	A	B	C
1	URL	https://thisdavej.com/api/tanks.php
2	Template	{{Tank}} || {{watertemp.time}} || {{watertemp.value}}
3	Row count	-1
4	Filter expression	x.Tank === 'Tank1' && x.watertemp.value > 72.6
5
6	Tank	Time	Temp (°F)
7	Tank1	2019-04-06 16:05 GMT	72.84
8	Tank1	2019-04-06 16:04 GMT	72.7

GETJSON_SORTED Function

Purpose

Retrieve JSON from a Web API and sort and render the results using a template

Usage

=GETJSON_SORTED(url, template, sortFields, [takeNum], [filterExpression])

Note: square brackets denote optional parameters.

Parameters

• url - The Web API URL containing the JSON object or array of objects to process
• template - The string template for rendering results. Use "{{Field1}}" to retrieve the value associated with a JSON attribute called "Field1". Use || to split the result into multiple spreadsheet columns.
• sortFields - The name of the JSON field or fields (attributes) to use for sorting. Multiple sort fields can be included, separated by commas inside one string. Use a minus sign in front of the JSON field to sort in descending order. Use "|" followed by a function to specify a function to be called on each field prior to making comparisons. For example, enter "watertemp.time|dateConvert" and create a "dateConvert" function to convert strings to date to sort by date. In our tanks example, the dates will sort correctly alphabetically so this example is somewhat contrived. The "|function" part of the syntax is optional.
• takeNum - The number of sorted results to return. Use -1 to return all rows.
• filterExpression - The filter expression to apply on the array of JSON objects before returning the results. Objects that evaluate to true are returned. Objects are referenced as "x" in the expression. For example: x.Title === 'VP' (This will return true if the Object in the JSON array being filtered has an attribute named "Title" with a value of "VP".)

The JSON object attributes specified in template, sortFields, and filterExpression are not case sensitive to simplify the usage of the function.

Examples

We continue using our Web API for retrieving the temperature of fish tanks (https://thisdavej.com/api/tanks.php) so we can sort the JSON data returned.

Example: Sort using a single sort field

We sort the water temperature in ascending order using a sortFields value of "watertemp.value".

Note: To sort water temperature in descending order (largest values first), we would use a sortFields value of -"-watertemp.value". The "-" in front of the sort field produces a descending sort.

Spreadsheet input

	A	B	C
1	URL	https://thisdavej.com/api/tanks.php
2	Template	{{Tank}} || {{watertemp.time}} || {{watertemp.value}}
3	Sort field	watertemp.value
4
5	Tank	Time	Temp (°F)
6	=GETJSON_SORTED(B1,B2,B3)

Spreadsheet result

The resulting rows are populated with the water temperatures in ascending order using the format provided in our template.

	A	B	C
1	URL	https://thisdavej.com/api/tanks.php
2	Template	{{Tank}} || {{watertemp.time}} || {{watertemp.value}}
3	Sort field	watertemp.value
4
5	Tank	Time	Temp (°F)
6	Tank2	2019-04-06 16:03 GMT	72.22
7	Tank2	2019-04-06 16:05 GMT	72.39
8	Tank1	2019-04-06 16:03 GMT	72.56
9	Tank1	2019-04-06 16:04 GMT	72.7
10	Tank2	2019-04-06 16:04 GMT	72.81
11	Tank1	2019-04-06 16:05 GMT	72.84

Example: Sort using multiple sort fields

Let's now step it up a notch and sort by tank in ascending order and then by water temperature in descending order. The sortFields parameter we will apply will thus be "Tank,-watertemp.value" to sort first on the JSON object attribute called "Tank" followed by a secondary sort in descending order based on the "watertemp.value" attribute.

Spreadsheet input

	A	B	C
1	URL	https://thisdavej.com/api/tanks.php
2	Template	{{Tank}} || {{watertemp.time}} || {{watertemp.value}}
3	Sort field	Tank,-watertemp.value
4
5	Tank	Time	Temp (°F)
6	=GETJSON_SORTED(B1,B2,B3)

Spreadsheet result

The resulting rows are populated and sorted as expected:

	A	B	C
1	URL	https://thisdavej.com/api/tanks.php
2	Template	{{Tank}} || {{watertemp.time}} || {{watertemp.value}}
3	Sort field	Tank,-watertemp.value
4
5	Tank	Time	Temp (°F)
6	Tank1	2019-04-06 16:05 GMT	72.84
7	Tank1	2019-04-06 16:04 GMT	72.7
8	Tank1	2019-04-06 16:03 GMT	72.56
9	Tank2	2019-04-06 16:04 GMT	72.81
10	Tank2	2019-04-06 16:05 GMT	72.39
11	Tank2	2019-04-06 16:03 GMT	72.22

Example: Filter and sort the data in one operation

We can also filter the data first and then sort the data. Let's filter our JSON data set to only show Tank1 data and sort the Tank1 temperatures in descending order. We must supply a value for takeNum since it appears before the filterExpression in the parameter list. A takeNum value of -1 returns all rows (after filtering) which is what we want.

Spreadsheet input

	A	B	C
1	URL	https://thisdavej.com/api/tanks.php
2	Template	{{Tank}} || {{watertemp.time}} || {{watertemp.value}}
3	Sort field	-watertemp.value
4	Row count	-1
5	Filter expression	x.Tank === 'Tank1'
6
7	Tank	Time	Temp (°F)
8	=GETJSON_SORTED(B1,B2,B3,B4,B5)

Spreadsheet result

We receive three rows back for Tank1 data sorted by temperature in descending order as expected.

	A	B	C
1	URL	https://thisdavej.com/api/tanks.php
2	Template	{{Tank}} || {{watertemp.time}} || {{watertemp.value}}
3	Sort field	-watertemp.value
4	Row count	-1
5	Filter expression	x.Tank === 'Tank1'
6
7	Tank	Time	Temp (°F)
8	Tank1	2019-04-06 16:05 GMT	72.84
9	Tank1	2019-04-06 16:04 GMT	72.7
10	Tank1	2019-04-06 16:03 GMT	72.56

Advanced Examples - Sorting with the Primer Function

We can create or use an existing function to call on each field prior to making comparisons for sorting.

Let's start with a new simple data set of phrases:

[
  {
    "phrase": "hello"
  },
  {
    "phrase": "to"
  },
  {
    "phrase": "the"
  },
  {
    "phrase": "world"
  }
]

For starters, we'll apply a standard sort to sort alphabetically by the phrase field:

Spreadsheet input

	A	B	C
1	URL	https://thisdavej.com/api/strings.php
2	Template	{{phrase}}
3	Sort fields	phrase
4
5	Phrase
6	=GETJSON_SORTED(B1,B2,B3)

Spreadsheet result

As expected, we receive the phrase field values back sorted in alphabetic order:

	A	B	C
1	URL	https://thisdavej.com/api/strings.php
2	Template	{{phrase}}
3	Sort fields	phrase
4
5	Phrase
6	hello
7	the
8	to
9	world

Let's now create a primer function called stringLength so we can sort the strings by their length rather than alphabetically. Add the following function at the top of your Code.gs JavaScript file in Google Sheets above the GETJSON functions you added earlier:

function stringLength(s) {
  return s.length;
}

Perfect! Let's modify the sort field and add our stringLength primer function to it using the "|primer" syntax described in the sortFields parameter section of the GETJSON_SORTED usage summary above.

Spreadsheet input

	A	B	C
1	URL	https://thisdavej.com/api/strings.php
2	Template	{{phrase}}
3	Sort fields	phrase|stringLength
4
5	Phrase
6	=GETJSON_SORTED(B1,B2,B3)

Spreadsheet result

We now receive the strings sorted in order of string length.

	A	B	C
1	URL	https://thisdavej.com/api/strings.php
2	Template	{{phrase}}
3	Sort fields	phrase|stringLength
4
5	Phrase
6	to
7	the
8	hello
9	world

As a final sorting exercise, we sort using multiple sort criteria. Let's sort by string length first (our primary sort key), and then sort in reverse alphabetical order as a secondary sort key to break any ties where we are sorting field values of the same length.

Spreadsheet input

	A	B	C
1	URL	https://thisdavej.com/api/strings.php
2	Template	{{phrase}}
3	Sort fields	phrase|stringLength,-phrase
4
5	Phrase
6	=GETJSON_SORTED(B1,B2,B3)

Spreadsheet result

Bam! Since "hello" and "world" both contain 5 characters, our secondary sort key (reverse alphabetic) ensures that "world" appears before "hello". How cool is that? 😎

	A	B	C
1	URL	https://thisdavej.com/api/strings.php
2	Template	{{phrase}}
3	Sort fields	phrase|stringLength,-phrase
4
5	Phrase
6	to
7	the
8	world
9	hello

GETJSON_MAX Function

Purpose

Retrieve JSON from a Web API and return the maximum value for a given JSON attribute

Usage

=GETJSON_MAX(url, field, [filterExpression], [primer])

Note: square brackets denote optional parameters.

Parameters

• url - The Web API URL containing the JSON object or array of objects to process
• field - The name of the JSON field to use as a basis for the maximum value
• filterExpression - The filter expression to apply on the array of JSON objects before determining the maximum value. Objects are referenced as "x" in the expression. For example: x.Tank === 'Tank1' (This will return true if the Object in the JSON array being filtered has an attribute named "Tank" with a value of "Tank1".)
• primer - The function to call on each field prior to making comparisons. For example, create a function called "dateConvert" to convert strings to dates and enter "dateConvert" as the primer. In our tanks example, the dates will sort correctly alphabetically so this example is somewhat contrived. It is not necessary to use the built-in JavaScript "parseFloat" function for sorting floating point numbers since JavaScript will infer the values as floats.

The JSON object attributes specified in field and filterExpression are not case sensitive to simplify the usage of the function.

Examples

Example: Get maximum value of a numeric field

We retrieve the maximum water temperature (field named watertemp.value) in the JSON data set fetched from the web API:

Spreadsheet input

	A	B	C
1	URL	https://thisdavej.com/api/tanks.php
2	Field	watertemp.value
3
4	Maximum temp	=GETJSON_MAX(B1,B2)

Spreadsheet result

The maximum temperature for the entire set is retrieved successfully.

	A	B	C
1	URL	https://thisdavej.com/api/tanks.php
2	Field	watertemp.value
3
4	Maximum temp	72.84

Example: Get maximum value of a numeric field after applying a filter

This time, we apply a filter first, so we only retrieve the maximum water temperature reading from the "Tank2" tank:

Spreadsheet input

	A	B	C
1	URL	https://thisdavej.com/api/tanks.php
2	Field	watertemp.value
3	Filter expression	x.Tank === 'Tank2'
4
5	Maximum temp	=GETJSON_MAX(B1,B2,B3)

Spreadsheet result

We get our result - very powerful!

	A	B	C
1	URL	https://thisdavej.com/api/tanks.php
2	Field	watertemp.value
3	Filter expression	x.Tank === 'Tank2'
4
5	Maximum temp	72.81

GETJSON_MIN Function

Purpose

Retrieve JSON from a Web API and return the minimum value for a given JSON attribute

Usage

=GETJSON_MIN(url, field, [filterExpression], [primer])

Note: square brackets denote optional parameters.

Parameters

• url - The Web API URL containing the JSON object or array of objects to process
• field - The name of the JSON field to use as a basis for the minimum value
• filterExpression - The filter expression to apply on the array of JSON objects before determining the minimum value. Objects are referenced as "x" in the expression. For example: x.Tank === 'Tank1' (This will return true if the Object in the JSON array being filtered has an attribute named "Tank" with a value of "Tank1".)
• primer - The function to call on each field prior to making comparisons. For example, create a function called "dateConvert" to convert strings to dates and enter "dateConvert" as the primer. In our tanks example, the dates will sort correctly alphabetically so this example is somewhat contrived. It is not necessary to use the built-in JavaScript "parseFloat" function for sorting floating point numbers since JavaScript will infer the values as floats.

The JSON object attributes specified in field and filterExpression are not case sensitive to simplify the usage of the function.

Examples

Example: Get minimum value of a numeric field

We retrieve the minimum water temperature (field named watertemp.value) in the JSON data set fetched from the web API:

Spreadsheet input

	A	B	C
1	URL	https://thisdavej.com/api/tanks.php
2	Field	watertemp.value
3
4	Minimum temp	=GETJSON_MIN(B1,B2)

Spreadsheet result

The minimum temperature for the entire set is retrieved successfully.

	A	B	C
1	URL	https://thisdavej.com/api/tanks.php
2	Field	watertemp.value
3
4	Minimum temp	72.22

Conclusion

You are now equipped to fetch, filter, and sort JSON Web APIs from external sources in Google Sheets. Go out and build some awesome projects! I'd love to hear how you are using the "GETJSON" functions in the comments if you find them useful!

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

Additional articles

Learn Handlebars with Node.js and Help Freddy's Fish Too
How to Count Unique Items in JavaScript Arrays
Add Emoji to your Node Projects with node-emoji ✨
How to Host a Raspberry Pi Web Server on the Internet with ngrok

Last updated Apr 23 2019