手动importhtml()？

Question

我厌倦了每次对我的工作表进行更改时重新触发我的importHTML函数，所以我希望手动实现这个函数，并将返回的数据存储到下次该函数被手动调用时。

我之前问过如何用importJSON()和那个解决方案工作得很好，但是我还不能修改它来处理importhtml()。下面是我目前没有返回任何内容的代码：

function mImportHTML(url, query, index) {
  return "Imported data below this cell";
}

function updateImportHTML() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var formulas = range.getFormulas();
  for (var i = 0; i < formulas.length; i++) {
    for (var j = 0; j < formulas[0].length; j++) {
      if (/^=mImportHTML/i.test(formulas[i][j])) {
        var data = eval("ImportHTML" + formulas[i][j].slice(12));
        sheet.getRange(i + 2, j + 1, data.length, data[0].length).setValues(data);
      }
    }
  }
}

mImportHTML()只是“手动ImportHTML”。

我在工作表中的调用示例是=mImportHTML("http://www.apmex.com/category/50000/other-items","table",1)

不幸的是，我对JS不是很好，所以我还没有弄清楚这是为什么失败的。在我看来，它应该像importJSON()代码一样工作，但是由于某些原因，它没有工作。

Answer 1

出现此错误是因为Google脚本无法计算像importHTML这样的电子表格函数。

能够从类UrlFetchApp获取内容的URL。一旦脚本获得内容，它应该解析它，但有一个问题，谷歌应用程序脚本没有包括一个健壮的方法，与任何网页工作。

如果要导入的网页是XHTML，那么您可以尝试使用类XMLService。另一种选择是尝试使用JavaScript正则表达式，但是HTML不是一种常规语言，所以这不容易。

关于堆栈Oveflow的相关问题：在google应用程序脚本中解析html的最佳方法是什么？

Answer 2

我一直面临着同样的问题，并开发了以下Google脚本作为解决方案：

脚本：

// inspired by: https://webapps.stackexchange.com/questions/90908/importxml-data-reloading-every-time-i-open-the-sheet

function onOpen() {
  var menu = [
    {name: "Update imported data", functionName: "update_imported_data"},
    {name: "Disable Formulas", functionName: "disable_formulas"},
    {name: "Enable Formulas", functionName: "enable_formulas"}
  ];
  SpreadsheetApp.getActiveSpreadsheet().addMenu("Custom", menu);
}

function update_imported_data() {
  var currentSheet = SpreadsheetApp.getActiveSheet();
  var currentSheetName = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();

  // if (currentSheetName.indexOf("-formula")>-1) {
  if (/-formula/i.test(currentSheetName)) {
    var formulaSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var dataSheetName = currentSheetName.split(new RegExp("-formula",'i'))[0];
    var dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dataSheetName);
  } else {
    var dataSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var formulaSheetName = currentSheetName + "-formula";
    var formulaSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(formulaSheetName);
  }

  var rangeImportHtml = formulaSheet.getRange(1,2, formulaSheet.getLastRow(),1);
  enable_formulas(formulaSheet)
  SpreadsheetApp.flush();
  var rangeFormula = formulaSheet.getDataRange().offset(1, 1);
  var rangeData = dataSheet.getDataRange().offset(1, 1);
  rangeFormula.copyTo(rangeData, {contentsOnly: true});
  SpreadsheetApp.flush();
  disable_formulas(formulaSheet);
}

function disable_formulas (formulaSheet) {
  // var sheet = SpreadsheetApp.getActiveSheet();
  if (typeof formulaSheet == 'undefined') {var formulaSheet = SpreadsheetApp.getActiveSheet();}
  var range = formulaSheet.getDataRange();
  var textFinder = range.offset(1,0).createTextFinder("^=").useRegularExpression(true).matchFormulaText(true);
  textFinder.replaceAllWith("`=");
}

function enable_formulas (formulaSheet) {
  // var sheet = SpreadsheetApp.getActiveSheet();
  if (typeof formulaSheet == 'undefined') {var formulaSheet = SpreadsheetApp.getActiveSheet();}
  var range = formulaSheet.getDataRange();
  var textFinder = range.offset(1,0).createTextFinder("^`=").useRegularExpression(true).matchFormulaText(true);
  textFinder.replaceAllWith("=");
}

选择菜单项扩展> Apps脚本并粘贴到那里。

解释

脚本的主要功能是在将所选工作表中的所有公式复制到另一个工作表后基本上禁用它们，从而生成静态数据，这些数据可以通过重新运行脚本来更新。

它将在菜单栏上创建一个自定义菜单，其函数为：

• 更新导入的数据。
• 禁用公式。
• 启用公式。

，下面是您必须做的事情：

• 编写公式(包括: ImportHTML、ImportJSON等)如往常一样在纸上(如Sheet1)照常。这是存储静态数据的工作表，即“数据表”。
• 复制工作表并更改新工作表的名称以匹配原始名称并添加-formula。现在的名字是Sheet1-formula。这是存储(禁用的)公式的工作表，即“公式表”。
• 在任何一个工作表(“数据表”或“公式表”)中运行函数Update imported data，脚本将将在“公式表”中生成的数据复制到“数据表”中，然后通过添加`禁用这些公式。

，您可以在任何一个工作表中再次运行该函数来更新数据。它将自动启用公式，复制其结果，并重新禁用公式.

其他两个函数，Disable Formulas & Enable Formulas用于手动禁用和启用活动工作表中的公式，而不管工作表的名称或内容如何。