0

OK, so I figured how my question isn't a duplicate, and how what I am looking for isn't resolved in the links given.

This is as close as I can get:

      function sendNotification() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Incoming POs");
  var cell = ss.getActiveCell().getA1Notation();
  var row = sheet.getActiveRange().getRow();
  var cellvalue = ss.getActiveCell().getValue().toString();
  var recipients = "test@gmail.com";
  var message = '';
  if(cell.indexOf('K')!=-1){ 
    message = sheet.getRange('A'+ sheet.getActiveCell().getRowIndex()).getValue()
   }
  var subject = 'PO# '+ sheet.getRange('A'+ sheet.getActiveCell().getRowIndex()).getValue() + ' has been received';
  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);
};

The problem with this code is that I need the notification to be sent ONLY if a row has it's F column value changed to "Yes". This code sends a notification any time anything is changed in column F.

Does that make sense? Thanks for the help! This is close, I just can't figure out how to incorporate required text contains rules.

(Original text from original question: 'm developing a new Google Sheet that three different people will use/share to collaborate on some of their work. What I'm really after is writing a notification script that will email other stakeholders when any cell in column F gets its value changed to "Yes" in Sheet2 of the master sheet (I mean "workbook" because the terminology for Google sheets is confusing vs Excel because there are workbooks and sheets, but it seems like there are just sheets...and sheets in Google).

I want the email to contain some text, along with the value of cell A in the row that has the F column value changed.)

Lonememe
  • 37
  • 3
  • 9
  • Much more like http://stackoverflow.com/questions/15336907/email-notification-if-cell-is-changed. And I'm sure there are others! – Mogsdad Apr 30 '15 at 03:24
  • Dang! I thought I searched for this problem already like a good forum user, but I guess I didn't have the right search terms or something. I'll check these links out. Sorry for the dup question! – Lonememe Apr 30 '15 at 16:29
  • 1
    You say the code is close; what isn't it doing correctly? Don't you just need to check the `cellValue` you've parsed out to see if they typed in 'yes', and then not send the message if it's not 'yes'? – Patrick M Apr 30 '15 at 19:28
  • I can get it to send emails when *any* change is made to column K, but I can't get it to send emails when K is changed from "No" to "Yes". I have tried changing the if statement to `if(cell.indexOf('K')!=-1) && cellvalue=='Yes'` but that still just sends an email any time any change is made. Still so unbelievably stuck on this... – Lonememe Apr 30 '15 at 22:22

1 Answers1

0

You should set an onEdit(e)( runs when a user changes a value in a preadsheet.) trigger to recognize the edit.

In the same function you can get the event object which has the details of the cell that has been edited. Then you use sheet.getRange() to get the range of values to be added in the email message.

The result set from the getRange method would be a two dimensional array.

Hope that helps!

KRR
  • 4,647
  • 2
  • 14
  • 14