ImportJSON.gs parse nested JSON array

Question

I'm using the ImportJSON library in Google Sheets to parse JSON files. I have come across a very weird looking JSON doc that I am unsure about how to parse it. The JSON doc looks like this:

{
  "draw": 2,
  "recordsTotal": "80129",
  "recordsFiltered": "3988",
  "data": [
    [
      "28/08/2020",
      "US DOLLAR",
      "108.2221",
      "108.1224",
      "108.3218"
    ],
    [
      "27/08/2020",
      "US DOLLAR",
      "108.1529",
      "108.0529",
      "108.2529"
    ]
  ]
}

I am interested in getting the 0th index under /data and the third item in that list (108.2221).

Using a query of /data/0 in the function returns #REF

How can this be achieved in Google Spreadsheets using this lib?

Is that the value you are getting back ? Can you please share a copy of your sheet or a screenshot of the output you are getting ? — Marios
– Marios, Commented Aug 28, 2020 at 23:34
The json shared above in the input. Its is what's on the URL that I am trying to read. Using a query of /data/0 gives #REF — 170730350
– 170730350, Commented Aug 29, 2020 at 9:13
I posted an alternative solution. Please check and let me know if it was helpful. — Marios
– Marios, Commented Aug 29, 2020 at 9:54

Marios · Accepted Answer · 2020-08-29 12:32:03Z

1

Solution:

You can instead create your own custom function:

function myCustomFunction(url,pos) {
  
  const response = UrlFetchApp.fetch(url);
  const arr =JSON.parse(response.getContentText());
  return  arr['data'][0][pos-1];
}

and use it again as a formula.

For example, to get the 1st element:

=myCustomFunction("https://www.centralbank.go.ke/wp-admin/admin-ajax.php?action=get_wdtable&table_id=32",1)

or 3rd element:

=myCustomFunction("https://www.centralbank.go.ke/wp-admin/admin-ajax.php?action=get_wdtable&table_id=32",3)

Output:

Limitations:

You can't pass any url as an argument. The json url needs to have the structure you mentioned in the question.
This formula only works for the data field.

References:

edited Aug 29, 2020 at 12:32

answered Aug 29, 2020 at 9:54

Marios

27.5k9 gold badges40 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

170730350 Over a year ago

This works. So I have accepted it as it well answers my question. I am just realizing now that I had used a wrong payload I had filtered for US data). I will try and get the script to look up US DOLLAR from the returned JSON file and get pos-1 where pos=3 :-) Thanks

Collectives™ on Stack Overflow

ImportJSON.gs parse nested JSON array

1 Answer 1

Solution:

Output:

Limitations:

References:

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Solution:

Output:

Limitations:

References:

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related