Manual importhtml()?

by Jonathan van Clute   Last Updated August 26, 2017 20:03 PM

I'm tired of Google Sheets re-triggering my importHTML functions every time I make a change to my sheet, so I would like to make that function happen manually, and store the returned data until the next time the function is called.

I previously asked about how to do this with importJSON() and that solution works well, but I haven't been able to modify it to work with importhtml(). Here is the code I have at the moment that fails with ReferenceError: "importhtml" is not defined.:

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() is just "Manual ImportHTML".

My sample call in Sheets is =mImportHTML("http://www.apmex.com/category/50000/other-items","table",1)

Unfortunately I'm not very good with JS so I haven't been able to figure out why this is failing. It seems to me that it should work just like the importJSON() code but for some reason it doesn't. I'd appreciate any help in getting this going!



Related Questions



Retrieve last entry via ImportXML

Updated April 06, 2015 17:01 PM