0

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 manually 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 returns nothing:

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!

2

The error occurs because spreadsheet functions like importHTML can't be calculated by Google Apps Script.

The Class UrlFetchApp it's able to get content from a URL. Once the script got the content, it should parse it, but there is a problem, Google Apps Script doesn't include a robust method that work with any web page.

If the web page to import is XHTML then you could try to use the Class XMLService. Another alternative is to try to use JavaScript regular expressions but HTML isn't a regular language, so this could not be easy.

Related question on Stack Oveflow: What is the best way to parse html in google apps script

  • Ugh... that doesn't sound promising. I did discover at least that my error was due to case sensitivity of my function name in the eval(). However now it simply returns nothing rather than any error. Sounds like this is a dead end... what a shame. Thanks though! – Jonathan van Clute Aug 26 '17 at 22:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.