Copy and paste a URL pointing to a JSON in your spreadsheet, and call =IMPORTJSON(ref_to_your_id)
- 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:
- Paste the id in your spreadsheet, and call =IMPORTJSON(ref_to_your_id)
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
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
ImportJSON allows to filter the tree to retrieve only information of certain branches by using a filter
It is also possible to add more filters by separating each filter with a comma
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
And here is the result:
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.
Note that you could also write
=IMPORTJSON(A1,,A3:B3) if you don’t want to filter the JSON
Available filters are
|hideHeaders||Hide both rows headers and Columns headers. default to false|
|hideColumnsHeaders||Hide the columns headers (top header). default to false|
|hideRowsHeaders||Hide the rows headers (left header). default to false|