jsongoogle-sheets

Google Sheets: json into columns

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: enter image description here

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!

Solution

  • 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++) {

    ex

    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);
  }

    ex2