Send email when any cell in column is changed to 'YES'

1
votes

I have a spreadsheet with a Yes/No column (column AP) and I need to send an email notification whenever any value within that column is changed. I have worked out how to send one when a specific cell is changed:

function sendNotification(e) {


if("AP4" == e.range.getA1Notation()) {
if(e.value == "YES") {



 //Define Notification Details
 var recipients = "***********@gmail.com";
 var subject = "Update"+e.range.getSheet().getName();
 var body = "This cell has changed";

 //Send the Email
 MailApp.sendEmail(recipients, subject, body);
}
}
}

However, I need this to work for any cell within column AP.

I also then need to store other values with the changed row as variables to use within the email body. So for example, the product name is in column B, and I need to be able to access this name so that my message reads something like "Column AP has been changed to 'Yes' for " + productName. Any help would be received very gratefully.

1
emailgoogle-apps-scriptgoogle-sheets

1 Answers

2
votes

Try this:

function sendNotification(e){
if(e.range.getColumn()==42 && e.value=='YES'){
 var recipients = "***********@gmail.com";
 var subject = "Update"+e.range.getSheet().getName();
 var body = "This cell has changed";
 var valColB=e.range.getSheet().getRange(e.range.getRow(),2).getValue();
 MailApp.sendEmail(recipients, subject, body)
 }
}