Google Sheets IMPORTHTML

Question

I have completed the following script. I want to get a table in my Google Sheet with IMPORTHTML, everything works fine, only if the data changes, then that doesn't change in the Google table. I run the whole thing with a trigger so that will be updated every minute. But here comes the problem, if I let the whole thing run like this and the data changes on the website, and here comes now the problem that doesn't change in the Google Sheet, the old values are simply taken over and over again and not the new ones. If I delete IMPORTHTML from the cell manually and then copy it back in, it works strangely. But unfortunately it doesn't work with the script and the trigger, what am I doing wrong?

function getData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Lager123");
    
  var importXpath_1 = '=IMPORTHTML("URLwithSIMPLEtable","table", 1)';
  
  sheet.getRange("A1").setValue(importXpath_1);
  
}

Marios · Accepted Answer · 2020-08-24T07:57:33.230

1

Clear the content of the cell and then flush() it before you set the value:

function getData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Lager123");
    
  var importXpath_1 = '=IMPORTHTML("URLwithSIMPLEtable","table", 1)';
  sheet.getRange("A1").clearContent();
  SpreadsheetApp.flush();
  sheet.getRange("A1").setValue(importXpath_1);
  
}

References:

edited Aug 24 '20 at 07:57

answered Aug 24 '20 at 07:47

Marios

26,333
8
32
52

This is what I tried alread, but this is also not working. It still takes the old values. – mKeey Aug 24 '20 at 07:54
maybe the data does not get updated every minute, so you need to wait a little bit longer to see the changes? – Marios Aug 24 '20 at 07:55
1

@mKeey for the shake of completeness I updated my answer. – Marios Aug 24 '20 at 07:58

score 0 · Answer 2 · answered Jun 06 '21 at 12:03

For not getting cached response in importhtml, we can append some random query parameter in URL string.

var queryString = Math.random(); // This will generate random value
  sheetName.getRange('P7').setValue(queryString); // This random value will be placed in Cell P7

  var cellFunction = '=ImportHtml("URL?"&P7;"table";1)}'; // Value from P7 cell will be appended in URL to avoid caching issue
    sheetName.getRange('A1').setValue(cellFunction);

I tried this in my project and it's working perfectly.

Google Sheets IMPORTHTML

2 Answers2

Linked