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?

Hello Hanna, has my answer helped you solve your issue? Please let me know, thanks — carlesgg97, Nov 19 '19 at 16:58

score 2 · Answer 1 · answered Nov 14 '19 at 12:03

2

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

answered Nov 14 '19 at 12:03

CodeCamper

6,609
6
44
94

What is the Threashold? Defaul is 0.05 -My Formula is : =Dollar(Index(ImportHTML("https://www.apmex.com/silver-price","table",8),2,2)) But it stays in "LOADING" – Robert Long Sep 11 '20 at 19:55
It worked for me already when A1 only contained a number and I appended the URL with &A1. Whenever I change the numer the url content is reloaded. – Sandro Feb 17 '21 at 21:00

carlesgg97 · Answer 2 · 2019-11-15T09:12:22.587

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.

Script to refresh data with IMPORTHTML in Google Sheets

2 Answers2

Linked