Email scheduler for Gmail - create schedule

Overview blog post.

The following Google Apps Script code creates the trigger schedule for the message from the given date/time.

Add the date/time using the picker and dropdown boxes

Data Validation
Data validation has been used in the spreadsheet to ensure the date/time is entered in the correct format so that the script can handle the necessary values. A date-picker has been used to achieve the format yyyy-mm-dd by specifying the cell contents must be a valid date. The dropdown boxes for Hours/Minutes is a List from a range where there is a hidden sheet that has the hours from 00 to 23 and the minutes as intervals of 5 (so 00, 05, 10 etc).

The Code
For the apps script itself we get all of the spreadsheet data including the current time ('getTime') and begin to loop through each row looking for date/time values. Once those values have been evaluated we check the status of the message is not already Scheduled and that there is no existing Schedule ID (suggesting a trigger may already exist).

We check the formatted date/time from the spreadsheet is greater than (after) the current time - acquired at the start of the script - this ensures we do not try to schedule something for the past. The below snippet of code is used to create the actual trigger (which will run +/- 15mins):

var newTrigger = ScriptApp.newTrigger('sendEmail')
.timeBased()
.at(schedule)
.inTimezone(timeZone)
.create();

With the trigger setup we now need to 'getUniqueId' so that we can paste it into the spreadsheet for use when we want to cancel the scheduled message or delete the trigger once it has ran. We also paste in the text Scheduled as confirmation for the user.