I'm completely new to this and would appreciate your help.

I am trying to import GraphQL prices into Google Sheets and display the information in columns.

So far, I have this:

function graphData(query,url){
  var options = {"headers": {"Content-Type": "application/json"
                            },
                 "payload": JSON.stringify({query}),
                 "method": "POST"
                };
  var response = UrlFetchApp.fetch(url, options);
  Logger.log(response);
  return response;
};

function Prices () {
  var ss = SpreadsheetApp.openById('1uEpj8gWdHJCVXN2fTgGBCzoi5ZOnkIq0leoGWDB1s-g'); 
  var sheet = ss.getSheetByName('Test'); 
  //sheet.appendRow(["a man", "a plan", "panama"]);
  var url = 'https://api.thegraph.com/subgraphs/name/openpredict/chainlink-prices-subgraph' ;

  var query = 'query{prices(first:2 where:{assetPair_in:["USDC/ETH","DAI/ETH "]} orderBy:timestamp orderDirection:desc) {assetPair{id}timestamp price}}';
  var a=graphData(query,url);
  var code= a.getContent();
  var json = a.getContentText(); // get the response content as text

  var mae = JSON.parse(json); //parse text into json

Variable mae contains the following:

I am stuck at this point, not succeeding to properly access and display assetPair, timestamp and price in columnns.

I have tried this, which is not quite right:

  var data;

    for (i = 0; i < mae.length; i  ) {
      data = mae["data"]["prices"][i];
      var rows = [];
      for (j in data) rows.push(data[j]);
      sheet.appendRow(rows);
    }

Any help would be most welcome!

CodePudding user response：

The following script works for me:

function graphData(query,url){
  var options = {"headers": {"Content-Type": "application/json"
                            },
                 "payload": JSON.stringify({query}),
                 "method": "POST"
                };
  var response = UrlFetchApp.fetch(url, options);
  Logger.log(response);
  return response;
};

async function Prices () {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheet = ss.getSheetByName('Sheet1'); 
  //sheet.appendRow(["a man", "a plan", "panama"]);
  var url = 'https://api.thegraph.com/subgraphs/name/openpredict/chainlink-prices-subgraph';

  var query = 'query{prices(first:2 where:{assetPair_in:["USDC/ETH","DAI/ETH "]} orderBy:timestamp orderDirection:desc) {assetPair{id}timestamp price}}';
  var a = graphData(query,url);
  var code= a.getContent();
  var json = a.getContentText(); // get the response content as text

  var mae = JSON.parse(json);

  var data;
  for (i = 0; i < mae.data.prices.length; i  ) {
    data = mae.data.prices[i];
    var rows = [];
    for (j in data) rows.push(data[j]);
    sheet.appendRow(rows);
  }
}

---

You had

 data = mae["data"]["prices"][i];

I changed this to

data = mae.data.prices[i];

---

I also changed

for (i = 0; i < mae.length; i  ) {

to

for (i = 0; i < mae.data.prices.length; i  ) {

---

---

---

If you would like to get just the ID in the first column, you could change the FOR loop to include this:

  var data;
  for (i = 0; i < mae.data.prices.length; i  ) {
    data = mae.data.prices[i];
    var rows = [];

    for (j in data) {
      if(j == 'assetPair') rows.push(data[j].id)
      else rows.push(data[j])
    };
    sheet.appendRow(rows);
  }

CodePudding user response：

Function for you

function graphData(query,url){
  var options = {"headers": {"Content-Type": "application/json"
                            },
                 "payload": JSON.stringify({query}),
                 "method": "POST"
                };
  var response = UrlFetchApp.fetch(url, options);
  Logger.log(response);
  return response;
};

function Prices () {
  var ss = SpreadsheetApp.openById('1uEpj8gWdHJCVXN2fTgGBCzoi5ZOnkIq0leoGWDB1s-g'); 
  var sheet = ss.getSheetByName('Test'); 
  sheet.appendRow(["a man", "a plan", "panama"]);
  var url = 'https://api.thegraph.com/subgraphs/name/openpredict/chainlink-prices-subgraph' ;

  var query = 'query{prices(first:2 where:{assetPair_in:["USDC/ETH","DAI/ETH "]} orderBy:timestamp orderDirection:desc) {assetPair{id}timestamp price}}';
  var a=graphData(query,url);
  var code= a.getContent();
  var json = a.getContentText(); // get the response content as text

  var mae = JSON.parse(json); //parse text into json
  
  var rows = [],dat,lastR=sheet.getLastRow() 1;
  mae.data.prices.forEach(x => {rows[rows.length]=[x.assetPair.id,x.timestamp,x.price];});
  sheet.getRange('A' lastR ':C' (lastR rows.length-1)).setValues(rows);
  }