jsongoogle-sheetspoloniex

Poloniex APi to Google Sheet CSV via Json


I've got the following script that pulls keys from the Poloniex JSON output, but doesn't put the actual data that corresponds to the keys into the actual sheet...it only puts the keys as titles at the top of the sheet.

I'm new to API's, and GAS, and coding in general, so I'm sure I'm missing something incredibly obvious, I'd really appreciate it if you could point out what.

Thanks in advance

function Bitcoin_fromPolo_toCSV() {
  //Link the script with a spreasdsheet using the identifier found in the spreadsheet url
  var ss = SpreadsheetApp.openById('1cubxxxxxxxxxxxxjDqM');
  var APIPullSheet = ss.getSheetByName("APIPull");


    // Clear Columns A,B,C,D
  APIPullSheet.getRange('A2:D19999').clearContent();


  var url = "https://poloniex.com/public?command=returnChartData&currencyPair=BTC_ETH&start=1502344800&end=9999999999&period=14400";

  //Fetch pulls data from URL

  var responseAPI = UrlFetchApp.fetch(url);


  //Parse that JSON
  var parcedData = JSON.parse(responseAPI.getContentText());


  //Break that Parsed data into fields
  //Define the 'stats' array, and populate it from the parced data pulled
  // for loop iterates over each 'key' in 'parcedData' pushing that data to 'stats'


  var stats = [];
  stats.push(['date','high', 'low', 'open', 'close', 'volume', 'quoteVolume', 'weightedAverage']);


  for(var key in parcedData.stats)
  {
    stats.push(parcedData.stats[key]); 
  }


  statsRange = APIPullSheet.getRange(1, 1, stats.length, 8);
  statsRange.setValues(stats);

}


Solution

  • How about the following modification?

    Modification points :

    JSON data from URL is as follows.

    [
      {
        "date": 1502352000,
        "high": 0.0899,
        "low": 0.08754124,
        "open": 0.08795499,
        "close": 0.08988724,
        "volume": 1390.47552953,
        "quoteVolume": 15727.49124739,
        "weightedAverage": 0.08841051
      },
    .
    .
    
    ]
    

    parcedData doesn't have stats as a key.

    Flow for creating data :

    1. Outer forEach() retrieves an element from parcedData.
    2. Inner forEach() retrieves each key from stats[0], and retrieves data from the element of parcedData using the key.
    3. Retrieved data is imported to temp which is 1 dimensional array.
    4. The temp is imported to stats which is 2 dimensional array. After this, temp is initialized.

    The script reflected this is as follows.

    Modified script :

    function Bitcoin_fromPolo_toCSV() {
      var ss = SpreadsheetApp.openById('1cubxxxxxxxxxxxxjDqM');
      var APIPullSheet = ss.getSheetByName("APIPull");
      APIPullSheet.getRange('A2:D19999').clearContent();
      var url = "https://poloniex.com/public?command=returnChartData&currencyPair=BTC_ETH&start=1502344800&end=9999999999&period=14400";
      var responseAPI = UrlFetchApp.fetch(url);
      var parcedData = JSON.parse(responseAPI.getContentText());
      var stats = [];
      stats.push(['date','high', 'low', 'open', 'close', 'volume', 'quoteVolume', 'weightedAverage']);
    
      parcedData.forEach(function(e1){
        var temp = [];
        stats[0].forEach(function(e2){
          temp.push(e1[e2])
        });
        stats.push(temp); 
      });
    
      statsRange = APIPullSheet.getRange(1, 1, stats.length, 8);
      statsRange.setValues(stats);
    }
    

    Result :

    enter image description here

    If I misunderstand your question, I'm sorry.