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

Create a time trigger to run reminder notices in google apps script

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.

Gsuite Developer hub time trigger

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.

Google Apps Script email remind from Google Sheet The Badger 2

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.

google sheets benjamin lee

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.

update Spreadsheet id in Google Apps Script

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.

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

Google Sheet Script authorization

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

Google App Script Editor Current Project's Triggers

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

Gsuite Developer hub time trigger add trigger

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:

  1. Choose which function to run: If it isn’t on main change it. This is our main() function.
  2. Choose which deployment should run: Keep this as, set to head.
  3. Select event source: We want a time-driven event here.
  4. Select type of time-based trigger: Set to Day timer.
  5. 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.
  6. Check the time zone matches the timezone you are working in.

Gsuite Developer hub time trigger add trigger choices

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()

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()

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

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

Getting the three dates

We will need to have three dates:

  1. Due Date
  2. Current Date
  3. 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:

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.

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

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 month changes.

Our code should now look like this:

Updated Code

Create conditions for running the script or cancelling the time trigger

Firstly, we want to check to see if the current date is less then 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.

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.

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

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.

Google Apps Script Custom Menu Item for triggers in google Sheets

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

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:

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.

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.

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.

Code.gs

TimeTrigge.gs

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.

Looking to learn more about Google Apps Scripts in a more structured format? Udemy has some great courses that can get your from the basics to a real Google Apps Script pro!

Got a more specific problem you need help with, but don’t have the time to develop the skills? Fiverr’s your best bet to find a skilled Google Apps Script professional to solve your problem quickly and cheaply. *

 

*The above affiliate links have been carefully researched to get you to what you specifically need. If you decide to click on one of these links it will cost you just the same as going to the site. If you decide to sign up, I just get a little pocket money to help pay for the costs of running this website.

Leave a Reply