Sending Emails from Google Sheets

Our employees often have a need to view, review, or collaborate on documents when they are not in the same office or location. We find Google’s products such as Drive, Docs, Sheets, Slides, and Forms to be great resources that allow us to do just that. Best of all, most of their products are free. Everything is created via a web browser and is stored on Google’s servers. G Suite is a more robust fee based package tailored for businesses if you find you need more.

I recently had a request to update a Google Sheet so that whenever the document was updated, an email would get sent out to multiple recipients notifying them of the change. In this case, we had shared a spreadsheet with a list of job candidates. Many had access, but only 1 person regularly updated. The other users didn’t want to check the document constantly for updates and wanted a notification when a new candidate was added or an existing candidate status had changed.

Option 1 – Simple, but Limited

I first looked into the default options Google offered. I found that each user with edit rights to the spreadsheet can set up their own notifications through Tools > Notification rules.

The user will get notifications of any changes made by someone other than themselves. Pretty simple. I thought that would serve my needs. Not quite.

There are a few problems with this method. First, an email will get sent for any changes such as a corrected misspelling or you changed a 1 to a 2 which would result in a lot of unwanted emails. Second, the email tells you the document was updated, but not what was updated. The user will still have to open the spreadsheet to see the changes. Lastly, you can’t customize the subject line or body.

Below is an example of an email that gets sent.

Option 2 – Advanced, but Customizable and Expandable

I started looking into other options so I could customize the email and also transmit some of the sheet data in the email. You can accomplish this using Google Apps Scripts, a scripting language based on JavaScript. You add and edit code right in your browser, and the scripts run on Google’s servers. This also works with Docs, Sheets, Slides, and Forms.

This method will email the users specified when a new value is inserted or a value is changed in a given column on any sheet. The email has a customized subject line and contains a selection of data from the new line of data that was added.

One caveat: The spreadsheet editor will need to update the first column last as this column is the trigger for sending the email.

To open the script editor in a Google spreadsheet, go to Tools > Script Editor. A new window will open. To create a new script, go to File > New > Script File. Name it something relevant like SendEmails. The window will populate with an empty starter function.

Paste the following script in the window:

function sendEmails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();  
  var url = ss.getUrl();
  var email = Session.getActiveUser().getEmail();
  var docname = ss.getName(); 
  var sheetname = sheet.getName();     
  var mycell = ss.getActiveSelection();
  var cellcol = mycell.getColumn();
  var cellrow = mycell.getRow();
  var dataRange = sheet.getRange(cellrow, 1, 1, 16);
  var data = dataRange.getValues();
  for (i in data) {
    var column = data[i];
    var emailSendTo = "example@email.com";        
    var message = "Position: " + sheetname + "\n\n"
    + "Applicant Name: " + column[2] + "\n\n"
    + "Status: " + column[0] + "\n\n" 
    + "Updated by: " + email  + "\n\n"    
    + "To view the complete changes visit: " + url;  
    var subject = sheetname + " Candidate Status Update";     
    if (cellcol == 1)
    {       
    MailApp.sendEmail(emailSendTo, subject, message);
    }
  }
}

Next, we need to set a trigger so that the emails will send when the spreadsheet is edited.

1. Go to Edit > Current Project Triggers
2. Select ‘Add a New Trigger’
3. Under Run, select your function. In this case, SendEmails.
4. Under Events, select ‘From spreadsheet’ ‘On edit’
5. Save

Some explanation on the script:

These variables define and retrieve the URL, sheet, cell, and user’s email of the active document.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var url = ss.getUrl();
var email = Session.getActiveUser().getEmail();
var docname = ss.getName();
var sheetname = sheet.getName();
var mycell = ss.getActiveSelection();
var cellcol = mycell.getColumn();
var cellrow = mycell.getRow();

This fetches the range of cells specified:

var dataRange = sheet.getRange(cellrow, 1, 1, 16);

This fetches values for each row in the Range:

var data = dataRange.getValues();

This defines notification details. Customize as needed. You can add multiple emails separated by a comma. You can change the subject line or add additional columns.

var emailSendTo = "example@zekiah.com";        
var message = "Position: " + sheetname + "\n\n"
+ "Applicant Name: " + column[2] + "\n\n"
+ "Status: " + column[0] + "\n\n" 
+ "Updated by: " + email  + "\n\n"    
+ "To view the complete changes visit: " + url;  
var subject = sheetname + " Candidate Status Update";

This will check if a value is changed or a new value is inserted into column A, and if so, sends the email. Change the column number if you want to change the trigger to another column. In my example, column A is the status column.

if (cellcol == 1) 
{       
MailApp.sendEmail(emailSendTo, subject, message);
}

If you have a Google form that is collecting info from users into a spreadsheet, you can set up the trigger to send an email on form submit. As a simple alternative you can also set up email notifications from the responses tab in your form. The result is similar to option 1 above, as the email is only sent to the form owner and doesn’t add the details in the email.

To customize further, explore all the classes you can use to access and modify Google sheets.

This post was written by Fawn.

Contact us for more information on this post or our services.