IMPORTHTML function not refreshing data automatically in Google Sheets

Other Questions Google Sheets
1

I created a script that uses IMPORTHTML to pull table data into my Google Sheet. The function works perfectly when I use it manually, but I’m facing an issue with automatic updates.

I set up a time-based trigger to run the script every minute so the data stays current. However, when the source website updates its data, my Google Sheet keeps showing the old cached values instead of fetching the new information.

The strange part is that if I manually delete the IMPORTHTML formula from the cell and then paste it back, it immediately pulls the fresh data. But when my automated script runs with the trigger, it just keeps the stale data.

Here’s my current code:

function fetchTableData() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var targetSheet = spreadsheet.getSheetByName("DataSheet");
    
  var htmlFormula = '=IMPORTHTML("https://example.com/data","table", 1)';
  
  targetSheet.getRange("A1").setValue(htmlFormula);
}

What could be causing this caching issue and how can I force IMPORTHTML to refresh the data when triggered automatically?

2

IMPORTHTML has caching that sticks around even when you rewrite the formula through scripts. Google Sheets doesn’t think your setValue needs fresh data since the formula looks the same. I hit this exact issue building a competitor pricing dashboard. Here’s what fixed it - make the formula look different each time by adding a dummy parameter that breaks the cache but doesn’t mess with the import:

function fetchTableData() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var targetSheet = spreadsheet.getSheetByName("DataSheet");
  var timestamp = new Date().getTime();
  var htmlFormula = `=IMPORTHTML("https://example.com/data?refresh=${timestamp}","table", 1)`;
  targetSheet.getRange("A1").setValue(htmlFormula);
}

The timestamp makes each formula unique, so Google Sheets treats it as a new request instead of using cached data. Most sites ignore random parameters anyway, so it won’t break your data source.

3

SpreadsheetApp.flush() after setting the formula fixed this for me. Google Sheets batches operations and won’t immediately process new formulas - that’s why manual updates work but automated ones don’t. I had the same issue tracking stock prices. Just add SpreadsheetApp.flush() right after your setValue() and it forces the spreadsheet to process the formula instead of queuing it. Sometimes I throw in a Utilities.sleep(2000) after the flush to give IMPORTHTML time to fetch the data before the script finishes. Not pretty, but it works without messing with URLs or clearing cells.

4

clear the cell completely before adding the formula back. i had the same issue and this fixed it - use clearContent() first, then setValue(). like targetSheet.getRange(“A1”).clearContent(); then add your formula. google sheets cache gets messed up when you just overwrite formulas, but clearing forces a proper refresh.