How to set up Email notification workflow in Google Apps Spreadsheets

I often use Google Apps Spreadsheets for collaboration on a document with others. I created a simple custom function that will email me any time someone opens (or alternatively, edits) the spreadsheet, so I know when I should go in to check on changes made by others. My function emails me the name of the spreadsheet, the email address of the user (when possible), and a convenient link to spreadsheet as well. Here’s how:

1. From your Google Spreadsheet, click Tools, script editor.

2. Add the following code, changing the email address (YOUREMAIL@gmail.com) to the address that will receive the notifications:

function sendEmail() {

     //------------------------------------------------------------
     // set the email address you'd like to be notified at below
     var emailAddress = 'YOUREMAIL@gmail.com';
     //------------------------------------------------------------
     
     var editingUser = Session.getActiveUser().getEmail();

     var emailSubject = 'Hey! ('+editingUser+') edited the spreadsheet ' + 
                         SpreadsheetApp.getActiveSpreadsheet().getName();

     var emailBody = 'Link: ' + SpreadsheetApp.getActiveSpreadsheet().getUrl();

     MailApp.sendEmail(emailAddress, emailSubject, emailBody);
}

3. Click Save and give the script a name.
collab_email
4. Test the email function by clicking Run, sendEmail. It will ask you to authorize this function, as it will be sending emails as you. Click Continue, then Accept.
collab_auth_required

collab_requestforpermission
5. Now you need to create a trigger to bind the function to an event (for example, someone opening the spreadsheet). Click Resources, current project triggers.

6. Set up a new trigger. The “Event” should be “From Spreadsheet”, “On Open”. If you like, you can set up an email notification if the trigger fails. Since we tested the trigger function above, this isn’t really necessary.
set up trigger
7. Done! Now close down the spreadsheet, open it up again, and you should receive an email.

Enjoy!

PS: there are a couple caveats with this approach:

1. Google Apps only gives you access to the user who opens the Spreadsheet if you are a Google Apps for Business user and the user is in the same organization as you. If you give access to the spreadsheet to other gmail / Google users or anonymous users, for security purposes it will not be able to tell you who is opening or editing the document.

2. The method above uses installed triggers. Google Apps also has simple triggers, but these are not allowed to do things that require authorization, like send emails, and are therefore not suitable.

Leave a Reply

Your email address will not be published.