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 });
}

var range = e.range;
is not working

Hello, I am new to programming and honestly do not have a clue what I am doing. I would like to adapt the above script to a google sheet that originates from a google form. I would like to get an email only when a certain question, i.e. a cell in the google sheet is modified with the word "Yes". I presume I could modify this script to do so after I learned how to implement it using the examples in the blog post. at https://blog.gsmart.in/alert-by-email-when-input-data-on-google-spreadsheet-meets-given-condition/

Thank you for creating the post and sharing this wonderful script.

I too get the same 'Range' errors TriptiHirani experiences but on line 26 of the above code. My google sheet has the same column headings as those in the blog post and I did update the email address on line 1

Here's to hoping someone sees this request. Cheers.

I get the same error message on line 26

Hi, was anyone able to solve the 'range not defined' error?

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?