0

I have two scripts:

  • One which adds an IMPORTJSON sheets function from bradjasper, works great.

  • The second one is that I want to refresh the scripts automatically, that does not work. The script does work when I ran it manually, and also the trigger does work according to the logs, but the data does not get refreshed.

I have tried all the different scripts, they do work, but the data imported on the sheet is not actually updated.

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

  var id = "1Vt-rqQZ7iXsui8Nrr2XABusd4lUbGqxj4HkzsRkZFNA";
  var ss = SpreadsheetApp.openById(id);
  var sheet = ss.getSheetByName("Blad2");
  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|json|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(1,1).setValue("Rates were last updated at " + now.toLocaleTimeString())
}

This script does run, as there are no errors in the logs. However, the data shown on the sheet does not change to reflect the current information from the API / JSON file.

sinaraheneba
  • 781
  • 4
  • 18
Rick
  • 3
  • 5
  • How often is the trigger set to run? Have you tried using `console.log()` to see how far through execution the script gets==e.g., possibly stuck on lock? what value is `updatedContent`? error with the loop logic? (that is, you are using `setFormula()` **inside** a loop, rather than collecting your results in an array and using `setFormulas()` after; could your loop be interacting with the values it is setting?) – sinaraheneba Aug 07 '19 at 07:22
  • To be real honest, I'm totally new to this. can i send you a dm? My trigger is set to run every minute - How do i add console.log()? – Rick Aug 07 '19 at 07:24
  • Try adding `SpreadsheetApp.flush()` somewhere in your script. – TheMaster Aug 07 '19 at 07:31
  • Seeing the Stack Overflow guide on [How to Ask](https://stackoverflow.com/help/how-to-ask) might help you in improving your question (and solving it yourself). For information on issues specific to Google Apps Script, see Google's [documentation](https://developers.google.com/apps-script/reference/base/console). For general information on Javascript, other answers and Mozilla's MDN are very helpful. – sinaraheneba Aug 07 '19 at 07:32
  • Looking at your script again, I realize when you referred to a first function, you meant a sheets function, not a Google Apps Script function. As results are generally cached by Google, they are not automatically updated--this can be fairly annoying to work around, see [for example.](https://stackoverflow.com/questions/17341399/refresh-data-retrieved-by-a-custom-function-in-google-sheet) – sinaraheneba Aug 07 '19 at 07:33
  • Yes, that describes exactly my problem. But I do not get it working, unfortunately. – Rick Aug 07 '19 at 07:46
  • Threre is an Issue about this in [Google's Issue Tracker](https://issuetracker.google.com/issues/36753882) which details the problems you're encountering. You can keep up-to-date with how it is going by giving the issue a star in the top left of the page. – Rafa Guillermo Aug 08 '19 at 09:14

0 Answers0