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:
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.
/edit, so in this case,
var id = '1234567890abcdefghijklmnop';.
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
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!
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.