Script to refresh data with IMPORTHTML in Google Sheets

Question

I'm trying to get the data about XAU prices from table here: https://www.investing.com/currencies/xau-pln-historical-data

I need to have the data in google sheets and the problem is that it doesn't refresh the data. When I clean cells manually and insert formula then data is refreshed, but when I do it using script it doesn't work. I have this script (based on https://support.geckoboard.com/hc/en-us/articles/206260188-Use-Google-Sheets-ImportHTML-function-to-display-data-in-Geckoboard):

function getData2() { 
  var sheetName = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Rank");
  var cellFunction = '=INDEX(IMPORTHTML("https://www.investing.com/currencies/xau-pln-historical-data","table",1),2,2)';
  var PricesRange = sheetName.getRange('K2:K14');
  PricesRange.clearContent();
  var queryString = Math.random();
  for (var j = 2; j <= 14; j++) {
    var cellFunction2 = '=INDEX(IMPORTHTML("https://www.investing.com/currencies/xau-pln-historical-data","table",1),' + j + ',2)';
    sheetName.getRange(j,11).setValue(cellFunction2);
  }
}

I set up a trigger which runs the script every minute but the data is still the same.

Do you have any ideas what should I do?

Answer 1

A much easier solution is to enable circular references with 1 iteration. For example put in cell A1 =A1+1 then append your url with "?"&A1

Answer 2

You have declared the queryString variable but you have never used it. The idea of having it is to append it to the URL, so that it gets recognized as a different URL (that could yield different results). That causes the formula's results to be refreshed.

You can use the code below which uses the queryString variable:

function getData2() { 
  var sheetName = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Rank");
  var PricesRange = sheetName.getRange('K2:K14');
  PricesRange.clearContent();
  var queryString = Math.random();
  for (var j = 2; j <= 14; j++) {
    var functionTemplate = '=INDEX(IMPORTHTML("https://www.investing.com/currencies/xau-pln-historical-data?%s","table",1),%s,2)';
    var cellFunction2 = Utilities.format(functionTemplate, queryString, j);
    sheetName.getRange(j,11).setValue(cellFunction2);
  }
}

Additionally, you may be interested in replacing the for-loop for this single formula:

=QUERY(IMPORTHTML("https://www.investing.com/currencies/xau-pln-historical-data","table",1), "SELECT Col2 LIMIT 13 OFFSET 1", 0)

Which could be used in your code as follows:

function getData2() { 
  var sheetName = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Rank");
  var PricesRange = sheetName.getRange('K2:K14');
  PricesRange.clearContent();
  var queryString = Math.random();

  var functionTemplate = '=QUERY(IMPORTHTML("https://www.investing.com/currencies/xau-pln-historical-data?%s","table",1), "SELECT Col2 LIMIT 13 OFFSET 1", 0)';
  var cellFunction2 = Utilities.format(functionTemplate, queryString);
  sheetName.getRange(2, 11).setValue(cellFunction2);
}

You may also read more about the Utilities.formatString function in case you may need it here.