import binance api data into google sheet

Question

I'm just a newbie trying to import raw binance api data into a google sheet. I tried using Mixed Analytics API Connector but the result is usually "completed with errors". And the support team suggestions didn't help at all with the end result still the same and so the data is still the same from its previous data that was a week old already. You could see the raw binance api data on the link below.

https://api.binance.com/api/v3/ticker/24hr

And so I think it, the way only to tackle this problem would be to code it as a google script. I would greatly appreciate any help I can get. Any sample code gs code would be very helpful. Thank you very much...

Answer 1

Here is a solution. Put a triger if needed on the function horodatage (i.e. each day)

// Mike Steelson

let resultat = [];

// mettre déclencheur horaire sur cette fonction
// define a trigger here
function horodatage(){
  var f = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data')
  f.getRange('A1').setValue(!f.getRange('A1').getValue())
  f.getRange('B1').setValue(new Date())
}

function getDataJSON(url,xpath){
  try{
    if (url.match(/http(s)?:\/\/?/g)){var data = JSON.parse(UrlFetchApp.fetch(url).getContentText())}
    else{var data = JSON.parse(url)}
    var json = eval('data')
    if (typeof xpath == 'object'){var liste = xpath.join().split(",")} else {var liste = xpath.split("|")}
    if (json.length){json.forEach(function(elem){getData(elem,liste)})} else {getData(json,liste)}
    return resultat
  }
  catch(e) {
    return ('Pas de résultat - vérifier l\'url et les paramètres !');
  }
}
function getData(elem,liste){
  var prov=[]
  liste.forEach(function(chemin){
    var t=chemin.split('/');
    var obj=elem;
    for (var i=1;i<t.length;i++){obj=obj.item(t[i])}
    if(typeof obj=='object'){prov.push('['+obj+']')}else{prov.push(obj)}
  })
  resultat.push(prov)
}
Object.prototype.item=function(i){return this[i]};

you can take a copy of this spreadsheet https://docs.google.com/spreadsheets/d/1DN0Gfim0LC098zVgrUpt2crPWUn4pWfZnCpuuL1ZiMs/copy

Answer 2

i cannot comment but here could be a solution, instead of api.binance.com/api/v3/ticker/24hr write api1.binance.com/api/v3/ticker/24hr I have added 1 to api. in a video I saw it works for him.... but for me didnt work. let me know if it was useful thanks