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

问题描述

我有一个包含大约 30 个 importxml 函数的大表，这些函数从通常每天更新两次的网站获取数据.

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

我想及时(每 8 小时)为我的 Google 电子表格运行 importxml 函数以将数据保存在另一个工作表中.保存已经有效，但更新无效！

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!

我在 Google 电子表格行更新中读到它可能每 2 小时运行一次，但是我不要相信这是真的，因为自从我将它添加到我的工作表后，没有任何更改或更新，当电子表格未打开时.

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.

如何在我的 Google 电子表格中以简单的方式触发"importxml 函数，因为其中有很多 importxml 函数?

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?

推荐答案

Google 电子表格行更新 问题及其答案涉及旧表格"，其行为与 2015 版 Google 表格不同.新表格"没有内容自动刷新；现在仅在响应编辑时评估更改.

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.

虽然 Sheets 本身不再提供此功能，但我们可以使用脚本来刷新导入"公式(IMPORTXML、IMPORTDATA、IMPORTHTML> 和 IMPORTANGE).

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

要定期刷新 IMPORT 公式，请将此函数设置为 时间驱动触发器.

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

注意事项:

• 导入函数 其他脚本或用户在刷新期间对电子表格所做的公式更改可能会被覆盖.
• 重叠刷新可能会使您的电子表格不稳定.为了缓解这种情况，实用程序脚本使用 ScriptLock.这可能会与脚本中该锁的其他用途发生冲突.

 

/**
 * 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屋！