Use IMPORTJSON(input) To display the full JSON



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


IMPORTJSON("", "/name")

Will only return the elements under the name node



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

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 '' -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

  • Add the ID to the IMPORTJSON function



JSON in text

Any JSON can be parsed directly from Google Sheets.


=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.


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.


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


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


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
  • Relative path: The function will ignore whether the filter is a direct descendant of the root or not

or use the alias


Widcard: Use an * to replace a chain of characters


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

Hiding headers

By default, in order to help finding the right filters, the function will display rows headers and columns headers. Hide them by writing one of the following option as third parameter:

  • hideHeaders: hides rows headers and columns headers
  • hideHeaderRow: hides the header row
  • hideHeaderColumn: hides the header column


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


=IMPORTJSON(B1, , "hideHeaders")

More options

ImportJSON accepts various options aimed at adding more flexibility.

Options can be added in two ways:

  • A text of keys and values


=IMPORTJSON(B1, "/topLevelDomain/0", "hideHeaders, cacheLifespan:48, disablePivot")

Bear in mind that when the value is omitted, the function will interpret it as TRUE. Hence, the example above is equal to 

"hideHeaders:true, cacheLifespan:48, disablePivot:true"

(Spaces after the comma are not compulsory)

  • A reference to a two-column range

The left column defines option names and the second column contains corresponding values.

This image has an empty alt attribute; its file name is image-5-1024x319.png

Some options might be limited or unavailable according to your subscription. Please check out our plans for more details

cacheLifespan (hours)

Google Sheets controls how the functions recalculate according to different behaviours (modifying cells, opening the spreadsheet, …)
To avoid that IMPORTJSON fetches the target data constantly, the content is cached 24 hours by default. In this time span, whenever IMPORTJSON 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 occurs.

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

disablePivot (TRUE | FALSE)

Use it to disable automatic pivot and display all the data in a single column

Set up automatic updates

ImportFromWeb functions can be executed automatically even though the spreadsheet is closed. Setting up an automatic trigger can be done through the sidebar.

Only available in Premium and High Volume plans.