Search code examples
arraysjsongoogle-apps-scriptgoogle-sheetsgoogle-sheets-formula

In Google Sheets, how can I output an array of IMPORTJSON() calls using a column as reference?

I hope the question is clear, if not let me know so I can rewrite.

I am using IMPORTJSON() from Github:
https://github.com/bradjasper/ImportJSON

What I want to do seems possible to do with HYPERLINK():

=ARRAYFORMULA(
    HYPERLINK("https://api-apollo.pegaxy.io/v1/pegas/"&QUERY(
        {A2:A},"SELECT * WHERE Col1 IS NOT NULL")
        )
    )

I Want to build an array of IMPORTJSON() calls the same way. You'd think I should be able to do this the same way:

=ARRAYFORMULA(
    ImportJSON("https://api-apollo.pegaxy.io/v1/pegas/"&QUERY(
        {A2:A},"SELECT * WHERE Col1 IS NOT NULL"),
        "/energy", "noHeaders")
        )

enter image description here

But I'm getting some errors. ATM its displaying:

Exception: Request failed for 
https://api-apollo.pegaxy.io returned code 404. 
Truncated server response: <!DOCTYPE html> <html lang="en">
     <head> 
        <meta charset="utf-8"> 
        <title>Error</title> 
        </head> 
    <body> 
        <pre>Cannot GET /v1/pegas/923195,https://api-apo... 
            (use muteHttpExceptions option to examine full response) 
            (line 217).

Another thing I tried, check Sheet2, was to simplify the call with this:

=ARRAYFORMULA(ImportJSON({E2:E}))

Where E2:E is the list of URLs built with HYPERLINK(). With this I also get an error:

Exception: Limit Exceeded: URLFetch URL Length. (line 217).

So maybe ÌMPORTJSON() does not allow for building an array?
Is there a work around for this situation or is there something wrong with my formulas?

Demo file here:
https://docs.google.com/spreadsheets/d/1OVXk9MVRtVXiOl-btz3BI33UZu-c2F6Nro4jRZBmMsk/edit

EDIT:

I accepted Mike's solution but I changed it a bit to suit my needs:

function importAllJSONArray(url) {
  var listUrls = Array.isArray(url) ? url.flat() : [url]
  var listXpath = []
  var result = []
  listUrls.forEach((address, i) => {
    var prov = []
    var json = JSON.parse(UrlFetchApp.fetch(address).getContentText())
    if (i == 0) { 
      listXpath = Object.keys(json); 
      result.push(listXpath)
    }
    listXpath.forEach(xp => {
      prov.push(json[xp])
    })
    result.push(prov)
  })
  return result
}
Solution

  • You need to build your own arrayformula as follows

    =arrayformulaJson(A2:A7,B1:D1)

    code.gs

    function arrayformulaJson(ids,xpath) {
  listIds = Array.isArray(ids) ? ids.flat() : [ids]
  listXpath = Array.isArray(xpath) ? xpath.flat() : [xpath]
  var result = []
  listIds.forEach(id => {
    var prov = []
    var json = JSON.parse(UrlFetchApp.fetch('https://api-apollo.pegaxy.io/v1/pegas/' + id).getContentText())
    listXpath.forEach(xp => {
      prov.push(json[xp])
    })
    result.push(prov)
  })
  return result
};

    enter image description here

    edit:

    to import the whole JSON including headers

    =arrayformulaAllJson(A2:A7)

    note that the result includes the headers, you may put the formula one row before the range

    function arrayformulaAllJson(ids) {
  var listIds = Array.isArray(ids) ? ids.flat() : [ids]
  var listXpath = []
  var result = []
  listIds.forEach((id, i) => {
    var prov = []
    var json = JSON.parse(UrlFetchApp.fetch('https://api-apollo.pegaxy.io/v1/pegas/' + id).getContentText())
    if (i == 0) { 
      listXpath = Object.keys(json); 
      result.push(listXpath)
    }
    listXpath.forEach(xp => {
      prov.push(json[xp])
    })
    result.push(prov)
  })
  return result
}

    enter image description here