Email scheduler for Gmail - cancel schedule

Overview blog post.

The following Google Apps Script code cancels the selected scheduled message and removes its associated trigger. This was created as there may be times when a user changes their mind on sending an email or they make need to make a correction with the date/time - hence this provides a safety net for stopping the schedule.

Once we have all of the data from the spreadsheet we create a loop that will check for 3 components before trying to run - as if one of these is missing then it is not possible to fully cancel a schedule. We use the Message Actions column to select Cancel Schedule which along with a status of Scheduled and a valid trigger ID we satisfy the requirements of the if statement.

From this point we run the deleteTrigger function and feed in the trigger ID as a parameter (this function will be detailed in the next blog post). We then update the Status column to Cancelled to inform the user and 'clearContent' of Schedule ID and the Message Actions columns to tidy up.

	function cancelScheduled() {
	// log user action
	logEvent('selected to cancel scheduled email');
	// get spreadsheets
	var ss = SpreadsheetApp.getActiveSpreadsheet();
	var emailSheet = ss.getSheetByName('Email Schedule');
	var emailData = emailSheet.getDataRange().getValues();
	var lastRow = emailSheet.getLastRow();
	var configSheet = ss.getSheetByName('Config');
	var cancelText = configSheet.getRange(4, 6).getValue();
	ss.toast('Started ...', 'Cancel Scheduled Message');
	// loop through each row looking for messages marked for cancelling *************************
	for (var i=2; i<lastRow; i++) {
	var action = emailData[i][7];
	var status = emailData[i][5];
	var triggerID = emailData[i][6];
	// check cancel action has been selected AND 'Status' is 'Scheduled' AND 'Schedule ID' exists
	if ((action == cancelText) && (status == 'Scheduled') && (triggerID != "")) {
	// run function to delete corresponding trigger
	deleteTrigger(triggerID);
	// change 'Status' of message to Cancelled
	emailSheet.getRange(i+1, 6).setValue('Cancelled');
	// clear content of 'Schedule ID'
	emailSheet.getRange(i+1, 7).clearContent();
	// clear content of 'Cancel Scheduled Message'
	emailSheet.getRange(i+1, 8).clearContent();
	}
	else {}
	}// end of loop through each row looking for messages marked for cancelling *****************
	ss.toast('Completed.', 'Cancel Scheduled Message');
	// log user action
	logEvent('cancelling scheduled email has finished');
	}

view raw Email scheduler for Gmail - cancel schedule.gs hosted with ❤ by GitHub