Parse json with dashdash!

Why?

You want to parse a JSON in a spreadsheet and neatly organize its data into rows and columns? You can do this with dashdash by using PARSE(). Both formulas do exactly the same.

How?

Syntax: =PARSE(json, [query]).

So, PARSE(json, query) will get you the data inside json specified by query. If you want an top level element, just provide PARSE(json, element). if you want a sub element do PARSE(json, element.sub-element). If you want an item from a list (within []) do PARSE(json, element[])". Etc.

Example: =PARSE(A1, "Employees").

Returns value: The selected object of the JSON.

Details

If you want to parse objects on a lower level of the JSON, you can simply do so by using e.g. =PARSE(A1, "xxx.Employees").

If there is a table, i.e. an array, inside the JSON, it is delimited by[square brackets]. You can parse the rows of the table by referring to [0], [1], etc. In this case, use e.g. =PARSE(A1, "xxx[0].Employees"), =PARSE(A1, "xxx[1].Employees"), =PARSE(A1, "xxx[2].Employees"), etc. to parse values in the rows.

There is a ton more that you can do with the JSON query language. Learn more here, if you want.

You can also dynamically reference your requests to cell values. Let’s say you have this JSON in cell A1:

[{"symbol":"AAPL","marketPercent":0.01394,"bidSize":0,"bidPrice":0,"askSize":0,"askPrice":0,"volume":325020,"lastSalePrice":158.22,"lastSaleSize":2,"lastSaleTime":1505419198525,"lastUpdated":1505422800000,"sector":"technologyhardwareequipmen","securityType":"commonstock"},{"symbol":"AMZN","marketPercent":0.02861,"bidSize":0,"bidPrice":0,"askSize":0,"askPrice":0,"volume":108688,"lastSalePrice":992.2,"lastSaleSize":5,"lastSaleTime":1505419199415,"lastUpdated":1505419200002,"sector":"retailing","securityType":"commonstock"}]

If you now fill the cells:

A2: 0
A3: 1
B1: "symbol"
C1: "lastSalePrice"

Then you can dynamically refer to these cells in the PARSE function e.g. like this:
=parse($A$1,"["&$A2&"]."&B$1) will return AAPL, and you can quickly create this table:

image

1 Like