Import JSON data into google sheets as a table

2
votes

Is there a way to use importJSON() to import this data located here

http://stats.nba.com/js/data/sportvu/2015/drivesData.json

into Google sheets as a table with formatted rows and columns with headers?

Currently when I use the following, the whole data posts into one cell;

=importJSON("http://stats.nba.com/js/data/sportvu/2015/drivesData.json","/resultSets/headers,/resultSets/rowSet","noTruncate,noInherit")
1
jsongoogle-sheets

1 Answers

3
votes

I almost sure that it could be done by script. But here's solution with formulas.

One big formula

Paste your import formula into cell A1: =importJSON(...). And then paste this formula in Cell A4:

={SPLIT(A2,",",0);ARRAYFORMULA(HLOOKUP(ArrayFormula(COLUMN(OFFSET(A1,,,counta(SPLIT(substitute(B2,",,",",-,"),",",0))/COUNTA(SPLIT(A2,",",0)),COUNTA(SPLIT(A2,",",0))))+(row(OFFSET(A1,,,counta(SPLIT(substitute(B2,",,",",-,"),",",0))/COUNTA(SPLIT(A2,",",0))))-1)*counta(SPLIT($A$2,",",0))),{ArrayFormula(COLUMN(OFFSET(A1,,,1,counta(split(substitute(B2,",,",",-,"),",",0)))));SPLIT(substitute(B2,",,",",-,"),",",0)},2,0))}

It's very big and heavy. There's a way to simplify it.

Step by step formulas

Here's list of formulas to complete same task.

  • A1 -- your formula
  • B3 -- =substitute(B2,",,",",-,")
  • B4 -- =counta(SPLIT(B3,",",0))
  • B5 -- =COUNTA(SPLIT(A2,",",0))
  • A7 -- final formula

it will produce the table:

={SPLIT(A2,",",0) ;ARRAYFORMULA(HLOOKUP(ArrayFormula(COLUMN(OFFSET(A1,,,B4/B5,COUNTA(SPLIT(A2,",",0))))+(row(OFFSET(A1,,,B4/B5))-1)*counta(SPLIT($A$2,",",0))),{ArrayFormula(COLUMN(OFFSET(A1,,,1,B4)));SPLIT(B3,",",0)},2,0))}

Ok, it works, not too fast, but it does. Look at sample workbook