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

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