ImportHTML won't update data

Question

I'm trying to use ImportHTML in Google Spreadsheets to update data regularly. The standard ImportHTML-code updates once an hour which is too slow, so I found some Google Script to do it every minute. (I should mention I'm not a developer so I'm kinda operating blind)

However, the script doesn't replace the old data with the new data (which is the idea). I need to remove all data from the spreadsheet for it to update the information. It seems to me te script is executing, but simply doesn't overwrite when cells are already filled. This is the Script I'm using:

function myFunction() {
  var sh = SpreadsheetApp.getActiveSheet();
  var r = "A1";
  var insertDataOption = 'overwrite';
  var f = '=ImportHTML("URL";"table";1)';
   sh.getRange(r).setFormula(f) .onChange;
}

I hope you can help, thankS

Possible duplicate [Google Sheets IMPORTHTML](https://stackoverflow.com/q/63556446/1595451), [Trigger importHTML in Google Apps Script](https://stackoverflow.com/q/42171487/1595451), [Script to refresh data with IMPORTHTML in Google Sheets](https://stackoverflow.com/q/58854739/1595451) — Rubén, Oct 19 '20 at 17:16

Rubén · Accepted Answer · 2020-10-19T17:21:15.890

If you add the same formula it might not not trigger a IMPORTHTML update. To make it work, first you could to clear the cell holding the IMPORTHTML formula, in this case A1, force the spreadsheet update, then add the formula.

function myFunction() {
  var sh = SpreadsheetApp.getActiveSheet();
  var r = "A1";
  var insertDataOption = 'overwrite';
  var f = '=ImportHTML("URL";"table";1)';
  var range = sh.getRange(r);
  range.clear();
  SpreadsheetApp.flush();
  range.setFormula(f);
}

By the other hand if you are already using Google Apps Script, instead of using IMPORTHMTL you could use the Fetch Service to to the same job. This will require more coding but you will have more control over how frequently is updated your spreadsheet.

Resources

https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#flush

Related (More recent first)

Hi Rubén, thanks a lot! Your code seems to work perfectly! Will look into the Fetch Service as well, as I'm trying to get more skilled at the use of Apps Script, both personally and professionally. — Alwin Wubs, Oct 20 '20 at 07:08

ImportHTML won't update data

1 Answers1