CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
user3546314
user3546314

Reputation: 134

Can't get data from API on my Google Spreadsheet

I manage to get data from https://api.hitbtc.com/api/2/public/ticker to my Google Spreadsheet using this code script

    function ticker(){
  var rows=[],obj_array=null;
  try {obj_array=JSON.parse(UrlFetchApp.fetch("https://api.hitbtc.com/api/2/public/ticker").getContentText());} catch (e) {obj_array=null;}
  if (obj_array!=null){
    for (r in obj_array) {rows.push([obj_array[r].symbol,parseFloat(obj_array[r].last),parseFloat(obj_array[r].volume),parseFloat(obj_array[r].ask),parseFloat(obj_array[r].bid)]);}
    var ss=SpreadsheetApp.getActiveSpreadsheet(),sheet=ss.getSheetByName('Hitbtc');ss.getRange("Hitbtc!A1").setValue(new Date());
    try {var range=sheet.getRange(2,1,sheet.getLastRow(),6).clearContent();} catch(e) {Logger.log("error");}
    if (rows==null||rows=="") {Browser.msgBox("Oops, no data"); return false;}
    range=sheet.getRange(2,1,rows.length,5); range.setValues(rows); 
  }
}

Then I try another API from https://api.kucoin.com/api/v1/market/allTickers, I can't manage to get the data.Always show #NUM!. Can someone help me?

function kuticker(){
  var rows=[],obj_array=null;
  try {obj_array=JSON.parse(UrlFetchApp.fetch("https://api.kucoin.com/api/v1/market/allTickers").getContentText());} catch (e) {obj_array=null;}
  if (obj_array!=null){
    for (r in obj_array) {rows.push([obj_array[r].symbol,parseFloat(obj_array[r].last),parseFloat(obj_array[r].vol),parseFloat(obj_array[r].buy),parseFloat(obj_array[r].sell)]);}
    var ss=SpreadsheetApp.getActiveSpreadsheet(),sheet=ss.getSheetByName('Kucoin');ss.getRange("Kucoin!A1").setValue(new Date());
    try {var range=sheet.getRange(2,1,sheet.getLastRow(),6).clearContent();} catch(e) {Logger.log("error");}
    if (rows==null||rows=="") {Browser.msgBox("Oops, no data"); return false;}
    range=sheet.getRange(2,1,rows.length,5); range.setValues(rows); 
  }
}

Here the file https://docs.google.com/spreadsheets/d/17lDKTVgqw99zqXYtuWHT3FgH7ahyyIqgGxgd4wld8cw/edit?usp=sharing

Thank you

Upvotes: 1

Views: 351

Answers (1)

Tanaike
Tanaike

Reputation: 201378

How about this answer?

Modification points:

  • I think that the reason of your issue is due to the difference structure between the object retrieved from https://api.hitbtc.com/api/2/public/ticker and the object retrieved from https://api.kucoin.com/api/v1/market/allTickers.
    • At the former, the data array can be directly from obj_array.
    • At the latter, the data array is included in obj_array.data.ticker.

When this is reflected to your script, please modify your function of kuticker() as follows.

Modified script:

From: 
obj_array=JSON.parse(UrlFetchApp.fetch("https://api.kucoin.com/api/v1/market/allTickers").getContentText());
To: 
obj_array = JSON.parse(UrlFetchApp.fetch("https://api.kucoin.com/api/v1/market/allTickers").getContentText()).data.ticker;

Result:

When above modified script is run, the following result can be obtained.

enter image description here

Upvotes: 1

Related Questions