CODEWITHSUNDEEP
jsongoogle-apps-scriptimportgoogle-sheetscustom-function
Jim Green
Jim Green

Reputation: 3

Tabulate JSON into Sheets

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.

Upvotes: 0

Views: 549

Answers (2)

Robin Gertenbach
Robin Gertenbach

Reputation: 10776

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.

Upvotes: 0

Max Makhrov
Max Makhrov

Reputation: 18707

Example

Simple formula

I think, faster way to get IDs:

=QUERY(QUERY(A2:B,"select B where A <> '' offset 4"),"skipping 7")

and if you want Names:

=QUERY(QUERY(A2:B,"select B where A <> '' offset 1"),"skipping 7")

  • when you change offset from 0 to 6, you get different columns outputs.

  • 7 is the number of columns in Data.

The result is autocompleted column with Data.

Hard formula

Also possible to get the whole result with one formula:

  1. paste =COUNTA(A:A) in cell E2
  2. paste 7 in E3, this is the number of columns in Data
  3. =E2/E3 in E4

And then in cell G2 or somewhere on right paste the formula:

=ArrayFormula(vlookup(if(COLUMN(OFFSET(A1,,,1,E3)),    
(row(OFFSET(A1,,,E4))-1)*E3+COLUMN(OFFSET(A1,,,1,E3))),    
{row(OFFSET(A1,,,E2)),OFFSET(B2,,,E2)},2,0))

It works slow, but gives the whole table.

or Script?

I've also tried to use script UDF function. Here's test formula:

=ConvertTo2D(TRANSPOSE(R3:R16),7)

where R3:R16 is small range which was splited into table with 7 columns. The script is pretty short:

function ConvertTo2D(Arr, index) {

   var newArr = [];
   while(Arr[0].length) newArr.push(Arr[0].splice(0,index));

   return newArr; 

}

Sounds good. But! It is ve-e-e-e-ery slow. So This solution is good only for quick test.

Upvotes: 1

Related Questions