How to send email when cell value changes in Google Sheets

Suppose you want to trigger an email to yourself when a cell value is changed in the Google Sheet.

Here are the steps that this script will do:

– Collect the details about the row that was updated.
– Compose an email with the details from the changed row
– send the email to one or more recipients

Here is how to do it in simple steps.

Create “OnEdit” Trigger

We need to trigger the email when a cell is updated.

Open your spreadsheet then go to Tools → Script Editor menu item

Let us first try and see whether the OnEdit trigger works or not!

Create a function like this:

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

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

triggerOnEdit() is the function that would be called when the Google Sheet is edited. It calls the function showMessageOnUpdate() just below that and it will just throw a message box and show the range that was edited.

Note that this is just for the test. Imagine alert boxes every time you edit the sheet!

Now that you have the code in the script editor. Do File → Save.

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.

Come back to your spreadsheet. Add a new value in any cell. You should see an alert box popping up.

Watching for specific changes

We don’t want to send email for every change. We want to send email only when the “status” column changes to “approved”. So lets first create a check for the status update.

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

checkStatusIsApproved() function returns a non zero number when the status is changed to approved. The return value is the row number that was updated.

Let us test this function.

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

Note that the triggerOnEdit() is updated to call showMessageOnApproval()

Do File → Save , go to the google sheet and change the status column of one of the rows to “approved”.

A message box should show the Row number that was updated.

Compose the email

Now that we know the row number of the approved record, all that we need to do is compose the email and send it.
We need to pick the first name, last name and email of the approved applicant and include those details in the email. Here is the code:

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);
  
  SpreadsheetApp.getUi().alert(" subject is "+mail.subject+"\n message "+mail.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});
}

Note that at this step, we are not actually sending the email. Just throwing a message box with and showing the email message in it.

Next, call sendEmailOnApproval() function from the trigger.

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

Do File → save. Go back to the Google sheet and approve another applicant. You should see the message box.

Send the email

Sending the email is simple. Just replace the message box with MailApp.sendEmail().
Let us first test whether mail works.
Create a function like this:

var admin_email='youremail@gmail.com';

function sendTestEmail()
{
  MailApp.sendEmail(admin_email,'a test email from script ',
'This is only a test');

}

Update admin_email to be your email address.

Select sendTestEmail in the script editor’s menu and press the ‘run’ button.

Provide the permissions to send email from the script.

Once the function finishes running, you should receive a test email.

Now you can update the sendEmailByRow() function.

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

Send another email to the candidate

It will just be appropriate to inform the candidate as well so that the whole process is automated. All that you have to do is to compose and send another email.

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

Sample Sheet and Code

Make a copy of the spreadsheet here

See the whole code snippet here

Note: Simple Trigger can’t send email

If you tried to send email from simple OnEdit() trigger that wouldn’t work because simple triggers in Google sheet can’t send email. It can display the message box though. You have to create the trigger as explained above so that the trigger can send the email.

Also See:
How to send email from Google Sheets