Running a Google Apps Script function every quarter or x months

One of the great things about Google Apps Script is the way you can automate tasks. I’ve previously written about how I automate reporting and other examples like running backup processes. These usually run daily or once a month which is very straight forward to setup in Google Apps Script. If you want to run a script automatically every x number of months such as quarterly it gets a little harder. If you use the project trigger interface in the Script Editor to manage triggers manually you’ll see the longest repeating period you can set is one month. 

image

 If you only want you script to run every three months you could handle with a condition e.g. storing the last run date as a script property and doing a date calculation. Another option is to manage triggers programmatically which allows you to specify the date a function should be run again. For example, if you wanted to run a myFunction every quarter you could use the script below:

function myFunction() {
  // setup for it to do again in x months
  // from http://stackoverflow.com/a/2706169/1027723
  // note won't work in edge cases e.g. adding to the 31st day of most months
  // http://stackoverflow.com/questions/2706125/javascript-function-to-add-x-months-to-a-date#comment9085189_2706169
  var x = 3; // months - change to whatever offset
  var triggerDay = new Date();
  triggerDay.setMonth(triggerDay.getMonth() + x);
  // https://developers.google.com/apps-script/reference/script/clock-trigger-builder#at(Date)
  ScriptApp.newTrigger("myFunction")
    .timeBased()
    .at(triggerDay)
    .create();
  // do something now (this will also keep being done every x months)
  SpreadsheetApp.getActiveSheet().getRange('A2:B2').setValues([
    [new Date(), triggerDay]
  ]);
}

To use this script you’d manually run it once or use the project triggers to run the script at a specific date/time and after that it would, in this case rerun every 3 months. As noted in the script some limitations to be aware of, the biggest one is if the script is run on the 31st or a month as the next date calculation could be out.  So now you don’t need to worry about sending that quarterly report on time … you could just Google Apps Script it!

chevron_left
chevron_right
css.php