Script To Refresh Data With Importhtml In Google Sheets

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 i

Solution 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

Solution 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:

functiongetData2() { 
  var sheetName = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Rank");
  varPricesRange = 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:

functiongetData2() { 
  var sheetName = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Rank");
  varPricesRange = 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.