Periodically refresh IMPORTXML() spreadsheet function

by

I made a couple of adjustments to Mogsdad’s answer:

  • Fixed the releaseLock() call placement
  • Updates (or adds) a querystring parameter to the url in the import function (as opposed to storing, removing, waiting 5 seconds, and then restoring all relevant formulas)
  • Works on a specific sheet in your spreadsheet
  • Shows time of last update

…

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())
}

More Related Contents:

  1. Is there a way to evaluate a formula that is stored in a cell?
  2. No permission to call msgBox in Google Apps Scripting
  3. Apps Script: how to get hyperlink from a cell where there is no formula
  4. Is there a custom function or script that returns gid of a specific sheet in Google Sheets?
  5. Selecting the last value of a column
  6. Using built-in spreadsheet functions in a script
  7. Google Sheet use Importxml error could not fetch url [duplicate]
  8. TypeError: Cannot read property ‘getRange’ of null (line 6, file “Code”) in google sheet script [duplicate]
  9. How to create INDIRECT array string of multiple sheet references in Google Sheets?
  10. Refresh data retrieved by a custom function in Google Sheet
  11. Create Google Calendar Events from Spreadsheet but prevent duplicates
  12. Google Apps Script: How to set “Use column A as labels” in chart embedded in spreadsheet?
  13. Retrieving Google Sheets cell border style programmatically
  14. Determining the last row in a single column
  15. Cannot find method getRange(number,number)?
  16. You do not have permission to call openById
  17. Single Google Form for multiple Sheets [duplicate]
  18. Adding Buttons To Google Sheets and Set value to Cells on clicking
  19. How to Autofill last row with formula, when data is received from IFTTT on the last row?
  20. Custom function throws a “You do not have the permission required to setValue” error
  21. Margins parameters for Google spreadsheet export as PDF
  22. Script to convert .XLSX to Google Sheet and move converted file
  23. Hidden field in a Google Form
  24. Apply multiple font colors to the text in a single Google Sheets cell
  25. How to evaluate a spreadsheet formula within a custom function?
  26. Adding a property to an Html template gives error “Object does not allow properties to be added or changed”
  27. Use script in all spreadsheets
  28. How to pause App Scripts until spreadsheet finishes calculation
  29. Custom Functions and Recalculation
  30. Can the google spreadsheet ‘query’ function be used in google apps script?

Leave a Comment