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 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')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.
.timeBased()
.at(schedule)
.inTimezone(timeZone)
.create();
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
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