Select your source

JSON from a URL

Copy and paste a URL pointing to a JSON in your spreadsheet, and call =IMPORTJSON(ref_to_your_id)

Example with the fantastic Rest Countries API
https://restcountries.eu/rest/v2

JSON from a Drive document

  • Upload a document your file in a Drive folder.

The file extension does not matter. However, the file should only contain a valid JSON object.

  • Right-click on the file, select “Share” and make sure anyone can view the file.
  • Copy the link and extract only the id part of the link:

Example:
In https://drive.google.com/file/d/1PtwQTmBhoFOU0wy70XNdL7yH4J-s3dQk/view?usp=sharing, extract 1PtwQTmBhoFOU0wy70XNdL7yH4J-s3dQk

  • Paste the id in your spreadsheet, and call =IMPORTJSON(ref_to_your_id)

JSON from a cUrl request

Although more and more APIs only need a URL to retrieve data, others need more information that is contained in a more complex request

The cUrl format is ideal because it contains all that necessary information.
We don’t have to know anything about cUrl to use ImportJSON because, in general, APIs providers provide the cUrl request in their documentation.

To use ImportJSON with a cUrl request, just copy and paste it into your cell and call the function on it

Filtering

A JSON is a tree of information, which means it has multiple dimensions.
Hence, it makes its manipulation cumbersome without technical knowledge.
Most of the tools that help navigate into a JSON allow to expand and contract the branch of the tree. Even though it makes it easy to view the information, it does not help us to manipulate it.
Spreadsheets, however, are the most flexible tool to manipulate data, but spreadsheets are 2 dimensional by definition: Rows and columns!

In order to fit into a spreadsheet, ImportJSON flattens the entire tree by moving each piece of information to a same dimension

For instance, the “price” in preOpenMarket > preopen > 0 will be called preOpenMarket/preopen/0/price

ImportJSON allows to filter the tree to retrieve only information of certain branches by using a filter

Example

=IMPORTJSON(url, “preOpenMarket/preopen/0”)

It is also possible to add more filters by separating each filter with a comma

Pivoting

In our previous example, the preopen branch contains an array of elements that have the same properties: price, buyQty, sellQty

It makes sense to put elements of this array in a second dimension. Luckily, ImportJSON is smart enough to do it automatically!
Hence, anytime the function finds that all the elements returned are part of an array, it will pivot the data into columns.

In order to create a table from an array, we just have to filter our JSON to the root of our array by using preOpenMarket/preopen

And here is the result:

Options

If you are used to ImportFromWeb, you might be using the third parameter for options. The same logic applies to ImportJSON.

The options parameter is optional and should point to a two-columns range. The left column defines option names and the second column contains corresponding values.

Example:

Note that you could also write =IMPORTJSON(A1,,A3:B3) if you don’t want to filter the JSON

Available filters are

hideHeadersHide both rows headers and Columns headers. default to false
hideColumnsHeadersHide the columns headers (top header). default to false
hideRowsHeadersHide the rows headers (left header). default to false