jsongoogle-sheetsgoogle-sheets-formula

How do I import JSON from a URL in Google Sheets?

I am trying to import JSON data from a public API into Google Sheets. (For the sake of an example, I'll be using data from Json Placeholder)

{"id":1,"name":"Leanne Graham","username":"Bret","email":"Sincere@april.biz","address":{"street":"Kulas Light","suite":"Apt. 556","city":"Gwenborough","zipcode":"92998-3874","geo":{"lat":"-37.3159","lng":"81.1496"}},"phone":"1-770-736-8031 x56442","website":"hildegard.org","company":{"name":"Romaguera-Crona","catchPhrase":"Multi-layered client-server neural-net","bs":"harness real-time e-markets"}}

I first tried to pull in the JSON data from the URL using IMPORTDATA, but this removes commas (treated as delimiter characters) and the quotation marks that surround the attribute names.

=IMPORTDATA("https://jsonplaceholder.typicode.com/users/1")

enter image description here

I tried joining each part and readding the commas with TEXTJOIN. However, excess commas get added to the data, seemingly after curly brackets in the original JSON.

=TEXTJOIN(",", TRUE, IMPORTDATA("https://jsonplaceholder.typicode.com/users/1"))

enter image description here

My current workaround is to specify an dummy character (^) for IMPORTDATA's delimiter.

=IMPORTDATA("https://jsonplaceholder.typicode.com/users/1", "^")

enter image description here

However, this approach will fail if that placeholder delimiter ever appears in the JSON data. It also doesn't account for quotation marks getting removed from the attribute names. My end goal is to fetch specific attributes from the JSON, (for example, the name field), but I need to get the raw JSON into Google Sheets before I can start parsing it.

Is there a way I can import the JSON data directly from the URL into Google Sheets?

Solution

  • I've had luck with the following formula. It's written so that you can easily convert it into a Named Function, if desired. The input value is either a url or the string representation of the JSON, or a reference to the cell containing either of those two.

    Edit: To get the raw JSON into one cell, you can use this part of that formula.

    =join(":",map(split(join(,index(substitute(importdata(A1,"♦"),": ",":"))),":"),lambda(x,if(right(x)=char(34),x,regexreplace(x,"^(.*?)([0-9a-zA-Z_]+)$","$1""$2""")))))

    Where A1 contains the URL.

    =LAMBDA(urlOrJsonString,index(let(

GetImportSplits,lambda(a,let(b,join(":",map(split(join(,index(substitute(importdata(a,"♦"),": ",":"))),":"),lambda(x,if(right(x)=char(34),x,regexreplace(x,"^(.*?)([0-9a-zA-Z_]+)$","$1""$2"""))))),if(iserror(b),tocol(index(split(tocol(if(isurl(a),importdata(a,"["),split(a,"["))),"]")),1,0),tocol(index(split(tocol(split(b,"[")),"]")),1,0)))),

CreateObjArrStr,lambda(a,join("♣",map(tocol(split(substitute(a,"},{","}♣{"),"♣")),lambda(b,join("♥",torow(iferror(index(split(tocol(iferror(split(substitute(regexreplace(substitute(b,"""""","null"),",([""{])","♦$1"),"""",),"{♦}",1,0)),1),":"))))))))),

UnpackObj,lambda(a,let(b,tocol(split(a,"♣")),c,torow(unique(choosecols(wraprows(split(a,"♣♥",1,0),2),1))),vstack(c,map(b,lambda(d,let(e,wraprows(split(d,"♥",1,0),2),map(c,lambda(f,iferror(vlookup(f,e,2,)))))))))),

RemoveThe1stBlankCell,lambda(a,choosecols(a,sequence(1,columns(a)-1,2))),

importSplits,GetImportSplits(urlOrJsonString),

output,reduce(tocol(,1),importSplits,lambda(t,c,let(lastCol,choosecols(t,-1),lastHeader,single(lastCol),u,UnpackObj(CreateObjArrStr(c)),

if(iferror(counta(lastCol)=1,false),
  iferror(hstack(choosecols(t,sequence(1,columns(t)-1)),
    if(and(left(c)=",",right(c)=":",columns(u)=1),u,

    if(len(c)=0,vstack(lastHeader,"null"),

    if(left(c)="{",vstack(lastHeader&"."&chooserows(u,1),if(rows(u)=1,tocol(,1),chooserows(u,sequence(rows(u)-1,1,2)))),

    vstack(lastHeader,substitute(c,"""",))))))),

  iferror(hstack(t,UnpackObj(CreateObjArrStr(c)))))))),

final,RemoveThe1stBlankCell(output),

final)))(A1)