In Taiwan, shops / restaurants are open as normal and people are walking around freely, other than the tiny percent in quarantine. Meanwhile Europe and the US are increasingly locked down. So which one has more freedom?
I made a Google spreadsheet add-on [1] that contains 3 built-in options for converting nested JSON to tabular format.
Method 1: drills into nested json objects and arrays and returns the key names as headers, key values as rows. In cases with multiple nested values, they get returned into multiple columns, differentiated by a number, e.g. orders > products > 1, orders > products > 2.
Method 2: same as above but returns all nested data into a single column, e.g. a single column named orders > products. This can break the association between JSON elements, but is more convenient for certain types of analysis.
Method 3: concatenates all the elements of each nested object into a single cell, separated with pipes.
In some cases (e.g. when the primary object of interest is nested inside another object) there are still problems recognizing what keys should represent rows vs columns, but in my tests the above 3 algorithms cover most of the use cases for spreadsheets.
You could put the nested data into a separate sheet with a unique identifier for the row. Then include a foreign key back to the original sheet's primary id.
As I described on another reply, SpreadOn not only supports a tree structured format, but also has tags for referring to entire sheets by name, for declaring grids of identically-typed data, for referring to named regions and extracting subsets of them, and compactly defining tables of repeating structures, which you can mix on a case-by as they make sense. (You can't embed a grid in another grid since it requires more than one cell, but you can make a grid of sheet references, since the sheet name only takes one cell and can be put into a grid.)
There is no "one best way" to represent JSON data in spreadsheets, because JSON data comes in all sizes and shapes, so SpreadON tries to support many different useful formats that you can link together, with a simple straightforward syntax that can be easily extended without breaking existing documents.
I also posted one recently, a plugin to connect to APIs directly in Google Sheets [1]. The primary use case I had in mind was for people looking to grab data from various APIs, in which case it's pretty clear why someone would want to directly access the output in Google Sheets. However, I was surprised to discover a lot of people are actually using it as an alternative to CMSes like Wordpress, like bolting it together to other plugins and sheets to create their own apps. It's a trend for sure, I think it all falls under the "low code/no code" movement.
Hi HN, a couple days ago I released a Google Sheets Add-on to pull JSON API data directly into Google Sheets. You can enter the URL, query strings, and any required headers into the Add-on sidebar, and can make dynamic requests by referencing cells in the sheet. For example, the API request URL can point to a cell containing today's date, which is useful if you want to refresh the data every day.
I made this after working on a lot of reporting projects that required aggregating API data from multiple marketing & analytics platforms.
Thanks, filipm. It's interesting, the intro page does actually list all the things it audits in the box that says Site Settings, Goals and Funnels, Site Search, Campaign Tracking, etc. Did you not see that (perhaps because it's below the fold) or was it just not clear enough?
