Send email notification using multiple sheet tabs when a cell value change

Question

Below function works well but I want to add 2nd sheet which have same number of columns and rows. The logic is the same when user select 'Yes' in column 7. How do I get to send email if user select 'yes' in Sheet2. I did create duplicate function and name sheet as 'Sheet2'. When I click 'Yes' in Sheet2, I get a email notification for Sheet1 and Sheet2 even though Sheet1 is not selected 'Yes'. If user select 'Yes' in Sheet1, it should only get email from Sheet1 and likewise in Sheet2. How do I go about it?

function sendNotification(e){
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getSheetByName('Sheet1');
   var editedRange = e.range;
   if(sheet.getName()=='Sheet1' && editedRange.getColumn()==7 && e.value=='Yes'){
        var row = editedRange.getRow();
        var numRows = sheet.getLastRow()-1;
        var row_values = sheet.getRange(row, 1, numRows, 7).getValues()[0];
        var first_name = row_values[0];
        var last_name = row_values[1];
        var company = row_values[3];
        var email= row_values[5];
        var message = "The person has completed Sheet1"+first_name+" "+last_name+ " - 
                      "+company;
        var subject = "Completed - "+first_name+" "+last_name;

 MailApp.sendEmail(email, subject, message);
 }
}

Rubén Rubén · Accepted Answer · 2021-02-05T18:54:44

Replace

var sheet = ss.getSheetByName('Sheet1');

by

var sheet = e.range.getSheet();

and replace

if(sheet.getName()=='Sheet1' && editedRange.getColumn()==7 && e.value=='Yes'){

by

if(['Sheet1', 'Sheet2'].indexOf(sheet.getName()) > -1 && editedRange.getColumn()==7 && e.value=='Yes'){

['Sheet1', 'Sheet2'].indexOf(sheet.getName()) will return 0 or 1 if sheet.getName() returns 'Sheet1' or 'Sheet2', respectively and -1 in any other case.

NOTE: You can remove var ss = SpreadsheetApp.getActiveSpreadsheet();

Send email notification using multiple sheet tabs when a cell value change

1 Answers