You can use ImportJSON to simply parse a JSON contained in a cell or a range of cells
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.
Therefore 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:
Note: You can use the native TRANSPOSE() function to transpose the array at your convenience.
▶︎ Find out more in the full documentation