Sample code sending email when Google Sheet is updated

email-on-update.js

var admin_email='youremailaddress@gmail.com'; //<- update your email address here

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

function showMessageOnApproval(e)
{
  var edited_row = checkStatusIsApproved(e);
  if(edited_row > 0)
  {
    SpreadsheetApp.getUi().alert("Row # "+edited_row+" approved!");
  }
}

function showMessageOnUpdate(e)
{
  var range = e.range;
  
  SpreadsheetApp.getUi().alert("range updated " +   range.getA1Notation());  
}

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);
  
  var candidate_email = composeCandidateEmail(row_values);
  
   MailApp.sendEmail(candidate_email.email,candidate_email.subject,candidate_email.message);
}



function composeApprovedEmail(row_values)
{
  var first_name = row_values[0];
  
  var last_name = row_values[1];
  
  var email = row_values[2];

  var message = "The following applicant is approved: "+first_name+" "+last_name+
    " email "+email;
  var subject = "Applicant approved "+first_name+" "+last_name
  
  return({message:message,subject:subject});
}

function composeCandidateEmail(row_values)
{
  var first_name = row_values[0];
  
  var last_name = row_values[1];
  
  var email = row_values[2];

  var message = "The following applicant is approved: "+first_name+" "+last_name+
    " email "+email;
  var subject = first_name+ ", Your appliation is approved";
  
  var message = "Hello "+first_name+"\n"+
    "Your application is approved.\n Please follow the following instructions to proceed with the process.\n";
    //... etc
     
  
  return({message:message,subject:subject, email:email });
}

I experienced a similar challenge with the "range not defined error. I went off to tweak the code and combined it with some other code snippets. I came up with this version that worked for me. https://github.com/TSiwale/AppsScripts/blob/main/A%20script%20to%20send%20email%20from%20Google%20Sheets%20based%20on%20a%20cell%20value

It will throw the range error if you test it directly by clicking the Run button. Go to the spreadsheet nad make an edit to see if it works.

I also experienced this error from another function. but the code still works if you save it and run it.

The code only works when we manually change the cell value. The email does not come when the cell changes its value automatically by, for example, a formula. Does anyone know how to fix this?