1

I have a work schedule in a Google Spreadsheet and I need it to automatically send each person on the schedule an email if their schedule changes. I created an additional sheet for each person that has only their schedule in it (these update automatically based on changes in the original schedule). How can I alter my script so that it will detect a change in one of the sheets (e.g. the sheet named "Todd") and send an email to him? Below is what I have so far...

I've been battling with this script for a few weeks and I can't find a definitive answer anywhere, so I'm posting my own question (sorry, I'm pretty inexperienced, so bear with me). 

function sendNotification() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Todd');
  var range = sheet.getActiveRange().getA1Notation();
  var recipients = "Todd@XXXXX.com";
  var message = '';
    function createSpreadsheetChangeTrigger() {
    var ss = SpreadsheetApp.getActive();
    ScriptApp.newTrigger('onChange')
      .forSpreadsheet(ss)
      .onChange()
      .create();
    }  
  var subject = 'Update to Work Schedule';
  var body = 'Todd, your schedule has changed. Visit ' + ss.getUrl();
  MailApp.sendEmail(recipients, subject, body);
};
Branham Snyder
  • 11
  • 1
  • 2

1 Answers1

1

In the Script Editor you will need to add a trigger in "Resources -> Current project's triggers" that triggers your sendNotification() function "on change". You can specify additional conditions within the script itself by using google's trigger functions:
https://developers.google.com/apps-script/guides/triggers/#onedit

rsynnest
  • 149
  • 4
  • Thanks for the quick reply rsynnest! I actually do already have my project triggers all set up correctly. The problem is that it only emails me when I hit "run" in Script Editor, not when a change is made in my sheets. Also, the reason I set up separate sheets for every person is so that when changes are made it will email only the person whose schedule has changed, not everyone. Might there be a simpler way of doing this? Maybe get rid of individual sheets, and only have one sheet and assign a range of cells to each person, and set my script to email that person if their cells change? – Branham Snyder Jan 24 '16 at 20:07
  • Ah, I didn't notice the API trigger call in your script. I've copied your code verbatim, and it works perfectly. Your problem may be due to multiple Sheets. What I would do is use a single sheet with an "email_address" column. Then send email notifications to the address in that column by referencing it in your script. – rsynnest Jan 28 '16 at 23:58
  • It should be working though. Are you sure you've enabled the onChange trigger for sendNotification() in your project triggers menu?. It won't work if you only specify the trigger in the sendNotification function code, because the sendNotification function will never get called unless it is triggered. – rsynnest Jan 29 '16 at 00:16
  • Yep, the onChange trigger is definitely enabled. Maybe it's the multiple sheets. I'll try what you suggested with a single sheet and an email address column. – Branham Snyder Feb 01 '16 at 17:04