Alert by email when input data on google spreadsheet meets given condition

Here is the sample sheet:

make a copy of the sheet

Suppose you want to receive an email when the status column changes to ‘approved’.

Install a trigger first

Note that simple trigger does not work since we need to send an email from the trigger. You have to install the trigger.
Create a function in script editor

function triggerOnEdit(e)
{
  
}

Then go to the menu item: Edit → Current Project’s triggers

Then click on the button Add trigger (the button is in the lower right corner)

For “Choose Which function to run” select our triggerOnEdit

for “event source” select spreadsheet

for “event type” select “OnEdit”

Press the “save” button and then provide the approvals that it asks.

Detect the cell update and send the email

Now that we have a trigger ready, place a check to detect the specific case (column 4 changing to ‘approved’) Then send email only on that case.

Here is the code that does the check:

function checkStatusIsApproved(e)
{
  var range = e.range;
  
  if(range.getColumn() <= 4 && 
     range.getLastColumn() >=4 )
  {
    var edited_row = range.getRow();
    
    var status = SpreadsheetApp.getActiveSheet().getRange(edited_row,4).getValue();
    if(status == 'approved')
    {
      return edited_row;
    }
  }
  return 0;
}
function sendEmailOnApproval(e)
{
  var approved_row = checkStatusIsApproved(e);
  
  if(approved_row <= 0)
  {
    return;
  }
  
  sendEmailByRow(approved_row);
}

function sendEmailByRow(row)
{
  var values = SpreadsheetApp.getActiveSheet().getRange(row,1,row,4).getValues();
  var row_values = values[0];
  
  var mail = composeApprovedEmail(row_values);
  
  //Uncomment this line for testing
  //SpreadsheetApp.getUi().alert(" subject is "+mail.subject+"\n message "+mail.message);
  
  MailApp.sendEmail(admin_email,mail.subject,mail.message);
}

sendEmailOnApproval() first checks the approved status column. Then send email when the condition matches.

See the whole code snippet here

Also See

• Get the whole code snippet
• How to send email when cell value changes in Google Sheets
• How to send email from Google Sheets