Probation Tracker Tool

The following Google Apps Script tool was developed to allow you to easily be reminded of when upcoming probation review meetings are due. Enter the details into the Google Sheet, including upcoming review dates, and the tool will automatically email the Line Manager both 14 days before and on the day of the review date.

Manage probation review dates in a spreadsheet and be sent reminder emails

Key Functionality

• The daily check is performed between 7am - 8am every day and for any emails that are sent a Note will be attached to the cell with its timestamp for record purposes and it will be coloured in light-green.
• You can continue to append further rows for new staff starters after the initial Sheet setup or you may wish to delete/move rows for those that have completed their probation period.
• You can adjust dates, emails addresses, etc at any point in time.
• If you enter something into the ‘Probation Passed’ column then that given row will be skipped in future checks, to help save time if you continue to add to this Sheet.
• You do not have to enter a review date for every single column, thus allowing flexibility between differing roles, just leave it blank.
• There is a Log sheet to help capture any errors and it will attempt to email the account that creates the daily check (trigger) to alert them.
• Email content:
• Subject: Probation Review due
• Message:
• Hi <Line Manager’s Name>
• A probation review for <Full Name> is due <Time period> for a <Number of months> month review.
• Thank you

Instructions

1. Start to complete the relevant columns in the Google Sheet. You do not need to add everybody at this point but you should ensure you complete the required information for any rows that you do start:
• Full Name - required.
• Job Title - optional.
• Job Type/Area - optional.
• Start Date - optional.
• Line Manager’s Name - required.
• Line Manager’s Email - required.
• Probation Length - optional.
• 3 Month Review Date - optional - double click in the cell to activate the date picker.
• 6 Month Review Date - optional - double click in the cell to activate the date picker.
• 9 Month Review Date - optional - double click in the cell to activate the date picker.
• 12 Month Review Date - optional - double click in the cell to activate the date picker.
• Probation Passed - optional - adding any details in here will skip the row in future e.g. the person has completed their probation but you want to continue appending new staff to this Sheet without it re-checking previous ones.
• Notes - optional.
  
  
2. Create the daily check - you must ensure you are not logged into more than one Google Account before you perform this step. It only needs to be performed once (unless you later remove the tool):
1. Click the ‘Admin’ option in the menu bar at the top of the Google Sheet, then click ‘Create Trigger’.
2. After selecting this option you will be asked to give permission to ensure you are happy for it to access various parts of your Google account.
3. Please click through the next few screens to allow it to connect to your Account.
4. After clicking the final ‘Allow’ option you will be returned to the Google Sheet. Now despite successfully authorising the tool you will need to select the previous menu item once more, as Google prevents it from automatically running the first time as a security measure.
5. Upon selecting the menu item this time, after a few seconds, you will see a success message which you can now close.
   
   
3. All done! You have now set up the daily check that will automatically look for any dates that match the review criteria, between 7am - 8am each morning, and send off any relevant emails. This runs automatically in the background each day so you do not need to keep the Sheet open. You can continue to append further staff probations as required without repeating this step.

Download

 

Probation Tracker Tool download (please use 'File' > 'Make a copy' for your own version).

	/**
	* Function to create menu items for Trigger
	*/
	function onOpen() {
	SpreadsheetApp.getUi()
	.createMenu('Admin')
	.addItem('Create Trigger', 'createTrigger') // label for menu item, name of function to run.
	.addItem('Delete Trigger', 'deleteTrigger') // label for menu item, name of function to run.
	.addToUi();
	}
	/**
	* Function to create daily Trigger that will check spreadsheet and initiate any email sending.
	*/
	function createTrigger() {
	try {
	logEvent("Started 'createTrigger' Function.");
	// get all existing Triggers
	var triggers = ScriptApp.getUserTriggers(SpreadsheetApp.getActiveSpreadsheet());
	var triggerLength = triggers.length;
	// loop through each Trigger
	for (var i = 0; i < triggerLength; i++) {
	// get a single Trigger
	var trigger = triggers[i];
	// delete existing Trigger
	ScriptApp.deleteTrigger(trigger);
	logEvent("Deleted an existing Trigger.");
	};
	// create new Daily Trigger
	ScriptApp.newTrigger('probationChecking')
	.timeBased()
	.atHour(7)
	.everyDays(1)
	.create();
	logEvent("Successfully created daily trigger");
	// display popup
	var ui = SpreadsheetApp.getUi();
	result = ui.alert(
	"Setup successful, please now close this dialogue box.",
	ui.ButtonSet.OK);
	logEvent("Completed 'createTrigger' Function.");
	} catch (error) {
	logEvent("Error with 'createTrigger' Function: " + error.stack);
	// display popup
	var ui = SpreadsheetApp.getUi();
	result = ui.alert(
	"Error with setup: " + error.stack,
	ui.ButtonSet.OK);
	};
	}
	/**
	* Main Function.
	*
	* @OnlyCurrentDoc
	*/
	function probationChecking() {
	try {
	logEvent("Started 'probationChecking' Function.");
	// get the spreadsheet
	var ss = SpreadsheetApp.getActiveSpreadsheet();
	// get the sheet
	var sheet = ss.getSheetByName('Probation Tracker');
	logEvent("Successfully got the 'Probation Tracker' Sheet.");
	// get spreadsheet timezone
	var timeZone = ss.getSpreadsheetTimeZone();
	// get Column and call Function to retrieve last row number
	var columnToCheck = sheet.getRange("A:A").getValues();
	var lastRowNo = getLastRowSpecial(columnToCheck);
	// get the data in the spreadsheet
	var data = sheet.getRange(1, 1, lastRowNo, 12).getValues();
	// create variable for today's date to test against in loop
	var today = new Date();
	today.setHours(0, 0, 0, 0);
	var todayTime = today.getTime();
	// create variable for today's date, plus 14 days, to test against in loop
	var todayPlus14 = new Date(Date.now() + 12096e5)
	todayPlus14.setHours(0, 0, 0, 0);
	var todayPlus14Time = todayPlus14.getTime();
	logEvent("Successfully created the today's date variables.");
	// get data length for loop
	var dataLength = data.length;
	// loop through spreadsheet data ********************************
	for (var i = 1; i < dataLength; i++) {
	// log row number
	var rowNo = i + 1;
	logEvent("Current row number is: " + rowNo);
	// get value of 'Probation Passed' column to see if skipping row
	var probationPassed = data[i][11];
	if (probationPassed == "") {
	logEvent("Probation Passed column is empty");
	// create and set empty variables
	var monthNo = '';
	var dueTimePeriod = '';
	var columnNo = '';
	// person up for probation's name
	var fullName = data[i][0];
	// Manager's details
	var managerName = data[i][4];
	var managerEmailAddress = data[i][5];
	var check3month = data[i][7];
	var check6month = data[i][8];
	var check9month = data[i][9];
	var check12month = data[i][10];
	if (check3month == '') {
	logEvent("No 3 month value - no email needed");
	} else if (check3month.getTime() == todayTime) {
	monthNo = 3;
	dueTimePeriod = 'today';
	columnNo = 8;
	} else if (check3month.getTime() == todayPlus14Time) {
	monthNo = 3;
	dueTimePeriod = "in 2 weeks' time";
	columnNo = 8;
	};
	if (check6month == '') {
	logEvent("No 6 month value - no email needed");
	} else if (check6month.getTime() == todayTime) {
	monthNo = 6;
	dueTimePeriod = "today";
	columnNo = 9;
	} else if (check6month.getTime() == todayPlus14Time) {
	monthNo = 6;
	dueTimePeriod = "in 2 weeks' time";
	columnNo = 9;
	};
	if (check9month == '') {
	logEvent("No 9 month value - no email needed");
	} else if (check9month.getTime() == todayTime) {
	monthNo = 9;
	dueTimePeriod = "today";
	columnNo = 10;
	} else if (check9month.getTime() == todayPlus14Time) {
	monthNo = 9;
	dueTimePeriod = "in 2 weeks' time";
	columnNo = 10;
	};
	if (check12month == '') {
	logEvent("No 12 month value - no email needed");
	} else if (check12month.getTime() == todayTime) {
	monthNo = 12;
	dueTimePeriod = 'today';
	columnNo = 12;
	} else if (check12month.getTime() == todayPlus14Time) {
	monthNo = 12;
	dueTimePeriod = "in 2 weeks' time";
	columnNo = 12;
	};
	logEvent("MonthNo is: '" + monthNo + "' and dueTimePeriod is: '" + dueTimePeriod + "' and columnNo is: '" + columnNo + "'");
	if (monthNo != '') {
	// run Function to send email
	var emailSent = sendEmail(fullName, managerName, managerEmailAddress, monthNo, dueTimePeriod);
	if (emailSent) {
	// create date timestamp
	var emailTimestamp = new Date();
	var emailTimestampNice = Utilities.formatDate(emailTimestamp, timeZone, "dd/MM/yyyy, HH:mm:ss");
	logEvent("Email was sent at: " + emailTimestampNice);
	// get relevant cell for updating
	var cell = sheet.getRange(rowNo, columnNo);
	// set cell Note with timestamp for email sending
	cell.setNote("Email sent: " + emailTimestampNice);
	// set cell colour to faded green
	cell.setBackground("#d9ead3");
	} else {
	// error occurred sending email, break out of loop
	break;
	};
	} else {
	logEvent("No email sent");
	};
	} else {
	logEvent("Skipping row as Probation Passed column not empty");
	};
	}
	// loop through spreadsheet data ********************************
	logEvent("Completed 'probationChecking' Function");
	} catch (error) {
	logEvent("Error with 'probationChecking' Function: " + error.stack);
	// run Function to send error email
	sendErrorEmail(error);
	};
	};
	/**
	* Function to get last row number from specified column.
	* Used as future additions to the Google Sheet may include tickboxes which affect 'getRange()'.
	*/
	function getLastRowSpecial(columnToCheck) {
	try {
	logEvent("Started 'getLastRowSpecial' Function.");
	// reset variables before using in loop below
	var rowNum = 0;
	var blank = false;
	// loop through the array and check the value in the cell ****************************
	for (var row = 0; row < columnToCheck.length; row++) {
	// check if cell value is empty AND 'blank' variable is not false
	var rowValue = columnToCheck[row][0];
	if ((rowValue == "") && (!blank)) {
	// if true then set row number variable to value of loop and flag it's true
	rowNum = row;
	blank = true;
	}
	else if (rowValue != "") {
	// if the cell value is not empty (contains data) then flag it's not blank
	blank = false;
	}
	}
	// loop through the array and check the value in the cell ****************************
	logEvent("Last Row is: " + rowNum);
	logEvent("Completed 'getLastRowSpecial' Function.");
	return rowNum;
	} catch (error) {
	logEvent("Error with 'getLastRowSpecial' Function: " + error.stack);
	// run Function to send error email
	sendErrorEmail(error);
	// return error flag
	return false;
	};
	};
	/**
	* Function to send email reminder to Line Manager.
	*/
	function sendEmail(fullName, managerName, emailAddress, monthNo, dueTimePeriod) {
	try {
	logEvent("Started 'sendEmail' Function.");
	// create email subject
	var subject = 'Probation Review due'
	// create email body
	var body = "Hi " + managerName + "\n\n";
	body += "A probation review for " + fullName + " is due " + dueTimePeriod;
	body += " for a " + monthNo + " month review." + "\n\n";
	body += "Thank you";
	// set additional options
	var options = {
	noReply: true
	};
	// send the email
	MailApp.sendEmail(emailAddress, subject, body, options);
	logEvent("Completed 'sendEmail' Function.");
	// return success flag
	return true;
	} catch (error) {
	logEvent("Error with 'sendEmail' Function: " + error.stack);
	// run Function to send error email
	sendErrorEmail(error);
	// return error flag
	return false;
	};
	};
	/*
	Function to output messages to the 'Log' sheet.
	Can be called anywhere else in script.
	*/
	function logEvent(action) {
	// get the relevant spreadsheet to output log details
	var ss = SpreadsheetApp.getActiveSpreadsheet();
	var logSheet = ss.getSheetByName('Log');
	// create and format a timestamp
	var dateTime = new Date();
	var timeZone = ss.getSpreadsheetTimeZone();
	var niceDateTime = Utilities.formatDate(dateTime, timeZone, "dd/MM/yyyy, HH:mm:ss");
	// create array of data for pasting into log sheet
	var logData = [niceDateTime, action];
	// append details into next row of log sheet
	logSheet.appendRow(logData);
	}
	/**
	* Function to send an email if an error in the script occurs.
	*/
	function sendErrorEmail(error) {
	logEvent("Started 'sendErrorEmail' Function.");
	// get link to Google Sheet for error email
	var sheetLink = SpreadsheetApp.getActiveSpreadsheet().getUrl();
	// get email address from user Trigger
	var triggerEmailAddress = Session.getActiveUser().getEmail();
	// send error email
	var subject = 'Error with Probation Review Sheet'
	var body = "Link to Google Sheet file: " + sheetLink + "\n\n";
	body += "Error message: " + error.stack;
	MailApp.sendEmail(triggerEmailAddress, subject, body);
	logEvent("Completed 'sendErrorEmail' Function.");
	};
	/**
	* Function to delete any Triggers.
	*/
	function deleteTrigger() {
	try {
	logEvent("Started 'deleteTrigger' Function.");
	// get all existing Triggers
	var triggers = ScriptApp.getUserTriggers(SpreadsheetApp.getActiveSpreadsheet());
	var triggerLength = triggers.length;
	// loop through each Trigger
	for (var i = 0; i < triggerLength; i++) {
	// get a single Trigger
	var trigger = triggers[i];
	// delete existing Trigger
	ScriptApp.deleteTrigger(trigger);
	logEvent("Deleted an existing Trigger.");
	};
	// display popup
	var ui = SpreadsheetApp.getUi();
	result = ui.alert(
	"Successfully removed, please now close this dialogue box.",
	ui.ButtonSet.OK);
	logEvent("Completed 'deleteTrigger' Function.");
	} catch (error) {
	logEvent("Error with 'deleteTrigger' Function: " + error.stack);
	// display popup
	var ui = SpreadsheetApp.getUi();
	result = ui.alert(
	"Error with removal: " + error.stack,
	ui.ButtonSet.OK);
	};
	}

view raw Probation Tracker Tool.gs hosted with ❤ by GitHub