https://www.pbainbridge.co.uk/2024/03/probation-tracker-tool.html

Probation Tracker Tool.gs

/**
 * 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);

  };


}