Is there a way to have a email sent if a certain cell contains a set word?

15 Views Asked by At

I have been working to send a email out automatically if a certain phrase is put in cell. We have found a formula but it sends out out a email even if the cell contains other words. Can you help out. Here is the current formula minus email addresses for security

function equipmentIssueTest() {
  // Spreadsheet, Model, Serial, and Location Variables.
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Responses")

  var Direction = SpreadsheetApp.Direction;
  var equipmentModelLast = ss.getRange("F"+(ss.getLastRow()+1)).getNextDataCell(Direction.UP).getValue();
  var equipmentSerialLast = ss.getRange("G"+(ss.getLastRow()+1)).getNextDataCell(Direction.UP).getValue();
  var buildingName = ss.getRange("C"+(ss.getLastRow()+1)).getNextDataCell(Direction.UP).getValue();

  var equipmentBad = ss.getRange("H"+(ss.getLastRow()+1)).getNextDataCell(Direction.UP).getValue();
  
  if (equipmentBad == "Equipment Issue");{
    // Fetch the email address
    var emailAddress = "[email protected]";
    var emailAddress2 = "[email protected]";
  
    // Send Alert Email.
    var message = 'Location: ' +buildingName+ '. An equipment issue has been reported for Model: ' +equipmentModelLast+ ' Serial: ' +equipmentSerialLast+ '. Please address at your earliest convenience.'; // Second column
    var subject = 'Equipment Issue';
    GmailApp.sendEmail(emailAddress, subject, message);
    GmailApp.sendEmail(emailAddress2, subject, message);
  
    }
}
  1. We have used formula to get emails sent out and have worked on sheets getting them sent it is just we need specific phrase to send out email and not if there is something else in the email.
0

There are 0 best solutions below