Auto Refreshing ImportXML Google Sheets

Question

I have a sheet that imports data from a site, there are about 10 imports all slightly different. I want a way that I could update that every five minutes or so. I know there are scrips to do it but when I tried to use one it would just put ?update at the end and mess up the entire import. I can show the script I am using. It is from 4 years ago and maybe it is outdated. Any help would be appreciated.

Also just having a script that changes the = to nothing then adds it back again or something would work I guess.

Here is the script I am using

    function RefreshImports() {
  var lock = LockService.getScriptLock();
  if (!lock.tryLock(5000)) return;             // Wait up to 5s for previous refresh to end.

  var id = "Sheets 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(12,2).setValue("Rates were last updated at " + now.toLocaleTimeString())
}

ziganotschka ziganotschka · Accepted Answer · 2020-05-18T14:48:30

You can write a script that retrieves the formula from the specified cell and sets it back to the same cell on a timer

For this you need:

Retrieve the formula with getFormula()
Set the formula back into the same cell with getFormula()
Bind an installable time-driven trigger to your function specifying the desired interval

Sample:

function setMeOnTrigger() {
  var sheet = SpreadsheetApp.getActive().getActiveSheet();
  var cell = sheet.getRange("C1");
  cell.setFormula(cell.getFormula())
}

Auto Refreshing ImportXML Google Sheets

1 Answers

You can write a script that retrieves the formula from the specified cell and sets it back to the same cell on a timer