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:

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)

on edit trigger for Google Sheets

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.

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.

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:

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.

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:

Update admin_email to be your email address.

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

run function (script editor)

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.

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.

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

Leave a Reply 0 comments

Leave a Reply: