CODEWITHSUNDEEP
javascriptarraysjsongoogle-apps-scriptgoogle-sheets
aka mtv
aka mtv

Reputation: 3

Google sheets scripts editor and correctly parsing JSON

I'm trying to parse a JSON file from Binance API so i can create a candlestick chart on google spreadsheets.

Nevertheless i cannot seem to figure out how can this be achieved... This is what i've tried so far:

function myJSON(){
  url = "https://api.kraken.com/0/public/OHLC?pair=XXBTZEUR&since=0&interval=1"
  var ss = SpreadsheetApp.getActiveSpreadsheet(),
  coinsheet = ss.getSheetByName('Sheet1')
  var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
  var json = response.getContentText();
  var rawdata = JSON.parse(json);
  var data = JSON.stringify(rawdata.result.XXBTZEUR)
  data = JSON.parse(data)
  for (var i = 0; i < data.length; i++) {
    for (var j = 0; j < data[i].length; j++) {
       var mdata = new Array();var obj = {};
       var utcSeconds = data[i][0];
       var mdate = new Date(0); // The 0 there is the key, which sets the date to the epoch
       mdate.setUTCSeconds(utcSeconds);
       open = parseFloat(data[i][1])
       high = parseFloat(data[i][2])
       low = parseFloat(data[i][3])
       close = parseFloat(data[i][4])
       obj['date'] = mdate
       obj['open'] = open
       obj['high'] = high
       obj['low'] = low
       obj['close'] = close
       mdata.push(obj);
       Logger.log(mdata);
       coinsheet.appendRow(mdata);
    }
  }
}

While running the function Logger gives this output but yields the following error:

This action would increase the number of cells in the workbook above the limit of 2000000 cells

How is that possible ? There are only 720 records, and why doesn't the mdata variable get overwritten at each loop? Shouldn't this create a table with column names and data automatically ? Totally lost here. Any help with be appreciated.

Upvotes: 0

Views: 1225

Answers (1)

chuckx
chuckx

Reputation: 6877

Here's an updated implementation (tested on a sheet, and it successfully appends rows):

function appendApiData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var coinsheet = ss.getSheetByName('Sheet1');
  var url = 'https://api.kraken.com/0/public/OHLC?pair=XXBTZEUR&since=0&interval=1';
  var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
  var parsedResponse = JSON.parse(response.getContentText());
  var data = parsedResponse.result['XXBTZEUR'];
  for (var i = 0; i < data.length; i++) {
    var utcSeconds = data[i][0];
    var mdate = new Date(0); // The 0 there is the key, which sets the date to the epoch
    mdate.setUTCSeconds(utcSeconds);
    var open = parseFloat(data[i][1])
    var high = parseFloat(data[i][2])
    var low = parseFloat(data[i][3])
    var close = parseFloat(data[i][4])
    var row = [mdate, open, high, low, close];
    Logger.log(row)
    coinsheet.appendRow(row);
  }
}

And here's an excerpt from the logs:

[18-06-01 11:44:41:028 PDT] [Thu May 31 23:45:00 GMT-07:00 2018, 6452.5, 6452.5, 6447.6, 6448.2]
[18-06-01 11:44:41:146 PDT] [Thu May 31 23:46:00 GMT-07:00 2018, 6450.5, 6450.9, 6450.5, 6450.6]
[18-06-01 11:44:41:263 PDT] [Thu May 31 23:47:00 GMT-07:00 2018, 6450.6, 6450.6, 6447.1, 6449.7]
[18-06-01 11:44:41:364 PDT] [Thu May 31 23:48:00 GMT-07:00 2018, 6449.5, 6449.7, 6447.2, 6447.2]
[18-06-01 11:44:41:446 PDT] [Thu May 31 23:49:00 GMT-07:00 2018, 6447.2, 6450.5, 6447.2, 6450.5]

Some notes regarding what I changed:

  • if you're not going to use the intermediate values, you can string together method calls (i.e. JSON.parse(response.getContentText()))
  • avoid JSON.stringify() followed by JSON.parse(), you're needlessly encoding/decoding data
  • remove the inner loop, you're referencing the array items explicitly by index, so there's no need for it. As it stands, the inner loop is causing you to process each row [number of row items] times
  • avoid new Array(), just use an array literal (i.e. [])
  • when constructing the row, you need to create an array of row values, not an array containing a single object. See the Sheet.appendRow() example in the documentation

Upvotes: 1

Related Questions