Google script sending email containing information from wrong sheet-CodePudding

I have a Google Sheet containing three sheets. Sheet 1 is an information page and doesn't get updated. Sheets 2 and 3 are similar, there's just a few differences in the data the columns contain and Sheet 3 has one extra column.

There's separate scripts in place for Sheets 2 and 3 that sends an email each time a row of data is added. The scripts are the same, they just refer to different columns and sheet name. They run fine and the emails send with no problems, however when a row of data is entered into Sheet 3, the script also sends an email relating to Sheet 2, referring to the same row number as Sheet 3 and I can't figure out why. I don't work with scripts that often, and have pieced these scripts together through various online searches and help from on here and other forums.

This is the script for Sheet 2:

var Notified = "Notified";
function sendPASSNotification(event){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("PASS Profile");
  var row = sheet.getActiveRange().getRow();
  //var cellvalue = ss.getActiveCell().getValue().toString();
   if(event.range.getA1Notation().indexOf("J") > -1 && sheet.getRange("Q"   row).getDisplayValue() != "Notified")
  {
  Utilities.sleep (30000) 
  var rowVals = getActiveRowValuesPASS(sheet);
  var aliases = GmailApp.getAliases();
  Logger.log(aliases);
  var bodyHTML,o,sendTO,subject;
  o = {};
  bodyHTML = "There has been a new PASS Profile created by "   rowVals.plannername   " in the "   rowVals.team   " team. The reason for the PASS Profile is:<br \> <br \>"
    "<i><b>"   rowVals.reason   ".</i></b>"  "<br \> <br \>"
    "<table border = \"1\" cellpadding=\"10\" cellspacing=\"0\"><tr bgcolor=#d9d2e9><th>Row</th><th>Layout Group</th><th>Items Affected</th><th>Location</th><th>Start Date</th><th>End Date</th><th>Cover</th></tr><tr><td align = center bgcolor = #ebb044>" rowVals.row "</td><td align = center>" rowVals.layout "</td><td align = center>" rowVals.items "</td><td align = center>" rowVals.location "</td><td align = center>" rowVals.start "</td><td align = center>" rowVals.end "</td><td align = center>" rowVals.cover "</td></tr></table>" 
         "<br \>To view the full details of the request, use the link below.<br \> <br \>"   
       "<a href=\"https://docs.google.com/spreadsheets\">PASS Profile</a>"
        "<br \> <br \><i><b>This is an automated email.</i>";
o.htmlBody = bodyHTML;
o.from = aliases[0]; 
sendTO = "[email protected]";
subject = "New PASS Profile created"

GmailApp.sendEmail(sendTO,subject,"",o);
sheet.getRange("Q"   row).setValue(Notified);
  }
}

function getActiveRowValuesPASS(sheet){
  var cellRow = sheet.getActiveRange().getRow();
  var layoutCell = sheet.getRange("E"   cellRow);
  var layout = layoutCell.getDisplayValue();
  var itemsCell = sheet.getRange("F"   cellRow);
  var items = itemsCell.getDisplayValue();
  var locationCell = sheet.getRange("G"   cellRow);
  var location = locationCell.getDisplayValue();
  var startCell = sheet.getRange("H"   cellRow);
  var start = startCell.getDisplayValue();
  var endCell = sheet.getRange("I"   cellRow);
  var end = endCell.getDisplayValue();
  var coverCell = sheet.getRange("J"  cellRow);
  var cover = coverCell.getDisplayValue();
  var plannernameCell = sheet.getRange("O"   cellRow);
  var plannername = plannernameCell.getDisplayValue();
  var teamCell = sheet.getRange("C"   cellRow);
  var team = teamCell.getDisplayValue();
  var reasonCell = sheet.getRange("D"   cellRow);
  var reason = reasonCell.getDisplayValue();  
  return {
    layout: layout,
    items: items,
    location: location,
    start: start,
    end: end,
    cover: cover,
    plannername: plannername,
    team: team,
    row: cellRow,
    reason: reason
  } }

And this is the script for Sheet 3:

 var Notified = "Notified";
function sendSSNotification(event){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sssheet = ss.getSheetByName("Sheet 3");
  var row = sssheet.getActiveRange().getRow();
   if(event.range.getA1Notation().indexOf("K") > -1 && sssheet.getRange("S"   row).getDisplayValue() != "Notified")
  {
  Utilities.sleep (30000) 
  var rowVals = getActiveRowValuesSS(sssheet);
  var aliases = GmailApp.getAliases();
  Logger.log(aliases);
  var bodyHTML,o,sendTO,subject;
  o = {};
  bodyHTML = "There has been new SS created by "   rowVals.plannername   " in the "   rowVals.team   " team. The reason for the SS is:<br \> <br \>"
    "<i><b>"   rowVals.reason   ".</i></b>"  "<br \> <br \>"
    "<table border = \"1\" cellpadding=\"10\" cellspacing=\"0\"><tr bgcolor=#cfe2f3><th>Row</th><th>Layout Group</th><th>Items Affected</th><th>Location</th><th>End Date</th><th>Cases or Cover</th><th>Value</th></tr><tr><td align = center bgcolor = #ebb044>" rowVals.row "</td><td align = center>" rowVals.layout "</td><td align = center>" rowVals.items "</td><td align = center>" rowVals.location "</td><td align = center>" rowVals.end "</td><td align = center>" rowVals.casescover "</td><td align = center>" rowVals.cover "</td></tr></table>" 
         "<br \>To view the full details of the request, use the link below.<br \> <br \>"   
       "<a href=\"https://docs.google.com/spreadsheets\">SS</a>"
        "<br \> <br \><i><b>This is an automated email.</i>";
o.htmlBody = bodyHTML;
o.from = aliases[0]; 
sendTO = "[email protected]";
subject = "New SS created"

GmailApp.sendEmail(sendTO,subject,"",o);
sssheet.getRange("S"   row).setValue(Notified);
  }
}

function getActiveRowValuesSS(sssheet){
  var cellRow = sssheet.getActiveRange().getRow();
  var layoutCell = sssheet.getRange("E"   cellRow);
  var layout = layoutCell.getDisplayValue();
  var itemsCell = sssheet.getRange("F"   cellRow);
  var items = itemsCell.getDisplayValue();
  var locationCell = sssheet.getRange("G"   cellRow);
  var location = locationCell.getDisplayValue();
  var casescoverCell = sssheet.getRange("J"   cellRow);
  var casescover = casescoverCell.getDisplayValue();
  var endCell = sssheet.getRange("H"   cellRow);
  var end = endCell.getDisplayValue();
  var coverCell = sssheet.getRange("K"  cellRow);
  var cover = coverCell.getDisplayValue();
  var plannernameCell = sssheet.getRange("Q"   cellRow);
  var plannername = plannernameCell.getDisplayValue();
  var teamCell = sssheet.getRange("C"   cellRow);
  var team = teamCell.getDisplayValue();
  var reasonCell = sssheet.getRange("D"   cellRow);
  var reason = reasonCell.getDisplayValue();  
  var daysCell = sssheet.getRange("L"   cellRow);
  var days = daysCell.getDisplayValue;
  return {
    layout: layout,
    items: items,
    location: location,
    casescover: casescover,
    end: end,
    cover: cover,
    plannername: plannername,
    team: team,
    row: cellRow,
    reason: reason,
    days: days
  } }

These are executed via onEdit triggers.

Long term I need to add a fourth sheet that will also send an email upon data being entered, but need to fix this issue first.

CodePudding user response：

In your logic if(event.range.getA1Notation().indexOf("J") doesn't limit it to column J of PASS Profile but any sheet. If you do want to limit it you should add a check.

In Script 2 add:

if( event.range.getSheet().getName() !== "PASS Profile" ) return;
if(event.range.getA1Notation().indexOf("J") > -1 && sheet.getRange("Q"   row).getDisplayValue() != "Notified")

Similarly for Script3:

if( event.range.getSheet().getName() !== "Sheet 3" ) return;
if(event.range.getA1Notation().indexOf("K") > -1 && sssheet.getRange("S"   row).getDisplayValue() != "Notified")