Auto-refresh ImportXML at predefined intervals using Time-Driven Triggers on Google Script

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 :

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