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?

Answer 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:

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

---

References:

• URL Fetch Service

• JSON.parse()

• Custom functions