Tabulate JSON into Sheets

Question

I've been trying to get a readable database of a JSON file from a URL.

I've used fastfedora's script on Github, https://github.com/fastfedora/google-docs/blob/master/scripts/ImportJSON/Code.gs, to import JSON from the URL to Sheets. I'm using the basic:

=TRANSPOSE(ImportJSON("https://rsbuddy.com/exchange/summary.json"))

I used transpose as it was easier to work with two long columns rather than two long rows.

The data that's been imported however, is very messy: https://docs.google.com/spreadsheets/d/1mKnRQmshbi1YFG9HHg7-mKlZZzpgDME6-eGjDJKzbRY/edit?usp=sharing. It's basically 1 long column of descriptive data, (name, id, price etc.) and another column of the variable (the actual name of the item and it's price in digits).

Is it possible to manipulate the resultant Sheets page so that the common factors in the first column can be lined up with the pseudo-table beside two initial columns? E.g. for the first item, the ID will be '2', the name will be 'Cannonball', the Sp will be '5' etc.

Thanks in advance. Do forgive me for my ignorance.

Answer 1

If the data is structured and every object will always have the same structure you can use a simple offset to do this:

=OFFSET($B$2,
        (ROW($B2) - 2) * 7 +
         COLUMN(D$1) - 4, 
        0)

Put that in D2 and drag to the right and down. It is possible to immediately return the data in this fashion but for that you need to meddle with the script.

Tabulate JSON into Sheets

Sign up