Update Date or Send Email when a Specific Range is Edited

Question

How do I tell cell C2 to set the date when cells A2 or B2 have been updated? Further, how do I trigger the sending of my email function when A2 or B2 have been updated?

My issue is that onEdit fires anytime the document is edited at all, but I only want an action if a specific range is edited.

For the sending of emails, I've found and edited a script that almost works, but it sends me email notifications whenever any cell in the document is changed rather than just when column G is changed. Any suggestions?

function sendNotification() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var cell = ss.getActiveCell().getA1Notation();
  var row = sheet.getActiveRange().getRow();
  var cellvalue = ss.getActiveCell().getValue().toString();
  var recipients = "[email protected]";
  var message = 'Cell value has been changed';
  if(cell.indexOf('G')!=-1){ 
    message = sheet.getRange('D'+ sheet.getActiveCell().getRowIndex()).getValue()
  }
  var subject = 'Update to '+sheet.getName();
  var body = sheet.getName() + ' has been updated. Visit ' + ss.getUrl() + ' to view     the changes on row: «' + row + '». New comment: «' + cellvalue + '». For message: «' +     message + '»';
  MailApp.sendEmail(recipients, subject, body);
};

Does this have to do with my onEdit() function being off?

For anyone that needs the final scripts

I ended up splitting this up in two separate functions. Here are the finished scripts.

The first one is the email notifications

/* This function send an email when a specified range is edited
 * The spreadsheets triggers must be set to onEdit for the function
*/

function sendNotification() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getActiveSheet();
  //Get Active cell
      var mycell = ss.getActiveSelection();
      var cellcol = mycell.getColumn();
      var cellrow = mycell.getRow();
  //Define Notification Details
      var recipients = ENTEREMAILHERE;
      var subject = "Update to "+ss.getName();
      var body = ss.getName() + "has been updated.  Visit " + ss.getUrl() + " to view the changes.";
  //Check to see if column is A or B to trigger
      if (cellcol == EDITMECOLUMN)
      {
  //check for row to trigger
        if (cellrow == EDITMEROW)
        {
  //Send the Email
      MailApp.sendEmail(recipients, subject, body);
      }
  //End sendNotification
 }
}

And here is the one for time stamps

/* This function saves the date in a cell
 * every time a specific row or column is edited
 * The spreadsheets triggers must be set to onEdit for the function
*/

function setDate() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getActiveSheet();
  //Get Active cell
      var mycell = ss.getActiveSelection();
      var cellcol = mycell.getColumn();
      var cellrow = mycell.getRow();
  //Check to see if column is A or B to trigger
      if (cellcol == EDITMECOLUMN)
      {
  //check for row to trigger
        if (cellrow == EDITMEROW)
        {
  //Find cell and set date in a defined cell
      var celldate = sheet.getRange(EDITMEROW, EDITMECOLUMN);
      celldate.setValue(new Date());
  //end set date
        }
      }
 }

A potential better way to do this is put all your sendmail information in a function with parameters you pass in and then call it in your other function. Right now you are going making the computer go through twice the amount of work you need to do thus slowing your script down. — Kevrone

Kevrone Kevrone · Accepted Answer · 2013-05-14T01:46:03

This should work or at least give you the idea as I do something very similar. I usually set the trigger for this to be onEdit()

   function sendNotification() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getActiveSheet();
       //Get Active cell
      var mycell = ss.getActiveSelection();
      var cellcol = mycell.getColumn();
      var cellrow = mycell.getRow();
      //Check to see if column is A or B to trigger email
      if (cellcol == 1 || cellcol ==2)
      {
        //check for row to trigger email
        if (cellrow ==2)
        {
      //Find cell and set date
      var celldate = sheet.getRange(2, 3);
      celldate.setValue(new Date());
     //end set date
      var cellvalue = mycell.getValue().toString();
      var recipients = "[email protected]";
      var message = 'Cell value has been changed';
      if(cell.indexOf('G')!=-1){ 
        message = sheet.getRange('D'+ sheet.getActiveCell().getRowIndex()).getValue()
      }
      var subject = 'Update to '+sheet.getName();
      var body = sheet.getName() + ' has been updated. Visit ' + ss.getUrl() + ' to view     the changes on row: «' + row + '». New comment: «' + cellvalue + '». For message: «' +     message + '»';
      MailApp.sendEmail(recipients, subject, body);
    }
    }
    }

Update Date or Send Email when a Specific Range is Edited

2 Answers