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.
- v1.0.5 - Added support for importing data from within a speadsheet (10 July 2021)
- v1.0.4 - Added support for converting values to dates (30 March 2021)
- 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)
To add this custom function to your spreadsheet, follow this procedure:
- Open the spreadsheet in your browser.
- Select the
Tools > Script editormenu option. This will open a script editor window. You will need to copy and paste the function code into a blank script file. - Copy the entire contents of the IMPORTJSONAPI.gs file. The raw file can found here.
- Paste this into the blank Code.gs script file or another blank script file that you have created.
- Now save the script by clicking the
Save projectbutton. - You should now be able to use the
=IMPORTJSONAPI()function in your sheet.
=IMPORTJSONAPI(URL, JSONPath Query, Columns [,Parameter] [,Parameter])
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", "@")
| Title |
|---|
| Yellow Rivers |
| Full Moon |
| Hot Dog |
Get all books and authors
=IMPORTJSONAPI("https://test.com/api", "$.stores.*[*]", "Title, Author")
| Title | Author |
|---|---|
| Yellow Rivers | I.P. Daily |
| Full Moon | Seymour Buns |
| Hot Dog | Frank Furter |
Select all books in all stores
=IMPORTJSONAPI("https://test.com/api", "$.stores.*[*]", "^.~, Title")
| Store Name | Title |
|---|---|
| Borders | Yellow Rivers |
| Borders | Full Moon |
| Waterstones | Hot Dog |
The titles of all books with a price greater than 5
= IMPORTJSONAPI("https://test.com/api", "$..[?(@.Price>5)]", "Title")
| Title |
|---|
| Full Moon |
| Hot Dog |
| Parameter | Description |
|---|---|
| URL | The URL endpoint of the API. |
| JSONPath Query | JSONPath query expression. |
| Columns | Comma separated list of column path expressions. |
| Parameter | Optional list of parameters. |
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, ...)
You can also import JSON data that is contained within a cell of your spreadsheet by replacing the URL with a reference to the cell:
=IMPORTJSONAPI(A3, ...)
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.
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.
| Component | Description |
|---|---|
| keyname | Specifies the key to a value. Must be quoted if it contains characters other than letters, numbers or the underscore character. if the name contains a comma ',' then it must always be escaped by using %2C instead. |
| [index] | Access an array with the specified index. |
| @ | The current value. |
| ^ | The parent of the current value. |
| ~ | The key name of the current value. This must always appear last in the path. |
| $ | The root of the JSON object. This must always appear first in the path. |
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.
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.
| JSONPath | Value | Columns | Result |
|---|---|---|---|
| $.book | { "title": "It", "author": "S. King", "orders" : [28, 72] } | title, author | "It", "S.King" |
| $.book.title | "It" | @ | "It" |
| $.book.orders | [28, 72] | @, [1] | "28, 72", "72" |
| $.book.orders | [28, 72] | ^.author | "S.King" |
| $.book | { "title": "It", "author": "S. King", "orders" : [28, 72] } | ~ | "book" |
| $.book.orders | [28, 72] | ^~, [0] | "book", "28" |
| $.book.title | "It" | $.book.author | "S. King" |
You can convert a column that returns a string to a numeric type by appending '>n' to the column path:
Title, Price>n
You can convert a column that returns a string to a date type by appending '>d' to the column path:
Title, PubDate>d
If you need support for other type conversions then please create a new issue.
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 name | Type | Description |
|---|---|---|
| method | String | The HTTP method for the request: get, delete, patch, post, or put. The default is get. |
| headers | Object | A JSON key/value map of HTTP headers for the request. |
| contentType | String | The content type for post requests. The default is 'application/x-www-form-urlencoded' |
| payload | Object | The payload for post requests. |
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' }")
To query a GraphQL API endpoint you need to set the method, contentType and payload parameters.
| Parameter | Value |
|---|---|
| method | post |
| contentType | application/json |
| payload | { 'query': 'YOUR_GRAPHQL_QUERY' } |
Example
= IMPORTJSONAPI("https://api.graph.cool/simple/v1/swapi", "$..films[*]", "^^name, director", "method=post", "contentType=application/json", "payload={ 'query': '{ allPersons { name films { director } } }' }")
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.
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", "@")