Force IMPORTXML (Google Spreadsheets) to Refresh

04 May 2017

Here’s a snippet you can add to automatically refresh a cell that performs an IMPORTXML() lookup.

Key components - you need a blank cell somewhere in your Google Sheet that we can use for cache-busting.

For the example, let’s say it’s J24. Make sure J24 isn’t occupied by data. We’re going to write a random number to this field.

Next, create a cell that holds the URL you are fetching.

Important: format it like so: ="http://example.com/xmlfeed#"&J24

Note the trailing hash sign - that is just a throwaway parameter that we’ll use to refresh.

Now add the following in the script editor as a timed trigger.

/**
 * From: http://stackoverflow.com/a/33875957/1677912
 * Adapted by Davis E. Ford, May 5, 2017
 */
function RefreshImports() {
  var lock = LockService.getScriptLock();
  if (!lock.tryLock(5000)) return;             // Wait up to 5s for previous refresh to end.
  // At this point, we are holding the lock.

  var id = "YOUR_SHEET_ID";
  var ss = SpreadsheetApp.openById(id);
  var sheets = ss.getSheets();

  for (var sheetNum=0; sheetNum<sheets.length; sheetNum++) {
    var sheet = sheets[sheetNum];
    var dataRange = sheet.getDataRange();
    var formulas = dataRange.getFormulas();
    var tempFormulas = [];
    for (var row=0; row<formulas.length; row++) {
      for (col=0; col<formulas[0].length; col++) {
        // See https://regex101.com/r/bE7fJ6/2
        var re = /.*[^a-z0-9]import(?:xml|data|feed|html|range)\(.*/gi;
        if (formulas[row][col].search(re) !== -1 ) {
          // Use a blank cell here, set it to whatever you want
          sheet.getRange("J24").setValue(Math.round(Math.random()*100000));
        }
      }
    }

    // After a pause, replace the import functions
    Utilities.sleep(2000);
    for (var i=0; i<tempFormulas.length; i++) {
      var cell = tempFormulas[i];
      sheet.getRange( cell.row, cell.col ).setFormula(cell.formula)
    }

    // Done refresh; release the lock.
    lock.releaseLock();
  }
}

Google Spreadsheets will automatically update whenever a cell is updated on the sheet ;) If you set a five minute trigger, that’s the frequency your IMPORTXML() cell will update.

NOTE: I’ve added a FAQ for this script.