In Google Sheets, how to output an array of GraphQL API calls using a column as reference?

user_16498578 0
Viewed 127

I placed this question below, a solution was provided and it works well for rest APIs:
In Google Sheets, how can I output an array of IMPORTJSON() calls using a column as reference?

The problem is that actually the API that will be used is a GraphQL API and the script does not work well for this situation. It returns blank only.

This is the script proposed in the solution above (check url), slightly modified for my needs, and works for REST API but not for a GraphQL API:

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
}

The custom function is being called like so:

=QUERY(
    ARRAYFORMULA(
        importAllJSONArray("https://marketplace-api.pegaxy.io/graphql?operationName=QueryPegaListing&variables=%7B%22id%22%3A"&QUERY(
        {A2:A},"SELECT * WHERE Col1 IS NOT NULL")&"%7D&extensions=%7B%22persistedQuery%22%3A%7B%22version%22%3A1%2C%22sha256Hash%22%3A%220bd7bcc0b53b0348328cbdfe6934685e7f7e272e607eca0552108fbbb8cee79f%22%7D%7D"
        )
    ),
"OFFSET 1",0)

Note that the formula works for the REST API so should work for the GraphQL API urls.

For the REST API it is ok:
enter image description here

For a GraphQL API it returns empty (because of the Query Offset. Without the Query it returns only data):
enter image description here

Is there any tweak that can be applied for the script above to work for GraphQL APIs?

Check my demo file to have a better idea:
https://docs.google.com/spreadsheets/d/1OVXk9MVRtVXiOl-btz3BI33UZu-c2F6Nro4jRZBmMsk/edit

google-sheets-formula graphql google-apps-script google-sheets arrays
1 Answers

When I saw the values of marketplace-api.pegaxy.io/graphql in your question and the values of api-apollo.pegaxy.io/v1, the structure of JSON is different. From your showing script, I think that this is the reason for your issue. And, when I saw your sample Spreadsheet, I noticed that your Google Apps Script project includes ImportJSON. In this case, I thought that ImportJSON can be used.

When your showing script is modified by using ImportJSON, how about the following modification?

Modified script:

function importAllJSONArray(url) {
  var listUrls = Array.isArray(url) ? url.flat() : [url];
  var result = [];
  listUrls.forEach((address, i) => {
    var [h, ...json] = ImportJSON(address);
    if (i == 0) {
      result.push(h);
    }
    result = [...result, ...json];
  })
  return result
}

Testing:

When this script is run to your sample Spreadsheet, the following result is obtained.

enter image description here

user_71086530
Related