IMPORTJSONAPI

Provides a custom function to selectively extract data from a JSON or GraphQL API in a tabular format suitable for import into a Google Sheets spreadsheet.

Changelog

• v1.0.3 - Added support for converting values to numbers (23 November 2020)
• v1.0.2 - Return null instead of empty string for blank columns (3 March 2020)
• v1.0.1 - Fix returning empty results (2 March 2020)
• v1.0.0 - Initial release (23 February 2020)

Installation

NOTE: THIS SCRIPT REQUIRES THE NEW V8 RUNTIME - SEE STEP 3

To add this custom function to your spreadsheet, follow this procedure:

1. Open the spreadsheet in your browser.
2. Select the Tools > Script editor menu option. This will open a script editor window. You will need to copy and paste the function code into a blank script file.
3. IMPORTANT - This script contains functionality that requires the V8 runtime in order to work. To enable this select the Run > Enable new Apps Script runtime powered by Chrome V8 option.
4. Copy the entire contents of the IMPORTJSONAPI.gs file. The raw file can found here.
5. Paste this into the blank Code.gs script file or another blank script file that you have created.
6. Select the File > Save menu option to save the script.
7. You should now be able to use the =IMPORTJSONAPI() function in your sheet.

Usage

=IMPORTJSONAPI(URL, JSONPath Query, Columns [,Parameter] [,Parameter])

Examples

The following examples are based on this JSON data:

{
  "stores" : {
    "Borders" : [
      {
        "Title" : "Yellow Rivers",
        "Author" : "I.P. Daily",
        "Price" : 3.99
      },
      {
        "Title" : "Full Moon",
        "Author" : "Seymour Buns",
        "Price" : 6.49
      }
    ],
    "Waterstones" : [
      {
        "Title" : "Hot Dog",
        "Author" : "Frank Furter",
        "Price" : 8.50 
      }
    ]
  }
}

Get titles of all books

=IMPORTJSONAPI("https://test.com/api", "$..Title", "@")

Get all books and authors

=IMPORTJSONAPI("https://test.com/api", "$.stores.*[*]", "Title, Author")

Select all books in all stores

=IMPORTJSONAPI("https://test.com/api", "$.stores.*[*]", "^.~, Title")

The titles of all books with a price greater than 5

= IMPORTJSONAPI("https://test.com/api", "$..[?(@.Price>5)]", "Title")

Function Arguments

URL

The URL of the API endpoint. Any query parameters containing characters such as '&' or '=' should be urlencoded. For example:

=IMPORTJSONAPI("https://api.test.com/store?api_token=ds45%3F6hjkd%3Ddjs, ...)

JSONPath Query

The JSONPath expression to select the data that you wish to extract. Each JSON object matched by the expression will become a row in your spreadsheet. An introduction to JSONPath expressions can be found at http://goessner.net/articles/JsonPath/.

The actual JSONPath query implementation used is JSONPath Plus which contains some additional functionality and examples.

Columns

The Columns parameter is a comma separated list of path expressions. Path expressions contain one or more of the following components optionally separated by a period.

If the value returned from the path expression is an array of scalars then the result is a list of the array items delimited by a comma.

If the value returned from the path expression is an object or an array which does not contain only scalars the result is the first 50 characters of the objects string representation.

Column path expression examples

All examples are based on the following JSON Object:

{
  "book" : {
    "title": "It",
    "author": "S. King",
    "orders" : [28, 72]
  }
}

The Value column is the result of the JSONPath expression and the Result column is the result after the column path expressions have been applied to the value.

Converting the column data type

You can convert a column that returns a string to a numeric type by appending '>n' to the column path:

Title, Price>n

If you need support for other type comversions then please create a new issue.

Parameters

After the three mandatory function arguments you can specify a variable number of function parameters. Each parameter is of the format:

"parametername = value"

If the value contains an equals (=) character then it needs to be replaced with '%3D'. If the value for a JSON parameter (headers or payload) contains a double quote (") then it needs to be replaced with '\%22'. The value does not need to be quoted even if it is a string.

Parameter Examples

A basic post request with no payload:

=IMPORTJSONAPI("https://test.com/api", "$..Title", "@", "method=post")

A post request with a payload:

=IMPORTJSONAPI("https://test.com/api", "$..Title", "@", "method=post", "payload={ 'user' : 'andy', 'pass' : 'pandy' }")

A request with Basic Authorizaton:

=IMPORTJSONAPI("https://test.com/api", "$..Title", "@", "headers={ 'Authorization' : 'Basic QWxhZGRpbjpPcGVuU2VzYW1l' }")

GraphQL

To query a GraphQL API endpoint you need to set the method, contentType and payload parameters.

Example

 = IMPORTJSONAPI("https://api.graph.cool/simple/v1/swapi", "$..films[*]", "^^name, director", "method=post", "contentType=application/json", "payload={ 'query': '{ allPersons { name films { director } } }' }")

Refreshing Data

By default Google Sheets only refreshes the results of a custom function every hour or so. If you want to force a refresh then this can be achieved by changing any of the function arguments. The easiest way of doing this is to add a 'dummy parameter' to the end of the function arguments. The dummy parameter should either be a number or a boolean and will be ignored by the import function.

Example

=IMPORTJSONAPI("https://test.com/api", "$..Title", "@", 1)

You can now force a refresh by incrementing the number at the end of the function.

Debugging

When you are trying to create the JSONPath query to filter your data, it is sometimes difficult to tell if you are getting the correct results. To help with this you should set the columns parameter to a single '@'. This will then output the list of objects that is being returned by the query. Once you are happy with the results you can then modify the columns to extract the relevant fields.

Example

=IMPORTJSONAPI("https://test.com/api", "$..book[?(@parent.bicycle && @parent.bicycle.color === "red")].category", "@")