Google Sheet IMPORTHTML VERY Slow?

Question

I have a spreadsheet that acquires some table data using the IMPORTHTML function, and for the first two days I was using it (refreshing twice daily) things were going fine. As of this morning, it is absolutely crawling. Went from taking ~15 seconds to load 30 rows to taking ~10 minutes. Can somebody lend aid on this?

Example formula:

=IMPORTHTML(
  "http://www.muthead.com/16/players/prices/1508-markus-wheaton/playstation-4","table",2
 )

As mentioned, the first couple of days it was able to refresh and process a list of 30 without any pauses. Now I get the 'Executing script' message for about ten minutes before it begins to do anything, and I haven't touched the source code since origin. I'm not sure what contributes to the performance of the IMPORTHTML statement...

Is the response time on the IMPORTHTML statement dependent on current traffic to the domain? — KJR
Do you know that import are refreshed a certain time intervals? How do you refresh and process the list? — Rubén

Chef1075 Chef1075 · Accepted Answer · 2018-07-14T16:35:24

I've run into similar loading issues when using IMPORTHTML, IMPORTDATA, etc. The best solution I've found is to write a trigger that will edit your formula so it is forced to refresh every hour or so.

Open up the script editor and put this in. Change 'A1' with the cell your IMPORTHTML function is in, and change foo to the URL you're trying to import.

function refreshData() {
   var range = SpreadsheetApp.getActiveSpreadsheet().getRange('A1');
   range.clear();
   range.setFormula('=IMPORTHTML(foo)');
}

Then go to Edit > Current Project Trigger > Add Trigger, and set a refresh interval.

Hope this helps.

Google Sheet IMPORTHTML VERY Slow?

1 Answers