I have a large sheet with around 30 importxml
functions that obtain data from a website that updates usually twice a day.
I would like to run the importxml
function on a timely basis (every 8 hours) for my Google Spreadsheet to save the data in another sheet. The saving already works, however the updating does not!
I read in Google Spreadsheet row update that it might run every 2 hours, however I do not believe that this is true, because since I added it to my sheet nothing has changed or updated, when the spreadsheet is NOT opened.
How can I "trigger" the importxml
function in my Google Spreadsheet in an easy way, as I have a lot of importxml
functions in it?
I made a couple of adjustments to Mogsdad's answer:
releaseLock()
call placement...
function RefreshImports() {
var lock = LockService.getScriptLock();
if (!lock.tryLock(5000)) return; // Wait up to 5s for previous refresh to end.
var id = "[YOUR SPREADSHEET ID]";
var ss = SpreadsheetApp.openById(id);
var sheet = ss.getSheetByName("[SHEET NAME]");
var dataRange = sheet.getDataRange();
var formulas = dataRange.getFormulas();
var content = "";
var now = new Date();
var time = now.getTime();
var re = /.*[^a-z0-9]import(?:xml|data|feed|html|range)\(.*/gi;
var re2 = /((\?|&)(update=[0-9]*))/gi;
var re3 = /(",)/gi;
for (var row=0; row<formulas.length; row++) {
for (var col=0; col<formulas[0].length; col++) {
content = formulas[row][col];
if (content != "") {
var match = content.search(re);
if (match !== -1 ) {
// import function is used in this cell
var updatedContent = content.toString().replace(re2,"$2update=" + time);
if (updatedContent == content) {
// No querystring exists yet in url
updatedContent = content.toString().replace(re3,"?update=" + time + "$1");
}
// Update url in formula with querystring param
sheet.getRange(row+1, col+1).setFormula(updatedContent);
}
}
}
}
// Done refresh; release the lock.
lock.releaseLock();
// Show last updated time on sheet somewhere
sheet.getRange(7,2).setValue("Rates were last updated at " + now.toLocaleTimeString())
}