Send automatic email using Google Sheets & Script

Operational risk (OR) is the risk of loss due to errors, breaches, interruptions or damages—either intentional or accidental—caused by people, internal processes, systems or external events. Automating processes once done by hand can reduce human operational risk

So I will share using Google Sheets & Script to reduce human error (in this case forget to send email reminder) and to reduce administrations task.

Use Case Example :

In HR task, we want to remind line manager to gives new hire the probation review in two weeks before their probation date ended (3 months after join date). You can also use it for collections, send invoices, etc.

In this opportunity I will share on how to create a Google Script that able to send email automatically on specific schedule on Google Sheets.

*This only worked if you are using GSuite / Google Platform.

First, create a Google Sheet for the data.

Below is the example of the data set. In my example case, you’ll need :

1. New Hire Name, 
2. New Hire Email, 
3. Probation End Date (if you have Join Date data you can use formula =JoinDateCell+90)
4. Superior Email
5. Superior Name
6. HRBP or someone that needs to be cc’d
7. Send Email Date / Schedule
8. And a =Today() formula in any cell (in this example I put it in H2)

If you already have New Joiner data that stored in different Sheets, you can use =Query() and =Importrange() formula to query the data in this sheets, so you don’t need to input and edit it manually.

Then, create the script.

In Sheets menu go to Tools -> Script editor , Delete everything and paste the script below

Here's the logic / flow on how this simple script works :

We will create a loop that reads every row, then using simple IF function to check that the date in Send Date Column equal to cell H2 (=Today()). If the condition satisfy, it will trigger the sendEmail() functions.

I will try to break down every line in the script by using // as comment :

function myProbation() {
//by Mirzan
//this function runs based on daily trigger that will be set in project trigger.


//declare variable 
  var ss = SpreadsheetApp.getActiveSpreadsheet();  //declare the spreadsheet
  var sheet = ss.getSheetByName("Sheet1");  //please change according to your sheet name
  var range = sheet.getDataRange(); //to set the range as array
  var values = range.getDisplayValues(); //to get the value in the array
  var lastRow = range.getLastRow();

//array [R][C] started from [0][0] 
  var curDate = values[1][7]; //this to declare cell H2/today() value, [1][7] means cell [2][H]
  var anyMatches = false;
  var message = "";
  
//this to get the email from spreadsheet account
  var email = Session.getActiveUser().getEmail();  
  var optionalEmail = values[2][1]; //this for
  var targetRecipient= "";
  
//now we use FOR function for looping the process
  var i;
  var logContent = '';
  for (i = 5; i < lastRow; i++)  //set variable i as index that start from row 5 and continue down
  {  
    var ProbationDueDate = values[i][6];  //get index value that set the send date

    //Now the IF functions, if today matches the send date, send an email
   //need to convert the date format to string first

    if (ProbationDueDate.toString() == curDate.toString()) 
    {  
      var NewHireName = values[i][0];   	 //[New Hire Name] cell A++
      var NewHireEmail = values[i][1];   	 // [New Hire Email] cell B++
      var SupervisorEmail = values[i][3];     	//[Supervisor Email] cell D++
      var HrbpEmail = values[i][5];		//[HRBP Email] cell F++
      var SupervisorName = values[i][4];	//[supervisor name] cell E++
      var DueDate = values[i][2];		//[probation end date] cell C++
      var SendEmailDate = ProbationDueDate;
      
      //add a message for this row if date matches, this use HTML, you can use html generator
      message = "Dear <b>" + SupervisorName + "</b>," + 
        " <br/><br/>This is a final reminder that your subordinate, <b>" + NewHireName + " </b> 's PROBATION is ended on <b>" + DueDate + 
          " </b> <br/> <br/> Please reply this email to confirm his/her status by latest two week before the probation period end. <br/><br/> **This email is auto-generated <br/><br/> Mirzan";

      // if there is a match, set anyMatches to true so email gets sent
      anyMatches = true;  
      
      // footer for check log message
      logContent += "Content No. " + i  + targetRecipient + " -- " + message + " ++++++++++++++++++++<br/>";
 
     // Now using API to send the email 
    MailApp.sendEmail({
     to: SupervisorEmail,
     cc:  HrbpEmail + "," + NewHireEmail + "," + optionalEmail,
     subject: 'Probation Review Reminder  ' + NewHireName,
     htmlBody: message});  
    }  
  }  // ends for loop
  
  Logger.log(logContent);
}

You can also declare another var for the message using the same variable declaration method above for the dynamic message. Add another column in sheets that stored html format value, delete the message in the script and change it to var message = values[i][x];

After paste, Click SAVE icon on top menu, and try to RUN to check if there any error and give the script access to your account.

Last, set the project trigger Click on trigger / below icon,

Then + Add trigger in right-bottom-corner, and set the trigger to day timer, and at what time you want to send the email

The script will triggered and runs every day, when the SendDate schedule meet TodayDate it will automatically send the message through email.

Hope by using this and you do a little automation in your task can help your daily work easier.