Google Apps Scripts: Create Time Triggers to automatically send email task reminders from a Google Sheets check list

Google Apps Script: Time Triggers, ClockTriggerBuilder, ScriptApp

One of the most powerful things about Google Apps Script is the ability to automatically complete tasks with time-driven triggers. You can set up your code to run any one of your functions in your script by the minute, hour, day, week, or month. You can even set it to run based on a specific date.

Have a look at the time-driven trigger options below:

- Specific Date and Time: Based on: YYYY-MM-DD HH: MM format
- Minute: Every minute or every 5, 10, 15 or 30 minutes.
- Hour: Every hour or every 2, 4, 6, 8 or 12 hours.
- Day: At a time within an hourly bracket. For example:
  - Midnight to 1 am,
  - 3 pm to 4 pm
Month: On a specific day of a calendar month at a specific time. For example:
- - Every 3 day of the month at between 3 am and 4 am.

There are two approaches to setting up these time-based triggers. The first one is by simply using the Trigger creator inside the G Suite Developer Hub. This allows you to select the function that you want to add a trigger to for your project and fill out a simple form to determine when you want your trigger to run.

Alternatively, you can create a time trigger programmatically using the ClockTriggerBuilder class.

In this tutorial, we will build on a previous project we’ve called The Badger, that contains a task check off Google Sheet. If the user does not check off their sheet by the due date, then we can send them an HTML email reminder.

Don’t worry, I will provide the starter code and the Google Sheet for you in a minute. However, if you want to learn how I created the email reminder for The Badger you can follow the tutorial here:

Google Apps Script: Create an HTML email reminder service from Google Sheet data.

If you want to see how I created the Spreadsheet, you can check out this link:

Google Sheets: How to create a task completion check sheet.

Setting Up

To get started, click on the link to The Badger Google Sheet. Got to:

File > Make a copy

You can then open a copy of the starter code by going to:

Tools > Script editor

You will also have to comment out line 73:

//var complete = openCompleteWindow(badEmailList);

Run for the first time

You will need to run the code for the first time and accept all the permissions.

Before you do this I recommend that you change one of the dummy email addresses for a user who hasn’t completed the task in The Badger sheet to your own. Try, say Benjamin Lee in cell B8. This will help you to see if the email work when you test it.

Make a copy of your sheet id; it’s in the URL of your copy of The Badger. Next, go into your Google Apps Script editor and update the ss_id global variable with your new sheet ID.

Back in the Google Sheet, you might have noticed that there is a custom menu to the right of the Help menu called Reminders. Click Reminders > Send reminders.

🐐You can support me for free by using this Amazon affiliate link in your next tech purchase :Computers & Stuff! 🐐

The first time you do this it will prompt you with an authorization request.

Hit continue then chose your account and follow the prompts to allow the script to run.

Time-Based Triggers using the GSuite Developer Hub

For our Badger project, we want to be able to send a daily email to any user who has not yet completed their assigned task and checked the tick-box.

We can set our script up to send out reminder emails daily. Our main() function checks for any user who has not ticked their tick-box and sends a custom reminder email to them. We will want to run our main() function daily first thing in the morning so that it is the first email that our users get.

To do this, we need to go to our Script Editor and select:

Edit > Current project’s triggers

This will load the GSuite Developer Hub. In the bottom right, click the Add trigger button.

A pop-up form window will appear. On the left is a list of options to build your trigger. On the right, you have an option for failure notifications (no need to change this). For our purpose, here is what we will change and keep the same on the left-hand side:

Choose which function to run: If it isn’t on main change it. This is our main() function.
Choose which deployment should run: Keep this as, set to head.
Select event source: We want a time-driven event here.
Select type of time-based trigger: Set to Day timer.
Select time of day: Let’s go with 6 am to 7 am. Remember, that some of your users might have their phone notifications on so you don’t want to wake them up.
Check the time zone matches the timezone you are working in.

Hit Save.

You will now see in your trigger menu one new item.

Your trigger will now run daily at the set time.

This saves us some time and bother by not having to go in an run the script every time we want to send users an email, but we still have to make note of when to turn the trigger off. We also need to be vigilant about when to start the trigger. In our example, this is the first day after our deadline (cell C2 of the sheet). This is where the ClockTriggerBuilder Class kicks in.

Time-Based Triggers using the ClockTriggerBuilder

What if we want to just be able to just run a set and forget time trigger? That means we can start the trigger before the deadline and it won’t send emails until after. Further, we want the time trigger to be able to turn off automatically with us not having to worry about it.

The Google Apps Script ClockTriggerBuilder class enables us to programmatically create a time trigger. The ClockTriggerBuilder has all of the same options as the time-based trigger we discussed above, but we simply add our trigger information to our Google Apps Script with code. We will also be able to remove the trigger once a certain condition is met with the ScriptApp.deleteTrigger(trigger) method.

Back to our example

In our example based on The Badger Google Sheet, we want to be able to initialise the time trigger with a link in our custom menu item under Reminders.

We want to set this trigger any time before or after the scheduled task deadline at our convenience. If we set the trigger before the deadline, we don’t want it to send reminders until after the deadline.

Also, we want to set an expiry to our time trigger so if we forget to turn off the trigger it will be done automatically. There is a limit to how many time triggers we can use in all our scripts so it is helpful to not be wasteful.

Let’s get started modifying our current code to achieve these new goals.

TimeTrigger.gs

First, we are going to create a new Google Script file to put our time trigger script in. We will do this for easy reading.

Got to File > New > Script file.

Rename that file and call it: TimeTrigger

Delete the dummy myFunction. We are going to add two functions in this file.

The good thing about Google Apps Script is that you don’t have to import from other *.gs files. Any function in any file will be available for use.

startTimeTrigger()

function startTimeTrigger() {

  ScriptApp.newTrigger('main')
           .timeBased()
           .atHour(7)
           .everDays(1)
           .create();

};

function startTimeTrigger() {

ScriptApp.newTrigger('main')

.timeBased()

.atHour(7)

.everDays(1)

.create();

};

As the name suggests, startTimeTrigger() will start the time-based trigger for our script. To create this we need to make use of the ScriptApp class. On line 3 we declare a new trigger and point to what function will be running when the trigger’s criteria is met. In our case, we want our trigger to run the main() function. Note that the function name is in single quotation marks and does not take any parameters.

There are a number of triggers you can create, but we want to use the ClockTriggerBuilder. To get this we state that our new trigger is timeBased() (line 4).

Next, we need to decide when we want to run our trigger. We want to run our trigger daily at around 7 am. This means that we first need to set the time – in 24 hr time – when we want to run the trigger and how many days apart we want to run it (lines 5 and 6).

Finally, we need to create the trigger (line 7).

Now, when this trigger is run it will prepare to run the main code at 7 am every day.

cancelTimeTrigger()

function cancelTimeTrigger(){
  
  var triggers = ScriptApp.getProjectTriggers();
  
  for(var i = 0; i < triggers.length; i++){
    if(triggers[i].getTriggerSource() == ScriptApp.TriggerSource.CLOCK){
      ScriptApp.deleteTrigger(triggers[i]);
    };
  };
};

function cancelTimeTrigger(){

var triggers = ScriptApp.getProjectTriggers();

for(var i = 0; i < triggers.length; i++){

if(triggers[i].getTriggerSource() == ScriptApp.TriggerSource.CLOCK){

ScriptApp.deleteTrigger(triggers[i]);

};

One of our requirements is to also be able to stop the time trigger. We will create the cancelTimeTrigger() function for this purpose. We will call this function in the main() function should we reach our designated expiry date (more on this in a bit). We also want to cancel the time trigger from the custom Reminders menu in the Google Sheet.

To create the function, we first need to find our time trigger. We do this by looking up all the triggers in our project (line 3). This will give us an array of triggers in our script. To further refine our search, we only want to look for our time-based or ‘CLOCK’ event source triggers, so we will iterate through each trigger looking for this parameter (lines 5-6).

Because we know that we only have one ‘CLOCK’ trigger, we can happily delete any trigger with this criterion (Line 7).

Add these two scripts to your TimeTriggers.gs file. We are done with this file and will now move to the Code.gs file.

Conditions for running the main() function and cancelling the time trigger upon expiry.

Now that we have set up our time triggers, we need to make some modifications to our Code.gs file.

We will be highlighting changes in our code and all commenting them with “// ####“.

change the modified due date to its own variable.

First, we need to modify the due date. If you look at the original code, you will see that we modified the due date to be more human readable. However, we also want to use the original format of the date to make some comparisons.

Original Code

function main() {
  var ss = SpreadsheetApp.openById(ss_id);
  var sheet = ss.getSheetByName(sheetName);
  
  var lastRow = sheet.getLastRow();
  var lastCol = sheet.getLastColumn();
  
  var dueDate = sheet.getRange(dueDateCell)
                     .getValue();
  dueDate = Utilities.formatDate(dueDate, "GMT+7", "EEE, dd MMM yyyy");
  
  var range = sheet.getRange(startRow,1,lastRow - startRow+1, lastCol); 
  var rangeVals = range.getValues();
  
  var badEmailList = [];
  var reminder = [];
  
  //Loop through range values. If tick box is not ticked continue.
  for(row in rangeVals){
    
    if(!rangeVals[row][2]){
      
      var badEmail = sendEmail(rangeVals[row], ss.getUrl, dueDate);

function main() {

var ss = SpreadsheetApp.openById(ss_id);

var sheet = ss.getSheetByName(sheetName);

var lastRow = sheet.getLastRow();

var lastCol = sheet.getLastColumn();

var dueDate = sheet.getRange(dueDateCell)

.getValue();

dueDate = Utilities.formatDate(dueDate, "GMT+7", "EEE, dd MMM yyyy");

var range = sheet.getRange(startRow,1,lastRow - startRow+1, lastCol);

var rangeVals = range.getValues();

var badEmailList = [];

var reminder = [];

//Loop through range values. If tick box is not ticked continue.

for(row in rangeVals){

if(!rangeVals[row][2]){

var badEmail = sendEmail(rangeVals[row], ss.getUrl, dueDate);

Let’s create a new variable for the formatted new date and call it: dueDateFormatted. Make sure you update the last parameter of the badeEmail Variable as well.

Updated Code

function main() {
  var ss = SpreadsheetApp.openById(ss_id);
  var sheet = ss.getSheetByName(sheetName);
  
  var lastRow = sheet.getLastRow();
  var lastCol = sheet.getLastColumn();
  
  var dueDate = sheet.getRange(dueDateCell)
                     .getValue();
  var dueDateFormatted = Utilities.formatDate(dueDate, "GMT+7", "EEE, dd MMM yyyy");#### changed var name
  
  var range = sheet.getRange(startRow,1,lastRow - startRow+1, lastCol); 
  var rangeVals = range.getValues();
  
  var badEmailList = [];
  var reminder = [];
  
  //Loop through range values. If tick box is not ticked continue.
  for(row in rangeVals){
    
    if(!rangeVals[row][2]){
      
      var badEmail = sendEmail(rangeVals[row], ss.getUrl, dueDateFormatted); // // #### Update the last var to dueDateFormatted

function main() {

var ss = SpreadsheetApp.openById(ss_id);

var sheet = ss.getSheetByName(sheetName);

var lastRow = sheet.getLastRow();

var lastCol = sheet.getLastColumn();

var dueDate = sheet.getRange(dueDateCell)

.getValue();

var dueDateFormatted = Utilities.formatDate(dueDate, "GMT+7", "EEE, dd MMM yyyy");#### changed var name

var range = sheet.getRange(startRow,1,lastRow - startRow+1, lastCol);

var rangeVals = range.getValues();

var badEmailList = [];

var reminder = [];

//Loop through range values. If tick box is not ticked continue.

for(row in rangeVals){

if(!rangeVals[row][2]){

var badEmail = sendEmail(rangeVals[row], ss.getUrl, dueDateFormatted); // // #### Update the last var to dueDateFormatted

Getting the three dates

We will need to have three dates:

Due Date
Current Date
Trigger Expiry Date

We will be using these dates to compare against one and another to determine a) the current time against the current date, and b) the current time against the Expiry Date.

We already have the due date in the variable dueDate. However, we really need to find that date earlier in our code now, There is no point calling the range values if we have decided not to run the rest of the main() function for the day.

Go ahead and move those dueDate two lines to just under the sheet variable.

Cool.

Now we need to grab the current date with new Date. Underneath the dueDate add:

var currentDate = new Date();

1	var currentDate = new Date();

Next, we want to have an expiry date. First, we want to make this easier for the user to update so let’s jump back into our global variables above main() and add the following just under the imgID global.

//#### Trigger Data
var daysToRunTrigger = 7;

1 2	//#### Trigger Data var daysToRunTrigger = 7;

Now that we have this, we can go back to our main() function and add our expiryDate variable right under currentDate.

  var expiryDate = function(){
    var result = new Date(dueDate);
    result.setDate(result.getDate() + daysToRunTrigger)
    return result;
  };

var expiryDate = function(){

var result = new Date(dueDate);

result.setDate(result.getDate() + daysToRunTrigger)

return result;

};

The expiryDate first creates a new date with the dueDate data and puts it in the result variable.

We then use the JavaScript Date setDate method to add the number of daysToRunTrigger to add 7 days to the due date. Don’t worry this will take into account monthly changes.

Our code should now look like this:

Updated Code

...
//Email data
var subject = "Quarterly Report Overdue"; //The email subject
var imgID = "1soyNy5-Fs2t8j_JCfQLCC_MZfzDPHJLs"; //The image you want to attach (optional);

//#### Trigger Data
var daysToRunTrigger = 7;

/******************************************************************************************
 * The main run function. It loops thorugh each user and calls the following functions:
 *   - sendEmail()
 *   - openCompleteWindow()
 */
function main() {
  var ss = SpreadsheetApp.openById(ss_id);
  var sheet = ss.getSheetByName(sheetName);
  
  //#### CHECK THE DATE ####
  var dueDate = sheet.getRange(dueDateCell) // #### moved to top.
                     .getValue();
  var currentDate = new Date();
  var expiryDate = function(){
    var result = new Date(dueDate);
    result.setDate(result.getDate() + daysToRunTrigger)
    return result;
  };
...

...

//Email data

var subject = "Quarterly Report Overdue"; //The email subject

var imgID = "1soyNy5-Fs2t8j_JCfQLCC_MZfzDPHJLs"; //The image you want to attach (optional);

//#### Trigger Data

var daysToRunTrigger = 7;

/******************************************************************************************

* The main run function. It loops thorugh each user and calls the following functions:

* - sendEmail()

* - openCompleteWindow()

function main() {

var ss = SpreadsheetApp.openById(ss_id);

var sheet = ss.getSheetByName(sheetName);

//#### CHECK THE DATE ####

var dueDate = sheet.getRange(dueDateCell) // #### moved to top.

.getValue();

var currentDate = new Date();

var expiryDate = function(){

var result = new Date(dueDate);

result.setDate(result.getDate() + daysToRunTrigger)

return result;

};

...

Create conditions for running the script or cancelling the time trigger

Firstly, we want to check to see if the current date is less than or equal to the due date. If this is true, then we don’t want to badger our staff with an email because the deadline is not over yet. Add this under your three dates in the main() function.

... 
//Check date
  if(currentDate &lt;= dueDate){
    return;
  }...

...

//Check date

if(currentDate <= dueDate){

return;

}...

Secondly, we need to check if the current date has exceeded the expiry date. If this is the case, then we want to cancel the time trigger.

...else if(currentDate &gt; expiryDate()){
    var cancelTrigger = cancelTimeTrigger();
    return;
  };
...

...else if(currentDate > expiryDate()){

var cancelTrigger = cancelTimeTrigger();

return;

};

...

If either of these conditions has not been met, then we are good to run the rest of the code.

Here is our code update so far from the start of the Global Variables to the end of these conditional statements:

Updated code

//***GLOBALS***

var ss_id = "1ulNkBn71t-28m8p0PyMWj-kaA9JLxcc1IXS_d7c3-Ys";//Update with your own sheet.
var sheetName = "Quarterly Report"; //Change the sheet name
var dueDateCell = "C2"; //The cell that indicates the due date.
var startRow = 6; //First row after your header.
var reminderColStart = 6; //What row your reminder starts on.

//Email data
var subject = "Quarterly Report Overdue"; //The email subject
var imgID = "1soyNy5-Fs2t8j_JCfQLCC_MZfzDPHJLs"; //The image you want to attach (optional);

//#### Trigger Data
var daysToRunTrigger = 7;

/******************************************************************************************
 * The main run function. It loops thorugh each user and calls the following functions:
 *   - sendEmail()
 *   - openCompleteWindow()
 */
function main() {
  var ss = SpreadsheetApp.openById(ss_id);
  var sheet = ss.getSheetByName(sheetName);
  
  //#### CHECK THE DATE ####
  var dueDate = sheet.getRange(dueDateCell) // #### moved to top.
                     .getValue();
  var currentDate = new Date();
  var expiryDate = function(){
    var result = new Date(dueDate);
    result.setDate(result.getDate() + daysToRunTrigger)
    return result;
  };
  
  //Check date
  if(currentDate <= dueDate){
    return;
  }else if(currentDate > expiryDate()){
    var cancelTrigger = cancelTimeTrigger();
    return;
  };
  //#### END CHECK DATE ####
...

//***GLOBALS***

var ss_id = "1ulNkBn71t-28m8p0PyMWj-kaA9JLxcc1IXS_d7c3-Ys";//Update with your own sheet.

var sheetName = "Quarterly Report"; //Change the sheet name

var dueDateCell = "C2"; //The cell that indicates the due date.

var startRow = 6; //First row after your header.

var reminderColStart = 6; //What row your reminder starts on.

//Email data

var subject = "Quarterly Report Overdue"; //The email subject

var imgID = "1soyNy5-Fs2t8j_JCfQLCC_MZfzDPHJLs"; //The image you want to attach (optional);

//#### Trigger Data

var daysToRunTrigger = 7;

/******************************************************************************************

* The main run function. It loops thorugh each user and calls the following functions:

* - sendEmail()

* - openCompleteWindow()

function main() {

var ss = SpreadsheetApp.openById(ss_id);

var sheet = ss.getSheetByName(sheetName);

//#### CHECK THE DATE ####

var dueDate = sheet.getRange(dueDateCell) // #### moved to top.

.getValue();

var currentDate = new Date();

var expiryDate = function(){

var result = new Date(dueDate);

result.setDate(result.getDate() + daysToRunTrigger)

return result;

};

//Check date

if(currentDate <= dueDate){

return;

}else if(currentDate > expiryDate()){

var cancelTrigger = cancelTimeTrigger();

return;

};

//#### END CHECK DATE ####

...

Updating the Menu to Run and Cancel The Time Trigger.

Being able to turn on and cancel the time trigger without going into Google Apps Script Editor will save us a bit of time.

You might be thinking that we only need to add a menu item to turn on the trigger because we already have a method of automatically cancelling the trigger. However, what if Peter from HR catches on that we are badgering the staff and we need to turn it off quickly or face a very uncomfortable person-to-person “conversation” time with Peter about something, something… inappropriate… something, something. No one wants to talk to Peter.

Alternatively, you might just want to cancel the trigger because all your staff have completed the task.

Anyway, enough about Peter. Let’s add these two menu items to your Reminders custom menu.

In your Code.gs file, scroll down to your onOpen() function. Go ahead and add two new items to createMenu.

/******************************************************************************************
 * Google trigger function. When the sheet is opened, a custom menu is produced.
 * 
 */

function onOpen(){

  var ui = SpreadsheetApp.getUi();
  ui.createMenu("Reminders")
  .addItem("Send reminder", "main")
  .addItem("Start time trigger","startTimeTrigger")   //#### Add this menu item
  .addItem("Cancel time trigger","cancelTimeTrigger") //#### Add this menu item
  .addToUi();

};

/******************************************************************************************

* Google trigger function. When the sheet is opened, a custom menu is produced.

function onOpen(){

var ui = SpreadsheetApp.getUi();

ui.createMenu("Reminders")

.addItem("Send reminder", "main")

.addItem("Start time trigger","startTimeTrigger") //#### Add this menu item

.addItem("Cancel time trigger","cancelTimeTrigger") //#### Add this menu item

.addToUi();

};

The addItem method of createMenu takes two parameters, the title and the function name.

For more on creating UI Menus for Google Apps Script check out the previous tutorial or go to the Docs.

All done for the menu. Feel free to run the onOpen() menu to check if your new custom menu items are there.

Send yourself a daily notification

Currently, we have commented out our popup notification system notifying us about the completion of our code and informing us of any faulty emails. This isn’t going to be particularly useful if we are not running the code live anymore. Who is going to see it? Instead, we should probably send ourselves a daily email notification.

Scroll down to the bottom of your Code.gs file. We are going to create a new function. We’ll call it sendDailySummaryEmail(). It will take the same parameter badEmailList as openCompleteWindow(badEmailList) you see just above.

We will use the MailApp Class – we go into detail on this in the previous tutorial. We are going to use the advanced parameters because they are a bit more code readable:

...
/******************************************************************************************
 * Sends a summary email to the administrator with a list of emails not sent. 
 *
 * @param {string} badEmailList - array of email addresses that could not be attemplted due to errors.
 *
 */

function sendDailySummaryEmail(badEmailList){
  //#### ADD DAILY EMAIL UPDATE SERVICE
  MailApp.sendEmail({
    to: myEmail,
    subject: mySubject, 
    htmlBody: "<p> Reminder emails send for today </p>" + 
              "<p> The following emails could not be sent:</p>" +
              badEmailList.join("<br>")
  });
  //#### END EMAIL
};

...

/******************************************************************************************

* Sends a summary email to the administrator with a list of emails not sent.

* @param {string} badEmailList - array of email addresses that could not be attemplted due to errors.

function sendDailySummaryEmail(badEmailList){

//#### ADD DAILY EMAIL UPDATE SERVICE

MailApp.sendEmail({

to: myEmail,

subject: mySubject,

htmlBody: " Reminder emails send for today " +

" The following emails could not be sent:" +

badEmailList.join(" ")

});

//#### END EMAIL

};

In this function, we first call the MailApp class and the sendEmail method. Using the advanced parameters we set the to: to equal myEmail and the subject: to equal mySubject (Lines 15 – 16). myEmail and mySubject are global variables that we need to add to the top of the Code.gs file just under the Trigger Data we added earlier.

//***GLOBALS***

var ss_id = "1ulNkBn71t-28m8p0PyMWj-kaA9JLxcc1IXS_d7c3-Ys";//Update with your own sheet.
var sheetName = "Quarterly Report"; //Change the sheet name
var dueDateCell = "C2"; //The cell that indicates the due date.
var startRow = 6; //First row after your header.
var reminderColStart = 6; //What row your reminder starts on.

//Email data
var subject = "Quarterly Report Overdue"; //The email subject
var imgID = "1soyNy5-Fs2t8j_JCfQLCC_MZfzDPHJLs"; //The image you want to attach (optional);

//#### Trigger Data
var daysToRunTrigger = 7;

//#### my Email
var myEmail = "ZyouremailZ@gmail.com";
var mySubject = "Badger Daily Email Trigger";

//***GLOBALS***

var ss_id = "1ulNkBn71t-28m8p0PyMWj-kaA9JLxcc1IXS_d7c3-Ys";//Update with your own sheet.

var sheetName = "Quarterly Report"; //Change the sheet name

var dueDateCell = "C2"; //The cell that indicates the due date.

var startRow = 6; //First row after your header.

var reminderColStart = 6; //What row your reminder starts on.

//Email data

var subject = "Quarterly Report Overdue"; //The email subject

var imgID = "1soyNy5-Fs2t8j_JCfQLCC_MZfzDPHJLs"; //The image you want to attach (optional);

//#### Trigger Data

var daysToRunTrigger = 7;

//#### my Email

var myEmail = "ZyouremailZ@gmail.com";

var mySubject = "Badger Daily Email Trigger";

You could add these two items directly as text files if you wanted to as well. I kinda like them up top with the globals.

Back down to the sendDailySummaryEmail() function, we will add our text to the htmlBody: . You’ll probably have noted that the last two lines of the old openCompleteWindow() function text were copied for this.

Speaking of the openCompleteWindow() function. Go ahead and delete it.

We will also need to reference the new sendDailySummaryEmail() function at the bottom of the main() function.

//Last two lines of the main() function
  
  var complete = sendDailySummaryEmail(badEmailList); // #### Changed from openCompleteWindow(badEmailList)
};

//Last two lines of the main() function

var complete = sendDailySummaryEmail(badEmailList); // #### Changed from openCompleteWindow(badEmailList)

};

Conclusion

You are all done.

We have created our time trigger and our cancel time trigger. Go ahead and play around with it. You can check out the executions of the code by going to View > Executions in the Code Editor for the logs as you experiment and run the code.

Now when the main() function is run it checks if the current date is before the due date and if so, does nothing. Otherwise, it checks if it is after the expiry date and if so cancels the trigger. If none of those conditions are met, then it runs the main() code and sends a reminder email to any user who has not checked the tick box. Once complete, the code sends a notification to us, the admin, to tell us it is done and if there were any faulty emails.

Finally, we set up the Reminder menu to be able to set the time trigger or cancel it.

Here is the final update Code.gs and TimeTrigger.gs files in their entirety.

Create and Publish Google Workspace Add-ons with Apps Script Course 300px

Code.gs

/*
 * Send and record reminders if a project checklist has not been
 * updated to indicated that task is complete.
 *
 * TO EDIT
 * If you wish to use this script as a basic template you will need
 * to edit the following:
 * - GLOBALS - the global variables just below. 
 * - email.html - this contains the custom HTML of your email message along with
 *                inline limited CSS.
 * - sendEmail() - Change the staff object data and body data. This is the unique 
 *                 information for each user that you will add into your email.
 */

//***GLOBALS***

var ss_id = "1ulNkBn71t-28m8p0PyMWj-kaA9JLxcc1IXS_d7c3-Ys";//Update with your own sheet.
var sheetName = "Quarterly Report"; //Change the sheet name
var dueDateCell = "C2"; //The cell that indicates the due date.
var startRow = 6; //First row after your header.
var reminderColStart = 6; //What row your reminder starts on.

//Email data
var subject = "Quarterly Report Overdue"; //The email subject
var imgID = "1soyNy5-Fs2t8j_JCfQLCC_MZfzDPHJLs"; //The image you want to attach (optional);

//#### Trigger Data
var daysToRunTrigger = 7;

//#### my Email
var myEmail = "ZyouremailZ@gmail.com";
var mySubject = "Badger Daily email trigger";

/******************************************************************************************
 * The main run function. It loops thorugh each user and calls the following functions:
 *   - sendEmail()
 *   - openCompleteWindow()
 */
function main() {
  var ss = SpreadsheetApp.openById(ss_id);
  var sheet = ss.getSheetByName(sheetName);
  
  //#### CHECK THE DATE ####
  var dueDate = sheet.getRange(dueDateCell) // #### moved to top.
                     .getValue();
  var currentDate = new Date();
  var expiryDate = function(){
    var result = new Date(dueDate);
    result.setDate(result.getDate() + daysToRunTrigger)
    return result;
  };
  
  //Check date
  if(currentDate <= dueDate){
    return;
  }else if(currentDate > expiryDate()){
    var cancelTrigger = cancelTimeTrigger();
    return;
  };
  //#### END CHECK DATE ####

  
  var lastRow = sheet.getLastRow();
  var lastCol = sheet.getLastColumn();
  
  var dueDateFormatted = Utilities.formatDate(dueDate, "GMT+7", "EEE, dd MMM yyyy"); // #### changed var name
  
  var range = sheet.getRange(startRow,1,lastRow - startRow+1, lastCol); 
  var rangeVals = range.getValues();
  
  var badEmailList = [];
  var reminder = [];
  
  //Loop through range values. If tick box is not ticked continue.
  for(row in rangeVals){
    
    if(!rangeVals[row][2]){
      
      var badEmail = sendEmail(rangeVals[row], ss.getUrl, dueDateFormatted); // #### Update the last var to dueDateFormatted
      
      
      //Update Redminder Sent column F(5) and Last Reminder Date column G(6);
      if(!badEmail){
        rangeVals[row][5] += 1;
        rangeVals[row][6] = new Date();
      }else{
        badEmailList.push(badEmail);
      };
         
      
    };
    reminder.push([rangeVals[row][5],rangeVals[row][6]])
  }; 
  
  var reminderRange = sheet.getRange(startRow, reminderColStart,lastRow - startRow+1, 2);
  reminderRange.setValues(reminder);
  
  var complete = sendDailySummaryEmail(badEmailList); // #### Changed from openCompleteWindow(badEmailList)
};

/******************************************************************************************
 * Google trigger function. When the sheet is opened, a custom menu is produced.
 * 
 */
 
function onOpen(){

  var ui = SpreadsheetApp.getUi();
  ui.createMenu("Reminders")
  .addItem("Send reminder", "main")
  .addItem("Start time trigger","startTimeTrigger")   //#### Add this menu item
  .addItem("Cancel time trigger","cancelTimeTrigger") //#### Add this menu item
  .addToUi();

};

/******************************************************************************************
 * Sends HTML email to user who has not completed task. 
 * The HTML template is created from the email.html file. 
 *
 * @param {array} overDueStaff - 2d array of overdue staff with full row data.
 * @param {string} sheetURL - url of current Spreadsheet.
 * @param {date} dateDue - formatted date.
 *
 * @return {array} - Returns array of email addresses that could not be attemplted due to errors.
 */
function sendEmail(overDueStaff, sheetURL, dateDue){
  
  //Staff Details
  var staff = {
    "name": overDueStaff[0],
    "email": overDueStaff[1],
    "dueDate": dateDue,
    "daysOverdue": overDueStaff[4]
  };
  staff.daysOverdue = (staff.daysOverdue > 1) ? staff.daysOverdue + " days" : staff.daysOverdue + " day";

  
  //Setup embedded image.
  var imageURL = "https://drive.google.com/uc?id="+imgID;
  var badgerBlob = UrlFetchApp
                         .fetch(imageURL)
                         .getBlob()
                         .setName("badgerBlob");
  
  
  var body = HtmlService.createTemplateFromFile("email");
  body.name = staff.name;
  body.due = staff.dueDate;
  body.overdue = staff.daysOverdue;
  body.reportName = sheetName;
  
  
  try{
      MailApp.sendEmail({
      to: staff.email,
      subject: subject,
        htmlBody: body.evaluate().getContent(), 
      inlineImages:
      {
      badger: badgerBlob
    }
                      });
  }
  catch(error){
    return staff.email;
  }
};


/******************************************************************************************
 * Sends a summary email to the administrator with a list of emails not sent. 
 *
 * @param {string} badEmailList - array of email addresses that could not be attemplted due to errors.
 *
 */

function sendDailySummaryEmail(badEmailList){
  //#### ADD DAILY EMAIL UPDATE SERVICE
  MailApp.sendEmail({
    to: myEmail,
    subject: mySubject, 
    htmlBody: "<p> Reminder emails send for today </p>" + 
              "<p> The following emails could not be sent:</p>" +
              badEmailList.join("<br>")
  });
  //#### END EMAIL
};

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

* Send and record reminders if a project checklist has not been

* updated to indicated that task is complete.

* TO EDIT

* If you wish to use this script as a basic template you will need

* to edit the following:

* - GLOBALS - the global variables just below.

* - email.html - this contains the custom HTML of your email message along with

* inline limited CSS.

* - sendEmail() - Change the staff object data and body data. This is the unique

* information for each user that you will add into your email.

//***GLOBALS***

var ss_id = "1ulNkBn71t-28m8p0PyMWj-kaA9JLxcc1IXS_d7c3-Ys";//Update with your own sheet.

var sheetName = "Quarterly Report"; //Change the sheet name

var dueDateCell = "C2"; //The cell that indicates the due date.

var startRow = 6; //First row after your header.

var reminderColStart = 6; //What row your reminder starts on.

//Email data

var subject = "Quarterly Report Overdue"; //The email subject

var imgID = "1soyNy5-Fs2t8j_JCfQLCC_MZfzDPHJLs"; //The image you want to attach (optional);

//#### Trigger Data

var daysToRunTrigger = 7;

//#### my Email

var myEmail = "ZyouremailZ@gmail.com";

var mySubject = "Badger Daily email trigger";

/******************************************************************************************

* The main run function. It loops thorugh each user and calls the following functions:

* - sendEmail()

* - openCompleteWindow()

function main() {

var ss = SpreadsheetApp.openById(ss_id);

var sheet = ss.getSheetByName(sheetName);

//#### CHECK THE DATE ####

var dueDate = sheet.getRange(dueDateCell) // #### moved to top.

.getValue();

var currentDate = new Date();

var expiryDate = function(){

var result = new Date(dueDate);

result.setDate(result.getDate() + daysToRunTrigger)

return result;

};

//Check date

if(currentDate <= dueDate){

return;

}else if(currentDate > expiryDate()){

var cancelTrigger = cancelTimeTrigger();

return;

};

//#### END CHECK DATE ####

var lastRow = sheet.getLastRow();

var lastCol = sheet.getLastColumn();

var dueDateFormatted = Utilities.formatDate(dueDate, "GMT+7", "EEE, dd MMM yyyy"); // #### changed var name

var range = sheet.getRange(startRow,1,lastRow - startRow+1, lastCol);

var rangeVals = range.getValues();

var badEmailList = [];

var reminder = [];

//Loop through range values. If tick box is not ticked continue.

for(row in rangeVals){

if(!rangeVals[row][2]){

var badEmail = sendEmail(rangeVals[row], ss.getUrl, dueDateFormatted); // #### Update the last var to dueDateFormatted

//Update Redminder Sent column F(5) and Last Reminder Date column G(6);

if(!badEmail){

rangeVals[row][5] += 1;

rangeVals[row][6] = new Date();

}else{

badEmailList.push(badEmail);

};

reminder.push([rangeVals[row][5],rangeVals[row][6]])

};

var reminderRange = sheet.getRange(startRow, reminderColStart,lastRow - startRow+1, 2);

reminderRange.setValues(reminder);

var complete = sendDailySummaryEmail(badEmailList); // #### Changed from openCompleteWindow(badEmailList)

};

/******************************************************************************************

* Google trigger function. When the sheet is opened, a custom menu is produced.

function onOpen(){

var ui = SpreadsheetApp.getUi();

ui.createMenu("Reminders")

.addItem("Send reminder", "main")

.addItem("Start time trigger","startTimeTrigger") //#### Add this menu item

.addItem("Cancel time trigger","cancelTimeTrigger") //#### Add this menu item

.addToUi();

};

/******************************************************************************************

* Sends HTML email to user who has not completed task.

* The HTML template is created from the email.html file.

* @param {array} overDueStaff - 2d array of overdue staff with full row data.

* @param {string} sheetURL - url of current Spreadsheet.

* @param {date} dateDue - formatted date.

* @return {array} - Returns array of email addresses that could not be attemplted due to errors.

function sendEmail(overDueStaff, sheetURL, dateDue){

//Staff Details

var staff = {

"name": overDueStaff[0],

"email": overDueStaff[1],

"dueDate": dateDue,

"daysOverdue": overDueStaff[4]

};

staff.daysOverdue = (staff.daysOverdue > 1) ? staff.daysOverdue + " days" : staff.daysOverdue + " day";

//Setup embedded image.

var imageURL = "https://drive.google.com/uc?id="+imgID;

var badgerBlob = UrlFetchApp

.fetch(imageURL)

.getBlob()

.setName("badgerBlob");

var body = HtmlService.createTemplateFromFile("email");

body.name = staff.name;

body.due = staff.dueDate;

body.overdue = staff.daysOverdue;

body.reportName = sheetName;

try{

MailApp.sendEmail({

to: staff.email,

subject: subject,

htmlBody: body.evaluate().getContent(),

inlineImages:

{

badger: badgerBlob

}

});

}

catch(error){

return staff.email;

}

};

/******************************************************************************************

* Sends a summary email to the administrator with a list of emails not sent.

* @param {string} badEmailList - array of email addresses that could not be attemplted due to errors.

function sendDailySummaryEmail(badEmailList){

//#### ADD DAILY EMAIL UPDATE SERVICE

MailApp.sendEmail({

to: myEmail,

subject: mySubject,

htmlBody: " Reminder emails send for today " +

" The following emails could not be sent:" +

badEmailList.join(" ")

});

//#### END EMAIL

};

TimeTrigge.gs

function startTimeTrigger() {

  ScriptApp.newTrigger('main')
           .timeBased()
           .atHour(7)
           .everDays(1)
           .create();

};

function cancelTimeTrigger(){
  
  var triggers = ScriptApp.getProjectTriggers();
  
  for(var i = 0; i < triggers.length; i++){
    if(triggers[i].getTriggerSource() == ScriptApp.TriggerSource.CLOCK){
      ScriptApp.deleteTrigger(triggers[i]);
    };
  };
};

function startTimeTrigger() {

ScriptApp.newTrigger('main')

.timeBased()

.atHour(7)

.everDays(1)

.create();

};

function cancelTimeTrigger(){

var triggers = ScriptApp.getProjectTriggers();

for(var i = 0; i < triggers.length; i++){

if(triggers[i].getTriggerSource() == ScriptApp.TriggerSource.CLOCK){

ScriptApp.deleteTrigger(triggers[i]);

};

Finally, here is a link to the updated version of The Badge with the new code:

The Badger V2

Just got to File > Make a copy and you have your own version of the Google Sheet and the accompanying Code.

Need help with Google Workspace development?

Go something to solve bigger than Chat GPT?

I can help you with all of your Google Workspace development needs, from custom app development to integrations and security. I have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.

Schedule a free consultation today to discuss your needs and get started or learn more about our services here.

8 thoughts on “Google Apps Scripts: Create Time Triggers to automatically send email task reminders from a Google Sheets check list”

Rigo Avila says:

at 11:56 pm

Hello All, thank you for such a detailed and thoughtful explanation of this script. I followed it carefully but the emails is not firing on V2. The original does send out the email. Any help is appreciated. Thank you

Loading...

Trang says:

at 6:47 am

Appreciate the walkthrough. But I still have a few questions:
– is there a way to activate the trigger without clicking the menu item? I can go to scripts.google.com to start a new trigger, but other users of the spreadsheet may not know about it.
– is cancelling the trigger mandatory?
I’m thinking of a scenario where I have a spreadsheet of to-do items and I need to check for the completed tasks at a fixed time every day and move them to another sheet for archiving.

Loading...

1. Yagi says:
  
  at 6:57 am
  
  Hi Trang,
  
  No, you do not need a menu item or a button to activate the script. You can run the code in the Google Apps Script Editor and then the trigger run in the background.
  It is good practice to cancel triggers so as not to unintentionally exceed your trigger runtime quota over multiple projects.
  
  Cheers,
  
  Yagi
  
  Loading...
  
  1. Trang says:
    
    at 12:43 pm
    
    Thank you for taking the time to reply. So the code to create the trigger can be run from the Apps Script Editor. However, if the spreadsheet is to be shared with other people who are not proficient in Apps Script, then having a menu item or button to activate the script is still preferable, am I right?
    
    Anyway, thanks for sharing so much of your coding knowledge with us. Knowing one more language is so empowering.
    
    Loading...
    
2. Trung Tang says:
  
  at 11:04 pm
  
  Hello Trang,
  
  You can create a button and assign a script function to it. Check this out https://www.benlcollins.com/apps-script/google-sheets-button/
  
  Loading...
  
Trang says:

at 1:22 pm

Hi again. I forgot to ask:
Is there any way to style the menu item to indicate that it has been clicked? For example, I’d like to add a checkmark in front of the Start Time Trigger item once it has been clicked.

Loading...

Mv says:

at 9:58 pm

Hello there,

Could someone explain to me what the “Choose which deployment should run:” option means?

Thank you

Loading...

clintfromnytova says:

at 2:48 pm

Would love to extend the undo send longer than 30 seconds. Can this be done with a few lines of code in an apps script?

Loading...

Google Apps Scripts: Create Time Triggers to automatically send email task reminders from a Google Sheets check list

Setting Up

Run for the first time

Time-Based Triggers using the GSuite Developer Hub

Time-Based Triggers using the ClockTriggerBuilder

Back to our example

TimeTrigger.gs

startTimeTrigger()

cancelTimeTrigger()

Conditions for running the main() function and cancelling the time trigger upon expiry.

change the modified due date to its own variable.

Getting the three dates

Create conditions for running the script or cancelling the time trigger

Updating the Menu to Run and Cancel The Time Trigger.

Send yourself a daily notification

Conclusion

Code.gs

TimeTrigge.gs

Like this:

Related

8 thoughts on “Google Apps Scripts: Create Time Triggers to automatically send email task reminders from a Google Sheets check list”

Leave a ReplyCancel reply

Setting Up

Run for the first time

Time-Based Triggers using the GSuite Developer Hub

Time-Based Triggers using the ClockTriggerBuilder

Back to our example

TimeTrigger.gs

startTimeTrigger()

cancelTimeTrigger()

Conditions for running the main() function and cancelling the time trigger upon expiry.

change the modified due date to its own variable.

Getting the three dates

Create conditions for running the script or cancelling the time trigger

Updating the Menu to Run and Cancel The Time Trigger.

Send yourself a daily notification

Conclusion

Code.gs

TimeTrigge.gs

Share this:

Like this:

Related

8 thoughts on “Google Apps Scripts: Create Time Triggers to automatically send email task reminders from a Google Sheets check list”

Leave a ReplyCancel reply