Can I limit how frequently an 'on open' script runs?

Question

Basically I have a bunch of functions that run on Open, but if possible I'd like to limit them to only run once per 60 minute period. Just so that if a user accidentally refreshes or closes the sheet, opening it again won't trigger the functions.

Setting a time based trigger is not an option as the sheet isn't opened on a scheduled time by users.

Answer 1

Say I'm forgetful, but I don't want to pester my coworker by calling her more than every two hours. I might remind myself just by jotting down when I call, so I can glance at that note next time I'm once again reaching for the phone.

A script could do the same, scribbling down when it needs to run again, and checking it later.

function onOpen() {
  var frequency = 60 * (60 * 1000); // minimum milliseconds between timed-script runs

  // YOUR "every onOpen, ignoring times" CODE HERE
  Logger.log("Running \"every onOpen\" code.");  

  var due = PropertiesService.getScriptProperties().getProperty('nextOnOpenRun');
  if (due == null || new Date().getTime() > due) {

    // YOUR "ONLY GREATER THAN HOURLY" CODE HERE
    Logger.log("Running \"hourly+ onOpen\" code.");

    // set up the next run, 60 minutes from now
    var later = new Date().getTime() + frequency;
    PropertiesService.getScriptProperties().setProperty('nextOnOpenRun', later);
  } else {

    // YOUR "onOpen within an hour later" CODE HERE
    Logger.log("Running \"<hourly onOpen\" code.");

  }      
}

Right now I've set the frequency to your mentioned one hour. Apps Script times are in thousandths of a second, so leave on the minutes-conversion, "* (60*1000)" part if you ever change the timing.

You'll see three "comment plus Logger call" pairs. They themselves are there for clarity, or your logging/testing, and could naturally be removed. Their position in the function and ifs is all that matters. Arranged this way, at every onOpen the time is checked and three blocks of code are conditionally run.

1. Unconditionally run any "run every open/reload" code you may have, plus the time checks.
2. If the script hasn't ever stored the time or if the current time is after our hour timestamp, run the infrequent code your question is really about. Importantly, also store a timestamp for "an hour from now" into a scriptProperty so we can check it in future onOpen runs.
3. If the time hasn't elapsed, run any "opened or reloaded early" code. I suppose this would be a rarer need, but didn't want to assume.