Today we are going to learn how to automate email sending process for a set of projects and their project owners.

Say, for example, we have 5 columns starting from Column A to Column E. Below are the contents of these Columns;

• Column A: Serial No
• Column B: Name
• Column C: Email ID
• Column D: Project
• Column E: Due Date

You need to create an automation using Google Apps Script that can send automated email reminders to the Person, whose name is in Column B, so that they can complete the Audit Work and can submit the work to the clients.

Having said that, you will use below code to achieve the result;

function sendEmailNotifications() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  
  for (var i = 1; i < data.length; i++) {
    var name = data[i][1];
    var project = data[i][3];
    var email = data[i][2];
    var dueDate = data[i][4];
    
    if (name && project && email && dueDate) {
      var subject = "Project Reminder: " + project;
      var message = "Hi " + name + ",\n\n" +
                    "Greetings of the Day,\n\n" +
                    "This is a gentle reminder that, your project named " + project + " has a due date of " + dueDate + ". " +
                    "Please complete the Audit requirements as soon as possible.\n\n" +
                    "Thanks & Regards,\nAudit Team";
      
      MailApp.sendEmail(email, subject, message);
    }
  }
}
 

Now, let's try to understand what each line of code is actually achieving;

1. 'function sendEmailNotifications() {: This line starts the definition of a custom function named sendEmailNotifications. It's like giving a name to a set of instructions that we want to follow.
2. var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();: This line gets the currently active Google Sheets document and selects the currently active (visible) sheet in that document. Think of it as focusing on the Excel sheet you're currently looking at.
3. var data = sheet.getDataRange().getValues();: This line gets all the data from the active sheet and stores it in the variable data. It's like taking all the information on that Excel sheet and putting it into a box called data.
4. for (var i = 1; i < data.length; i++) {: This line starts a loop. It's like reading each row of the Excel sheet one by one, starting from the second row (because the first row usually contains headers).
5. var name = data[i][1];: This line takes the information in the first column (column B) of the current row and stores it in a variable called name. It's like noting down the name in that row.
6. var project = data[i][3];: This line does the same thing but for the project name in the third column (column D) of the current row.
7. var email = data[i][2];: This line does the same but for the email address in the second column (column C) of the current row.
8. var dueDate = data[i][4];: This line does the same but for the due date in the fourth column (column E) of the current row.
9. if (name && project && email && dueDate) {: This line checks if all the required information (name, project, email, and due date) is available in the current row. If any of these pieces of information are missing, it skips to the next row. It's like making sure you have all the necessary details before proceeding.
10. var subject = "Project Reminder: " + project;: This line creates a subject line for an email reminder. It combines "Project Reminder: " with the project name and stores it in the variable subject. It's like writing a subject line for your email.
11. var message = "Hi " + name + ",\n\n" + ...: This line creates the email message. It combines various pieces of information (name, project name, due date, and a message) to create a complete email message. It's like composing the content of the email.
12. MailApp.sendEmail(email, subject, message);: This line sends the email to the recipient's email address (found in the email variable) with the specified subject and message. It's like clicking the "Send" button in your email client to send the email.
13. }: This closing brace marks the end of the if statement and the end of the loop. It's like saying, "We're done with this row; let's move on to the next one."
14. }: This closing brace marks the end of the for loop, indicating that we're done processing all the rows in the sheet.

In simple terms, this code reads data from your Google Sheets, checks if it has all the necessary information for each row, and then sends email reminders to people about their projects' due dates. It does this for each row in the sheet, one at a time.

Can we make this process a bit simple for our clients?
To make the process simple for our clients, we will have to add a custom menu item on top of the google sheets known as Email Menu. To achieve this result, we can use below code above the previous code.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Email Menu')
      .addItem('Send Email Notifications', 'sendEmailNotifications')
      .addToUi();
}
 

Once we have added this code line, you can select onOpen() function from function selector and then click on run. This will create a simple menu item in our google sheet that can be used to run sendEmailNotifications() function and to trigger the emails.

We can even use triggers to automate the overall process by sending reminders to the project managers on a weekly or fortnightly basis, which will again help our project managers in keeping a track of all the projects that are in process.