使用IMPORTHTML刷新Google表格中的数据的脚本 [英] Script to refresh data with IMPORTHTML in Google Sheets

问题描述

我正在尝试从以下表格中获取有关XAU价格的数据:https://www.investing.com/currencies/xau-pln-historical-data

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

我需要将数据保存在Google表格中，但问题是它不会刷新数据.当我手动清理单元格并插入公式时，数据将被刷新，但是当我使用脚本执行操作时，数据将无法正常工作.我有这个脚本(基于

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.

您有什么想法吗?

推荐答案

一个简单得多的解决方案是使用1次迭代启用循环引用.例如，在单元格A1中放入 = A1 + 1 ，然后在您的网址后附加?"& A1

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

这篇关于使用IMPORTHTML刷新Google表格中的数据的脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！