There are a generic methods (including some ready to use libraries) to 'flatten' JSON to key:value pairs, which you could then convert to row/column form. But I don't really think that helps you as you'd just end up with umpteen columns named by the composite keys which you'd still need to interpret to make sense of the info. When I extract data for reporting/analysis, I just write Python to pull out the bits I want from the nested structures, iterating through if necessary, and then output them as one row at a time in a standard column format of my choosing, that then feeds into downstream analysis routines. Due to differences in the JSON structures returned depending on context (and sometimes null data), the code needs to be specific to the API call (and in some cases, the way the API call is being used and its target.) From a quick look at the google sheet source, this is what it is really doing. It has specific output code for each API call it supports, i.e. it is not figuring it out dynamically, it is hard-coded. See the code section titled "Reports" https://gist.github.com/dexterlabora/9884aa4399715df8f453ea9de63255a0 that's the code from around line 688 onwards (at least with current numbering.)
... View more