0

I am struggling trying to create a formula that results in an array to feed the formula sparkline(). How can I call formulas into the script? So far I have:

 function historic(term) {

var data = '=index(split(split(IMPORTXML('https://www.coingecko.com/en/coins/'"&term&"'/historical_data/usd/','//td[3]'),' '),'$'),0,0)';



  var timelineData=data["default"].timelineData;
  for (var i in timelineData) {
    var tdata=timelineData[i];
    resp.push(tdata.value[0]);
  }
  return resp;
}

function testHistoric(){
  historic("mobfish");
}
suciocerdo
  • 9
  • 2
  • You can not see this article https://stackoverflow.com/questions/25234912/how-to-use-native-spreadsheet-functions-in-google-apps-script – TheWizEd Feb 27 '22 at 13:08
  • thanks for your feedback. The problem is that I am trying to create a function, in the script example you shared, the result is in a specific cell. All the examples that I have found have a similar approach. I am not sure how to do it for a formula. – suciocerdo Feb 27 '22 at 13:27
  • The native Google Spreadsheet formula has to be in the spreadsheet you can not run it from app script. Assuming the formula has executed and the sheet populated with values you can use getValues() to get data from the spreadsheet and setValues() to put results back. I have provide a link to getValues() https://developers.google.com/apps-script/reference/spreadsheet/range#getValues() and setValues() is on the same page. – TheWizEd Feb 27 '22 at 13:30

1 Answers1

1

by formula

You got an error while writing the formula because of the mix between ' and ". Use instead Template_literals. It could be

function historic(term) {
  var formula = `=index(split(split(IMPORTXML("https://www.coingecko.com/en/coins/${term}/historical_data/usd/","//td[3]")," "),"$"),0,0)`;
  return formula;
}
function testHistoric() {
  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange('A1').setFormula(historic("bitcoin"));
}

enter image description here

You can use coingecko api

Or use this formula

=importhtml("https://www.coingecko.com/en/coins/bitcoin/historical_data/usd","table",1)

enter image description here

edit: by script

If you want to fetch the informations by script and not by formula, try

function testHistoric() {
  historic("bitcoin")
}
function historic(term) {
  url = `https://www.coingecko.com/en/coins/${term}/historical_data/usd/`;
  var html = '<table' + UrlFetchApp.fetch(url, {muteHttpExceptions: true}).getContentText().replace(/(\r\n|\n|\r|\t|  )/gm,"").match(/(?<=\<table).*(?=\<\/table)/g) + '</table>';
  var trs = [...html.matchAll(/<tr[\s\S\w]+?<\/tr>/g)];
  var data = [];
  for (var i=0;i<trs.length;i++){
    var tds = [...trs[i][0].matchAll(/<(td|th)[\s\S\w]+?<\/(td|th)>/g)];
    var prov = [];
    for (var j=0;j<tds.length;j++){
      donnee=tds[j][0].match(/(?<=\>).*(?=\<\/)/g)[0];
      prov.push((donnee));
    }
    data.push(prov);
  }
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  sh.getRange(1,1,data.length,data[0].length).setValues(data)
}
Mike Steelson
  • 14,650
  • 2
  • 5
  • 20