Overview

Add Email Notification For A Sheet

More Information

In this tutorial see how to add an email notification system to a google sheet. In this use case a school wanted to be able to send push notification to a group of users if certain changes were made to an athletic calendar.

1. On the spreadsheet add a new sheet named Email List or something similar. This sheet has two columns, Email Address and Message.
2. The message column in this case can just be a link to the website that had the calendar
3. Add as many emails as you like. If you are in a google domain, build a google group with all the users you want to email (a group has a single email address that then emails all the group members). In this example the first email address is a group which will email multiple people.
4. Copy and Add this script to your sheet under Tools/Script editor. When the script editor opens highlight the text that is in it and replace it with this script.

______________________________________________________________________________

// This creates the Menu and the Menu Items

function onOpen() {

var ui = SpreadsheetApp.getUi();

ui.createMenu('Send Emails')

.addItem('Email Coaches', 'sendEmails')

.addToUi();

}

function sendEmails() {

var sheet = SpreadsheetApp.getActive().getSheetByName('emaillist')

var startRow = 2; // First row of data to process

var numRows = 2; // Number of rows to process

// Fetch the range of cells A2:B3

var dataRange = sheet.getRange(startRow, 1, numRows, 2)

// Fetch values for each row in the Range.

var data = dataRange.getValues();

for (i in data) {

var row = data[i];

var emailAddress = row[0]; // First column

var message = row[1]; // Second column

var subject = "The Athletic calendar has been updated";

MailApp.sendEmail(emailAddress, subject, message);

}

}