Syntax

Use IMPORTJSON(input) To display the full JSON

Example:

IMPORTJSON("https://restcountries.eu/rest/v2")

Use IMPORTJSON(input, filter) To display the JSON taking filter as a base node

Example:

IMPORTJSON("https://restcountries.eu/rest/v2", "/name")

Will only return the elements under the name node

Parameters

Inputs

One, or a list of:

  • Plain URLs
  • cURL Requests
  • Google Drive file ID
  • JSON in text

Plain URLs

It can be any URL that returns a JSON object, like

https://restcountries.eu/rest/v2

cURL requests

cURL is a command-line tool for getting or sending data including files using URL syntax

ImportJSON accepts POST and GET requests sent with cURL. Therefore you can use cURL to load any API.

Simple example:

=IMPORTJSON("curl 'https://api.thecatapi.com/v1/images/search' -H 'x-api-key: xxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxx'")

On a side note, although we take the best care to keep your data safe by communicating with our servers using a SSL certificate, beware that we don’t recommend using ImportJSON for highly sensitive data. In case you need to handle sensitive data, please contact us.

Google Drive file ID

ImportJSON allows to load JSON files stored in your Google Drive.

  • Upload a text file containing a JSON
  • Change the file permissions so that anyone who has the link can view the file
  • From the shareable link, only copy the id

Example:

https://drive.google.com/file/d/1KUPmjU4DQdZ8C2VhSHmyxnxyPe74IZhB/view?usp=sharing
  • Add the ID to the IMPORTJSON function

Example

=IMPORTJSON("1KUPmjU4DQdZ8C2VhSHmyxnxyPe74IZhB")

JSON in text

Any JSON can be parsed directly from Google Sheets.

Example

=IMPORTJSON("{'prefix':'roller','dict':'0','res':{'sug':['roller','roller quad','rollerblade','roller fender','rollers','rollerball','rollergames nes','roller enfant','roller fille','roller skating'],'categories':[[115180,'Articles de skate, de roller et de trottinette'],[7278,'Stylos haut de gamme']]},'savedser':{}}")

Note: Any text in a function has to be surrounded by double quotes. Therefore the JSON’s double quotes can be replaced either by single quotes, like the example, above or doubled double quotes:

You don’t need to modify the quotes if you place the JSON in another cell that you reference in the ImportJSON function

Note2: Google Sheets limits the size of a text in cell to 50,000 characters. To avoid this limitation, you can split your JSON into a range of cells like so:

Add multiple inputs

IMPORTJSON accepts a range of inputs. We are still improving it. Let us know how you would like to use it.

Filters

In case no filter is filled, the function will return all the nodes of the resulting JSON object.
Filters are a powerful tool to navigate into the JSON and display only certain nodes.

To find the right filter, start by returning the full JSON, then find the filter from the 1st column.

Example:

Hence, to retrieve the domain extension of all the countries, you can use:

=IMPORTJSON(B1, "/topLevelDomain/0")

will return

In this example, IMPORTJSON has detected an array and has automatically pivoted the countries in columns. The first row shows the index for each country. You can combine it with the filter to only return the domain extension for a specific country

=IMPORTJSON(B1, "/0/topLevelDomain/0")

will return

Add multiple filters

IMPORTJSON accepts a range of filters

Example:

Filters can be joined in one cell by separating them with a comma

Example:

Use filters to pivot the JSON

Filters can be useful to have elements in a JSON displayed into columns. Indeed, IMPORTJSON will pivot into a table only if all the elements returned are part of the same array. To trigger that, you should filter with the base of the array.

More rules

  • Absolute path: Only the elements directly descending from the nodes will be listed
/node1/node2
  • Relative path: The function will ignore whether the filter is a direct descendant of the root or not
//node2

or use the alias

node2

Widcard: Use an * to replace a chain of characters

node*

will display all the nodes which have names starting with “node”

Hide headers

By default, in order to help finding the right filters, the function will display rows headers and columns headers. Hide them by referencing a range of options

Available options:

  • hideHeaders: Set to TRUE, hides rows headers and columns headers
  • hideRowsHeaders: Set to TRUE, hides rows headers
  • hideColumnsHeaders: Set to TRUE, hides columns headers

More options

cacheLifespan (hours)

Google Sheets controls how the functions recalculate according to different behaviours (modifying cells, opening the spreadsheet, …)
To avoid that IMPORTFROMWEB fetches the target data constantly, the content is cached 24 hours by default. Therefore, whenever IMPORTFROMWEB recalculates, the content is retrieved from the cache.

You can customize the cache lifespan by setting the cacheLifespan option to the number of hours you will want the cache to last.
Bear in mind that the function will not necessarily update directly after the cache expires but only when Google Sheets lets the function recalculate.

shake (TRUE | FALSE)

Functions in Google Sheets recalculate only on some conditions. One condition is that it detects a change in the function definition. That is what shake is for!

The shake option is just a way to tell Google Sheets that the function definition has changed and that it has to recalculate. It doesn’t matter if the value is TRUE or FALSE. What matters instead is that it goes from one state to another.

Note that shake does not necessarily fetch the latest data from the target page. It tries beforehand to retrieve the cached data. If the cache is too old (more than 24 hours), then it fetches the target page.

Use it to:

  • “shakes” the function in case the function returned an error (starting with #)
  • retrieve pending requests (as indicated by the #PENDING_REQUEST message)

A convenient use is to couple it with a tick box (Menu > Insert > Tick box)

outputErrors (TRUE | FALSE)

Set to TRUE, it will output #ERROR! when an error occurrs.

When you use ImportFromWeb along with other functions, you might need the function to output errors as recognized by Google Sheets. Unlike the usual errors returned by IMPORTFROMWEB, it returns positive for a formula like =ISERROR( IMPORTFROMWEB( a_url, a_xpath) )

The downside of it, is that #ERROR! is not really explicit. The type of error can still be shown by passing the mouse over the cell.

Default is FALSE