定期刷新IMPORTXML（）电子表格函数 [英] Periodically refresh IMPORTXML() spreadsheet function

问题描述

我有一个大型的工作表，大约有30 importxml 函数，这些函数从一个网站获取数据，每天更新两次。

我想为我的Google Spreadsheet及时（每8小时）运行 importxml 函数以将数据保存到另一个工作表中。保存已经有效，但是更新不会！

我读过谷歌电子表格行更新，它可能每2小时运行一次，但我不相信这是真的，因为我将它添加到我的工作表没有任何更改或更新，当电子表格不是打开。

如何以简单的方式触发Google电子表格中的 importxml 函数，就像我有很多 importxml 函数在里面？

解决方案

谷歌电子表格行更新问题及其答案是指旧表格，它有不同于Google表格2015年的版本。 新表格不会自动刷新内容;

尽管表格本身不再提供此功能，但我们可以使用脚本来刷新导入公式（ IMPORTXML ， IMPORTDATA ， IMPORTHTML 和 IMPORTANGE ）。

工具脚本

刷新 IMPORT 公式，将此函数设置为时间驱动的触发器。

注意事项：

• 导入函数刷新期间其他脚本或用户对电子表格进行的公式更改可能被覆盖。


• 重叠刷新可能会导致电子表格不稳定。为了缓解这种情况，实用程序脚本使用 ScriptLock 。这可能与您的脚本中该锁的其他用途相冲突。
  

 

/ **
通过电子表格中的所有工作表，识别并移除所有电子表格
*导入函数，然后替换他们过了一段时间。这会导致刷新
*的导入功能。要定期刷新这些公式，请将此
*函数设置为基于时间的触发器。
*
*注意：更新期间其他脚本或用户
*对电子表格所做的公式更改可能会被覆盖。
*
* From：https://stackoverflow.com/a/33875957/1677912
* /
函数RefreshImports（）{
var lock = LockService.getScriptLock （）;
if（！lock.tryLock（5000））return; //等待5秒钟以前的刷新结束。
//此时，我们持有锁。

var id =YOUR-SHEET-ID;
var ss = SpreadsheetApp.openById（id）;
var sheets = ss.getSheets（）; （var sheetNum = 0; sheetNum< sheets.length; sheetNum ++）{
var sheet = sheets [sheetNum];

;
var dataRange = sheet.getDataRange（）;
var formula = dataRange.getFormulas（）;
var tempFormulas = [];对于（col = 0; col< formula [0] .length; col ++）{
//空白全部（$）;
for（var row = 0; row< formulas.length; row ++）{
包含任何导入函数的公式
//见https://regex101.com/r/bE7fJ6/2
var re = /.*[^a-z0-9]import(?:xml （公式[row] [col] .search（re）！== -1）{
tempFormulas.push（ {row：row + 1，
col：col + 1，
formula：formula [row] [col]}）;
sheet.getRange（row + 1，col + 1）。 setFormula（）;
}
}
}

//暂停后，替换导入函数
Utilities.sleep（5000）;
for（var i = 0; i< tempFormulas.length; i ++）{
var cell = tempFormulas [i];
sheet.getRange（cell.row，cell.col）.setFormula （cell.formula）
}

//完成刷新;释放锁
lock.releaseLock（）;
}
}


I have a large sheet with around 30 importxml functions that obtain data from a website that updates usually twice a day.

I would like to run the importxml function on a timely basis (every 8 hours) for my Google Spreadsheet to save the data in another sheet. The saving already works, however the updating does not!

I read in Google Spreadsheet row update that it might run every 2 hours, however I do not believe that this is true, because since I added it to my sheet nothing has changed or updated, when the spreadsheet is NOT opened.

How can I "trigger" the importxml function in my Google Spreadsheet in an easy way, as I have a lot of importxml functions in it?

解决方案

The Google Spreadsheet row update question and its answers refer to the "Old Sheets", which had different behaviour than the 2015 version of Google Sheets does. There is no automatic refresh of content with "New Sheets"; changes are only evaluated now in response to edits.

While Sheets no longer provides this capability natively, we can use a script to refresh the "import" formulas (IMPORTXML, IMPORTDATA, IMPORTHTML and IMPORTANGE).

Utility script

For periodic refresh of IMPORT formulas, set this function up as a time-driven trigger.

Caveats:

• Import function Formula changes made to the spreadsheet by other scripts or users during the refresh period COULD BE OVERWRITTEN.
• Overlapping refreshes might make your spreadsheet unstable. To mitigate that, the utility script uses a ScriptLock. This may conflict with other uses of that lock in your script.

 

/**
 * Go through all sheets in a spreadsheet, identify and remove all spreadsheet
 * import functions, then replace them a while later. This causes a "refresh"
 * of the "import" functions. For periodic refresh of these formulas, set this
 * function up as a time-based trigger.
 *
 * Caution: Formula changes made to the spreadsheet by other scripts or users
 * during the refresh period COULD BE OVERWRITTEN.
 *
 * From: https://stackoverflow.com/a/33875957/1677912
 */
function RefreshImports() {
  var lock = LockService.getScriptLock();
  if (!lock.tryLock(5000)) return;             // Wait up to 5s for previous refresh to end.
  // At this point, we are holding the lock.

  var id = "YOUR-SHEET-ID";
  var ss = SpreadsheetApp.openById(id);
  var sheets = ss.getSheets();

  for (var sheetNum=0; sheetNum<sheets.length; sheetNum++) {
    var sheet = sheets[sheetNum];
    var dataRange = sheet.getDataRange();
    var formulas = dataRange.getFormulas();
    var tempFormulas = [];
    for (var row=0; row<formulas.length; row++) {
      for (col=0; col<formulas[0].length; col++) {
        // Blank all formulas containing any "import" function
        // See https://regex101.com/r/bE7fJ6/2
        var re = /.*[^a-z0-9]import(?:xml|data|feed|html|range)\(.*/gi;
        if (formulas[row][col].search(re) !== -1 ) {
          tempFormulas.push({row:row+1,
                             col:col+1,
                             formula:formulas[row][col]});
          sheet.getRange(row+1, col+1).setFormula("");
        }
      }
    }

    // After a pause, replace the import functions
    Utilities.sleep(5000);
    for (var i=0; i<tempFormulas.length; i++) {
      var cell = tempFormulas[i];
      sheet.getRange( cell.row, cell.col ).setFormula(cell.formula)
    }

    // Done refresh; release the lock.
    lock.releaseLock();
  }
}

这篇关于定期刷新IMPORTXML（）电子表格函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！