IMPORTXML (Google Sheets) FAQ and Optimization

28 Jun 2017

Update: 10-27-2020

I got an email from a reader Ryan, who wrote the following:

Hey Davis. I ran across an old May 2017 blog you wrote about refreshing Google Sheets imports. It was hugely helpful and I want to thank you.

I think Google Sheets may have changed since your post and now IMPORTXML doesn't refresh with any changed cell.

I found the following changes worked:

I changed all my references for IMPORTXML to the cell holding the updated URL with the #{random number}
I deleted all the code that tries to remove and recreate the formulas
I created a 5 minute trigger that runs the function to update the cell with the random number.

Thanks so much for your contribution!

I haven’t personally tested this, but I can confirm that my old IMPORTXML statements no longer work. Give this a try!

NOTE: The below doesn’t necessarily apply anymore (as of late 2020)!

I got an email from a reader John who had some issues running the IMPORTXML script that I’d posted about in May.

A couple of clarifying pieces of information:

How do I get the Google Sheet ID?
  • The var id = '' requires your Google Sheet ID. The easiest way to get this ID is to look at the the URL of your Google Sheet.
  • Example: https://docs.google.com/spreadsheets/d/1234567890abcdefghijklmnop/edit#gid=1957285
  • The id is the hashed value between d/ and /edit, so in this case, var id = '1234567890abcdefghijklmnop';.
I keep getting the “Service using too much computer time for day” email error. Any way around this? Is it possible to have the script run on only one particular page rather than every page in the sheet? I wonder if that might help. Or another way?

Are you sure you really need to search each sheet, row, and column for the existence of IMPORTXML statements, and then, and only then, update a cell every five minutes?

Would the script run just as well if you just update a cell every five minutes regardless of conditions?

You really just want a script that updates the number after the hash #, e.g. cell E2 in "http://example.com/xmlfeed#"&E2.

I recommend that you make one new sheet (make it the last sheet in the whole workbook) that will contain all of your IMPORTXML statements. If you’re doing cryptocurrency stuff like most people using the script, name the sheet Prices, and that’s where all of your other sheets can reference values.

What I do is just have a page where all of the links have their # number pointed at cell E2. Then my script looks like the following. Remember that IMPORTXML runs every time you change the hash number, so all we really need is a script that updates one cell every few minutes. We don’t need to search for IMPORTXML and waste computing time when we know that we definitely want this cell updated!

function RefreshImports() {
  var lock = LockService.getScriptLock();
  if (!lock.tryLock(5000)) return;

  var id = "your_google_sheet_id";
  var ss = SpreadsheetApp.openById(id);
  var sheets = ss.getSheets();
  // Get the last sheet
  var sheet = sheets[sheets.length - 1];
  // Use a blank cell here, set it to whatever you want
  sheet.getRange("E2").setValue(Math.round(Math.random() * 100000));

  lock.releaseLock();
}

Using a one-page approach will condense your IMPORTXML statements, making them easier to find, and it’ll help avoid the “Service using too much computer time for day” issue, since it’ll require next to zero computation time to run this script.

Related Posts