In my last article, I spoke about how Google Sheets can easily integrate with multiple services. One useful implementation of this is sending email via Sheets, more specifically Gmail.

Combining the email function with data processing and triggers built into Sheets can lead to some powerful automation.

Class MailApp allows users to send emails with complete control over the content of the email. Unlike GmailApp, MailApp's sole purpose is sending email. MailApp cannot access a user's Gmail inbox.

Code

var msg = "Hi, this is a notification email. \n \n This is the second line of the email."

MailApp.sendEmail("To-Email-Address", "Subject", msg, {name:"From-Name"});

Note that you will not need enter your Gmail login details in the code itself. Since every Google Sheet is linked with a Gmail ID, there is the added advantage of not requiring any authentication in the code.

Sample Use Case

You maintain your company invoice data on Google Sheets, and would like to periodically check which invoices are unpaid. Then, you want to send an email reminder to your client, along with the necessary details.

function Email() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Invoices");    //refer to sheet that contains data                         
  var lastRow = sheet.getLastRow();        

    for (var i=2; i <= lastRow; i++)    //loop through the entire list of invoices
  {
    var inv = sheet.getRange(i,6).getValue();    //get status of invoice

    if(inv == "Not Paid")
    {
      sheet.getRange(i,6).setBackground("Pink");

      var to_name = sheet.getRange(i,3).getValue();
      var to_email = sheet.getRange(i,4).getValue();
      var msg = "Hi " +to_name + ", \n This is a notification email for an unpaid invoice."

      MailApp.sendEmail(to_email, "Invoice Pending", msg, {name:"Finance Team"});
      }
  }

}

How to setup on Google Sheets

1. Have your invoice data on a sheet

2. In the menu bar, click on Tools > Script Editor, where you will type in your code

3. Setup a trigger to check for pending invoices every morning

Inside Script Editor, click on Triggers in the side-menu. Set the trigger to fire up the code every morning between 6 AM to 7 AM.

4. Run the script

Click on Run in the top menu to execute the code.

Note that on the first run, you will be asked to approve the function linked to your Gmail ID.

Here is a sample implementation of the same. You will not be able to edit the Sheet, so make a copy to proceed.