Google Sheets - Send email based on cell matching True or False

1
votes

I don't work with Google Sheets very often and mainly deal with Google Form's script editor. We have created a database from various forms that fill into one spreadsheet and I would like to send email notifications to specific managers based on the recent row update, which would update from a form submission. Here is what I was trying to get it to do:

if cell in Column P matches to "FALSE":

  • send email
  • set the recipient field as "[email protected]" if cell in Column J matches to the word "Building 1"
  • set the CC field as "[email protected]" if the cell in Column I matches to the word "Department 1"

if Column P matches to "TRUE", then it doesn't have to do anything

function triggerOnEdit(e)
{
  alertManagers(e);
}


function checkClearance(e)
{
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName("Calculations");
  var value = sheet.getRange("P:P").getValue();
  var range = sheet.getRange(1, 1, sheet.getLastRow(), 1); //understands spreadsheet
  
  if(range.getColumn() <= 16 && 
     range.getLastColumn() >=16 )
  {
    var edited_row = range.getRow();
    
    var approval = SpreadsheetApp.getActiveSheet().getRange(edited_row,16).getValue();
    if(approval == 'FALSE')
    {
      return edited_row;
    }
  }
  return 0;
}



function alertManagers(e)
{
  var clearance_row = checkClearance(e);
  
  if(clearance_row <= 0)
  {
    return;
  }
  
  sendEmailByRow(clearance_row);
}

function sendEmailByRow(row)
{
  var values = SpreadsheetApp.getActiveSheet().getRange(row,1,row,16).getValues();
  var row_values = values[0];
  
  var mail = composeAlertEmail(row_values);
  var manageremail='[email protected]';

  //Uncomment this line for testing
  //SpreadsheetApp.getUi().alert(" subject is "+mail.subject+"\n message "+mail.message);
  
  MailApp.sendEmail(manageremail,mail.subject,mail.message);
  

}



function composeAlertEmail(row_values)
{
  var name = row_values[6];
  
  var email = "[email protected]";

  var message = "Good day, \n The following employee does not have clearance to enter the building: "+name+
    " email "+email;
  var subject = "Employee Not Cleared: "+name+" "
  
  return({message:message,subject:subject});
}

Does this seem possible? I know I am probably far off from a solution, but thanks if anyone can help in advance!

1
google-apps-scriptgoogle-sheetsgmailgoogle-sheets-apispreadsheet
For documentation reasons please accept the answer or provide your feedback in order to modify it and solve your question. Just click on the tick button the left of the answer. – soMario

1 Answers

1
votes

Solution:

Here is what you are looking for:

function triggerOnEdit(e) {
sendEmails(e)
}

function sendEmails(e){
  const row = e.range.getRow();
  const col = e.range.getColumn();
  const as = e.source.getActiveSheet();
  const subject = "This is the subject of the email";
  const body = "This is the body of the email";
  const to = "[email protected]";
  const cc = "[email protected]";
  
  if(as.getName() == "Calculations" && col == 16 && as.getRange(row,col).getDisplayValue() == "FALSE") {  

   if (as.getRange(row,10).getValue()=="Building 1"){

      if(as.getRange(row,9).getValue()=="Department 1"){
   
         MailApp.sendEmail({to:to ,subject: subject,body:body,cc:cc});
      }
      else {
         MailApp.sendEmail({to: to,subject: subject,body:body});
      }
   
   }      
  }
}

I assume that if column J does not match Building 1 which means there is no recipient, the email won't be sent.

Don't forget to add triggerOnEdit(e) to the current project's trigger:

project