Month: March 2021

Carelessly left behind Google Apps Script time triggers can be greedy little blighters that can ultimately end in a whole lot of noggin scratching when your scheduled scripts decide not to run all of a sudden. Then there is a whole lot of house cleaning to remove all those time triggers you couldn’t be bothered setting a calendar reminder to remove them when you didn’t nee them any longer.

After all, there are some pretty tight quotas for the Consumer account (90 min) and if you are doing some heaving lifting in your Google Workspace account (6 hrs), then it will add up fast. Well… maybe I am just a glutton for triggers.

In this tutorial, we will cover how to schedule weekly time triggers between a period of dates in Google Apps Script programmatically using the Clock Trigger method of the ScriptApp class. The code basically sets all the triggers up on the desired range of dates and then removes all the triggers when the time expires.

Let’s get stuck into it!

The Code

Triggers.gs

This is the main code you will copy and paste into your own project. Read the Quick Use guide for what you will need to update for your own project.

ExampleFuncitons.gs

This file is just an example of the functions I have assigned for my project in my runsies() function.

Quick use guide

Here, we will quickly go through using the script to get you up and running.

The Example

In the example, we have an imaginary document that needs to be edited by our team. If you look at the ExampleFuncitons.gs file you can see our list of time trigger task for our Google Apps Script project as follows:

• Refresh the Google Sheet: We possibly need to send a report and clear it out at a certain time to set it up for the next week.
• Open and send an access email: We will send off an email to our team to let them know that they now have edit access for the sheet and complete their weekly task.
• Send a deadline reminder: Just before the deadline, we send out a reminder to our stragglers that the deadline is due so that they can get unnecessarily offended. 🙄🐐
• Remove the editors from the sheet: Once the deadline hits, we revoke edit access for our team from the sheet to maintain the integrity of the sheet before we send off our report.

Before you add your trigger times

A note on time zones

Just hold up one second. Before you add your trigger times, I recommend that you double-check the time zone assigned to your project.

Didn’t know that was a thing?  No worries. Check out this short video on how to get that done fast:

And you can dive a little deeper here:

Help! My time triggers are not in sync! : How to update your Google Apps Script project time zone.

Adding the script into your project

I recommend creating a separate *.gs file to add in the timeTrigger object from lines 35-145. It just gets it out of the way of your awesome project codes.

The runsies() function is just an example function. If all you want to do is add your triggers days and dates in and hit run, then it is fine. But if you want to programmatically draw your start and end trigger dates and weekly trigger times from somewhere else then all you need to add into your own function is the  TIME_TRIGGER objects and then run:

timeTrigger.deploy(TIME_TRIGGER);

Adding your times and date window

There are two parts to this object. First, set the start and end date that you want to run your weekly triggers. Lines 4 & 5

If you want to start your triggers straight away, then you can enter “now”, otherwise enter in a date. You will get an error message if your date is before the current date.

The end date removes all the time triggers in your project. So if you have other triggers, you will need to make some changes to the timeTrigger object (Maybe register each trigger id in PropertiesSerivce).

To add your dates, start with the year, month and date. Note that unlike the weirdness of the Javascript Date() constructor, I have made the month the common number. So a 3 will be March (not April in the Date() constructor).

Next, add the weekly triggers that you want to run each week for this project. Lines 21-28

You can add in as many as you want here. In the example, I have added four. Each piece of weekly trigger data is contained in an array:

[function, weekday, hour]

• function: The function that you want to be triggered.
• weekday: Full word days of the week from Monday to Sunday.
• hour: The hour of the day that you want to run the trigger.

Deploying and scheduling the time trigger

To schedule, your  triggers run the timeTrigger.deploy(TIME_TRIGGER); function.

If you want to test things before your first deploy, you can check the triggers are all set up you can check the triggers in your Apps Script menu (1).

To check the times of your trigger, you can click on the vertical ellipses beside each trigger (2).

You can delete all the triggers in your project and start again with:

timeTrigger.remove();

That’s pretty much all you need to know to get this script up and running in your project.

If you want to learn more about how the timeTrigger object was written and how to code Clock Triggers, jump down into the next header.

Need help with Google Workspace development?

My team of experts can help you with all of your needs, from custom app development to integrations and security. We 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.

Code breakdown for timeTrigger

timeTrigger.deploy()

The timeTrigger.deploy() function is the main run function and takes the object of start and end dates and weekly triggers that we assigned in the TIME_TRIGGER object in the runsies() function.

Its first task is to set the date the weekly triggers need to be removed. This is done with the setEndDateTrigger(triggerData) function that we will discuss in a minute.

Check if start dat is now

Next, we need to check the input for the triggerData.startDate. If the user selected “Now”, then we immediately run timeTrigger.setTrigger() and complete the script. Lines 12-16

Check if start date is today

Alternatively, if the user puts in the current day’s date then we need to run the timeTrigger.setTrigger() straight away too. First, we need to transform the users triggerData.StartDate data into a readable date. We do this using the Javascript new Date() constructor that has the option to take the format; year, month, date:

new Date(year, month index, day of month)

Because the month index for Date()  starts at 0 for January and ends at 11 for December, so we need to subtract 1 from the users month input before creating the date. Line 18

I used a Javascript spread operator (…array) to add in all the values of triggerData.StartDate which in our example are:

[2021, 3, 8]

So:

new Date(...triggerData.startDate)

Is actually, this:

new Date(2021, 3, 8)

Which is much tidier than:

new date(triggerData.startDate[0], triggerData.startDate[1], triggerData.startDate[2])

I’m really digging the spread operator lately. 

Next, we need to compare the current date with the triggerData.startDate. We can do this by converting the two dates to times using the getTime() method. This transforms the dates into long number values that can be compared. Line 25

Before we do this though we kinda need to clear the current time out of the current data otherwise we won’t be able to compare things properly (line 26). When we run new Date() to create the today variable it will give us the date and the current time all the way down to milliseconds. However when we created the date for our scheduled date we only added the year, month and day so the time will be set to midnight.

Let’s update today date by changing the time of the day to midnight with:

today.setHours(0, 0, 0, 0);

If start date is in the future, schedule it!

Our final condition is if the user has scheduled a date in the future. We don’t want to clutter up their trigger quota unnecessarily so we need to postpone our triggers. To do this we ironically, need to create a clock trigger that runs only once on the date we want our weekly triggers to start.

To programmatically set a time trigger in Google Apps Script we call the newTrigger method in the ScriptApp class. Line 34

The newTrigger method takes our assigned function as an argument. This is the function we want to run when our trigger goes off. For us, this is the timeTrigger.setTrigger() function that will build the weekly triggers.

Running setTrigger() also starts the process of building the trigger.

Your next step is to decide what type of trigger you want. In our project, we want a timeBased() trigger. Line 35

Selecting timeBased() takes us to the Clock Trigger builder, where we can choose from a bunch of settings. For us, all we want to set is a start date so we chose the at(date) method which takes a constructed Javascript date. Here we throw in the startDate variable we built on line 19.

Once we have all our trigger data inputted, we need to create() our trigger.

timeTrigger.setEndDateTrigger()

The timeTrigger.setEndDateTrigger() function takes the triggerData as an argument. From that, it extracts the endDate array of year month and day.

Just like in timeTrigger.deploy(), we need to take one away from the month to get the proper value for Javascript. Then we want to remove our weekly triggers basically on the midnight of the following day after the user’s recorded end date. Lines 11-12

Why? Well, the end date usually means that it is a date the final trigger will occur on. We don’t want to remove our trigger before that date though or we will mess up our user’s process.

Once we have created our date we go through the same process as scheduling a start date in the future like we did in timeTrigger.deploy().

timeTrigger.setTrigger()

This trigger is initialised from either the timeTrigger.deploy() straight away if the start date is the current date or is scheduled for deployment at a later date.

The timeTrigger.setTrigger() takes the 2d array of all the weekly triggers assigned by the user. In our example, that was this:

First, it loops through each set of trigger input data with a forEach loop.

Assigning variables using destructuring

I want to assign a variable for each item in the currently iterated array so that I can work with it in building the trigger. Here I used a destructuring assignment now available in Google Apps Script V8 runtime. Line 11

If you are unfamiliar with the destructuring assignment, you can basically set an array of variables – in our case – on the left and assign (=) them to a corresponding array of data on the right. So:

let [nextFunct, weekday, hour] = time

Would assign these values to the varialbes on the first iteration:

let [nextFunct, weekday, hour] = ["refreshSheet", "Monday", 7]

Without destructuring this variable assignment might look like this:

Pretty cool, I reckon.

PReparing the days of the week

Next, we need to assign a day of the week to our clock trigger. These assignments require an enumerator which is basically:

ScriptApp.WeekDay.A_DAY_OF_THE_WEEK _IN_ALL_CAPS

Fortunately for us, we assigned our variable weekday to time[1] which is the day of the week for this iteration.

Lines 13-21

Creating the weekly clock triggers

Finally, we create our first clock trigger. Just like our date triggers we first call ScriptApp.newTrigger(nextFunct) where nextFunct is the time[0] value the user assigned as their function that they want to run on their trigger.

Again, we set the trigger to timeBased() but this time around we use onWeekDay() method. This method takes one of those weekday enumerators we store in our wkDay variable. We will select the correct one by adding weekday variable inside weekday:

wkDay[weeday] Line 25

For weekday triggers we can also set the hour of the day. This will deliver the trigger close to the hour selected. Line 26

timeTrigger.remove()

The timeTrigger.remove() function runs on the end date. Alternatively, you can run this to remove all of your triggers if you are testing on making a mistake.

To remove your triggers, you will first need to get all of your projects triggers. We do this with ScriptApp.getProjectTriggers();Then, you will need to loop through each trigger and delete the trigger using the deleteTrigger() method that takes the trigger object.

 Conclusion

I have been deploying triggers like this for a while now after and…erhm…unfortunate incident with meeting my quota and being lazy and leaving old triggers active. This now saves me a lot of future grief.

One thing you might have to adjust for a bigger project is the timeTrigger.Remove() method. If you have other triggers running in the same project, you probably don’t want to delete them. You could use the properties service to store your trigger ids and then check them against the list of all triggers to determine if they need to be deleted.

Another thing worth considering is that you might want to schedule multiple dates to run your triggers. You would need to loop through each start and end date here and create a trigger for each one.

Anyway, if you found this useful please give it a like in the comments below and if you have a plan to deploy it in your own project or a version of it I would love to hear about it in the comments.

Happy coding!

~Yagi