Pages

Thursday, 7 March 2019

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.
Screenshot of date-picker and dropdown boxes
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.

/*
This function creates the trigger schedule for the message from the given date/time.
*/
function createSchedule() {
// log user action
logEvent('createSchedule function started');
// get spreadsheet data
var ss = SpreadsheetApp.getActiveSpreadsheet();
var emailSheet = ss.getSheetByName('Email Schedule');
var emailData = emailSheet.getDataRange().getValues();
var lastRow = emailSheet.getLastRow();
ss.toast('Started ...', 'Create Schedule');
// get current time
var timeZone = ss.getSpreadsheetTimeZone();
var time = new Date().getTime();
// loop through each row and create a new trigger *************************
for (var i=2; i<lastRow; i++) {
try {
// reset 'Status' variable for loop
var statusText = '';
// reset variable for loop
var newTriggerID = '';
// check date cell is not blank
var dateTime = emailData[i][2];
if (dateTime != "") {
// add hours/minutes to form date/time
dateTime.setHours(emailData[i][3]);
dateTime.setMinutes(emailData[i][4]);
/* check Status not already 'Scheduled' | '*Delivered*'
AND no existing Schedule ID */
var status = emailData[i][5];
var isDelivered = status.match(/.*Delivered.*/);
var triggerID = emailData[i][6];
if ((status != 'Scheduled') && (isDelivered == null) && (triggerID == "")) {
// create Date from date/time value for trigger
var schedule = new Date(dateTime);
Logger.log('Scheduled Time is: ' + schedule);
// check Scheduled Time is not in the past
if (schedule.getTime() > time) {
Logger.log('Scheduled Time is not in the past');
// create a time-based trigger to run 'sendEmail' function
var newTrigger = ScriptApp.newTrigger('sendEmail')
.timeBased()
.at(schedule)
.inTimezone(timeZone)
.create();
// get ID for trigger to paste in spreadsheet
var newTriggerID = newTrigger.getUniqueId();
Logger.log('New Trigger ID is: ' + newTriggerID);
statusText = 'Scheduled';
}
else {
Logger.log('Scheduled Time is in the past');
statusText = 'Date is in the past';
}
// write status into 'Status' column
emailSheet.getRange(i+1, 6).setValue(statusText);
// write triggerID into 'Schedule ID' column
emailSheet.getRange(i+1, 7).setValue(newTriggerID);
}
else{}
}
else {
Logger.log('Date field is blank in spreadsheet');
}
}
catch(e) {
Logger.log('Error creating message schedule: ' + e);
}
} // end of loop through each row and create a new trigger *****************
ss.toast('Completed.', 'Create Schedule');
// log user action
logEvent('createSchedule function finished');
}

No comments:

Post a Comment