Google Apps Script – Limiting Frequency of ‘On Open’ Script Runs

google sheetsgoogle-apps-scriptgoogle-apps-script-triggers

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.

Best Answer

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.

Unconditionally run any "run every open/reload" code you may have, plus the time checks.
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.
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.

Related Solutions

Google Sheets – Using Triggers with Parameter-Requiring Functions

How to use triggers with functions that require parameters from the spreadsheet?

AFAIK, you cannot. The solution is to create one single function, for example called RefreshAll, inside which you call all the wrapper functions (GetValue) containing the actual parameters which need to be passed to the function that actually does the job (RealFunctionThatDoesTheJob).

Note: Sorry if this sounds complicated. If you only have one set of parameters (as opposed to 3, in the example below), you can do away with GetValue and call RealFunctionThatDoesTheJob directly from within the function that is being triggered (RefreshAll).

Lastly, to have your triggers send to your spreadsheet those values that are dependent on parameters, you need to have the script push the values to the spreadsheet using (currentSheet.getRange(Row,Column).setValue(Value);)

Here is the example:

function RefreshAll() {

  var currentSheet = SpreadsheetApp.openById("ID").getSheetByName("MySheet");

  var Value1 = GetValue1();
  currentSheet.getRange(Row,Column).setValue(Value1);

  var Value2 = GetValue2();
  currentSheet.getRange(Row,Column).setValue(Value2);   

  var Value3 = GetValue3();
  currentSheet.getRange(Row,Column).setValue(Value3);

}

function GetValue1() {
  return RealFunctionThatDoesTheJob("Parameter1","Parameter2","Parameter3");
}

function GetValue2() {
  return RealFunctionThatDoesTheJob("Parameter4","Parameter5","Parameter6");
}

function GetValue3() {
  return RealFunctionThatDoesTheJob("Parameter7","Parameter8","Parameter9");
}

Google Sheets – Does IMPORTDATA() Refresh When Apps Script Runs?

The IMPORTDATA() function has a latency of approximately 1 hour, as explained in this post on the Google Product Forum.

You mentioned the fact that a script was being fired by a time based Google Apps Script trigger. I was thinking, if you're using a script in the first place, why not fetch the CSV file as well, like so:

Code

var url = 'url to public CSV file';
var text = UrlFetchApp.fetch(url).getContentText();
var csv = Utilities.parseCsv(text);

The var csv is turned into a 2d-array and can be used like any other array. This way, you always have the latest CSV file.

Best Answer

Related Solutions

Google Sheets – Using Triggers with Parameter-Requiring Functions

Google Sheets – Does IMPORTDATA() Refresh When Apps Script Runs?

Code

References

Related Topic