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: