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 = "me@email.com";
  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, May 14 '13 at 23:32

score 0 · Answer 1 · edited May 23 '17 at 12:15

0

Your onEdit(event) needs to check the event.range and make decisions based on that.

See Understanding Events.

Here's an answer to a similar question.

edited May 23 '17 at 12:15

Community

1
1

answered May 13 '13 at 19:19

Mogsdad

44,709
21
151
275

score 0 · Accepted Answer · answered May 14 '13 at 01:46

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 = "me@email.com";
      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);
    }
    }
    }

This looks perfect, but when I adjust it to the columns I want and do a test run from the script editor, I get an error a reference error on this line if(cell.indexOf('D')!=-1){ The timestamp part is working perfectly. — jredeker, May 14 '13 at 15:35
I don't see indexOF as a part of the range class. You should be using cellvalue not cell is the first thing I see. cellvalue.indexOf('G') != -1 which will check for the letter "G" in the string to see if it exists. To clarify in order to do indexOF you need to have a string which we have. We set cellvalue = mycell.getValues.toString earlier for whatever cell was being edited(the if makes sure it's A or B row 2)f — Kevrone, May 14 '13 at 23:11

Update Date or Send Email when a Specific Range is Edited

2 Answers2