Using importHTML works 9 out of 10 times. Thinking of including a feedback loop, but how?-CodePudding

I'm using the importHTML function to pull the current prices of the top 100 crypto coins from Coingecko to a Google Sheet. It works great most of the times, but sometimes the formula goes to N/A in these two cases:

After having the sheet open for a few hours of inactivity;
After reloading the formula to get updated prices.

This is the code I'm using in my Macro, which is triggered once a day in the morning to get the updated prices:

spreadsheet.getRange('A1').activate(); spreadsheet.getCurrentCell().setFormula('=importhtml("https://www.coingecko.com/";"table";1)')

As mentioned, it works great 9 out of 10 times. But how do I get it right 10 out of 10 times? I'm no programmer, but I would love to have a loop where the script checks the following:

"If the cell A1 returns N/A, wait 30 seconds and refresh formula. Keep repeating this action until the cell is not N/A anymore". Or give up after 10 tries (to avoid an infinite loop).

Not sure if this logic is correct or if there is even a better logic out there, but happy to hear your advice!

Many thanks in advance,

Paul

CodePudding user response：

try:

spreadsheet.getRange('A1').activate(); 
spreadsheet.getCurrentCell().setFormula('=ifna(importhtml("https://www.coingecko.com/";"table";1);importhtml("https://www.coingecko.com/";"table";1))')

CodePudding user response：

Try and put a trigger on that function

function importTable() {
  var url = 'https://www.coingecko.com/'
  var n = 1
  var html = UrlFetchApp.fetch(url, { followRedirects: true, muteHttpExceptions: true }).getContentText().replace(/(\r\n|\n|\r|\t|  )/gm, "")
  const tables = [...html.matchAll(/<table[\s\S\w] ?<\/table>/g)];
  var trs = [...tables[n - 1][0].matchAll(/<tr[\s\S\w] ?<\/tr>/g)];
  var data = [];
  trs.forEach(tr => {
    var tds = [...tr[0].matchAll(/<(td|th)[\s\S\w] ?<\/(td|th)>/g)];
    var prov = [];
    tds.forEach(td => {
      donnee = td[0].match(/(?<=\>).*(?=\<\/)/g)[0];
      prov.push(donnee.replace(/(<([^>] )>)/g, ""));
    })
    data.push(prov);
  })
  const sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  sh.getRange(1, 1, data.length, data[0].length).setValues(data)
}