How to create 'Time-driven Triggers' in Google Script
Functions that pull data from outside the spreadsheet recalculate at the following times:
● ImportRange: 30 minutes
● ImportHtml, ImportFeed, ImportData, ImportXml: 1 hour
● GoogleFinance: may be delayed up to 20 minutes
See more details here.
Time-driven triggers is a very helpful tool for getting periodic updates of [import] functions used in google spreadsheets. There are two ways to do it. First script uses a random number added to the target page as an URL query string. It by passes the one hour limit by calling a dummy URL every time at auto-recalculation of google spreadsheet. Since target values are populated directly through scripted formulas, it is tedious to manipulate a large number of function through with it.
Lets see, how can we set-up a Time-driven triggers in script editor for import functions at a time interval as low as 'on every minute'. It would be best to use it for upto 5-10 import function, but if you handle more , than go ahead as there is no restriction as such except script may take a little longer to update.
Make sure all you have is a list of URLs and Xpath_query that you want to get output to values in output Cells after each auto-update. if you need any help on [Xpath query] generation see related article or video blog at :
Related : Use Xpath query to fetch data from a Webpage
Just copy and paste the simple code in google script editor :
Just copy and paste the simple code in google script editor :
function getData() {
var queryString = Math.random();
var Xpath_1 = "[Your Xpath_query here]";
var importXpath_1 = '=IMPORTXML("' + '[Your URL link here]' + '?' + queryString + '","'+ Xpath_1 + '")';
SpreadsheetApp.getActiveSheet().getRange('[outputCell]').setValue(importXpath_1);
}
Replace desired values at <[Your Xpath_query here]>, <[Your URL link here]> and <[outputCell]> in the above mentioned code. save project and allow permissions to 'Run' the script in your gmail account.
Related : Auto-updated Equity Stocks Portfolio in Google Sheets
Utility script for periodic refresh of IMPORT formulas
Above said basic script works well, if you want to populate just a few import functions. It not easy to manipulate a long list of import functions inside a spreadsheet. I explored the web for this excellent solution that works great. Thank you all contributors to share such a wonderful script.Here is the script, :
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())
}
Resources :
1. Periodically refresh importxml() spreadsheet function
