CODEWITHSUNDEEP
google-sheetsonchangeemail-notifications
Branham Snyder
Branham Snyder

Reputation: 11

Google Sheets Scripts: email notifications when changes made in a specific sheet

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 = "[email protected]";
  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);
};

Upvotes: 1

Views: 3111

Answers (1)

rsynnest
rsynnest

Reputation: 169

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

Upvotes: 1

Related Questions