In this article:

Send Email from Google Sheets When a Cell Value Changes

May 8, 2024

It is very common for your datasets in Google Sheets to have cells that you change to indicate product movement, updates and other things you want to keep track of. If you want to be able to send emails when you change a cell value, here is a simple guide for you to do so.

Note that this method uses Lido to detect changes and to trigger a send email function.

How to Send Email in Google Sheets when a Cell Value Changes

In this example we will automatically email a team member once the status of their task is changed from “FALSE” to “TRUE”. You can substitute in your own values as you follow along.

1. Prepare your Spreadsheet with the relevant data for your email

For this example, we want to be able to send email messages to a team member when we update their task status from FALSE to TRUE.

Your Google Sheet data should be arranged in a table with no empty rows or spaces. The first row should be your column headers, which cannot have special characters or line breaks.

google sheets send email based on cell value
Email information on Google Sheets

2. Create a Lido Account

Lido is a new spreadsheet built for automation that is fully compatible with Google Sheets. You can create a free account at

We will use Lido to send emails when any of our cells change from FALSE to TRUE.

3. Connect your Google Sheet to Lido

google sheet send email based on cell value

Click on the green Connect Data button in the upper left corner, then click Google Sheets.

send email from google sheets based on cell value

Paste in your Google Sheets link you created earlier and click next. You will need to connect to a Google account that has access to the spreadsheet.

automated email from an excel sheet or google workspace account

You will be asked to grant Lido permission to access your Google Sheets the first time. Make sure that you check the appropriate box as shown here.

checkbox for adding data to cell a2 for your html email

Next, select the columns from your Google Sheet that you want to connect to Lido. Lido will automatically keep records updated from your Google Sheet, so don’t worry if you plan to add more records.

Click Add Data and your spreadsheet data has now been imported to Lido as a Table.

4. Create Email Subject and Body Columns

First, create the Subject and Email templates

In a new worksheet, type in the subject line and body messages you want to send. You can reference different column names as variables using this syntax [@ColumnName]. For example:

We added our subject line and email body headers to column a and our messages in column B. We will use this email template for our mail merge from a google spreadsheet later.

Next, create the Subject and Body columns

Now let's make these messages dynamic for every row in your table. Go back to your table and add a Computed Column for "Subject".

A Computed Column is a special column type that will apply the same formula to every row.

Create a Computed Column to apply the same formula to every row.

The formula we want to use in the computed column is called STRINGTEMPLATE. This formula replaces fields in your templates ([@Column]) dynamically with the values for each row

The syntax for our Computed Column formula is:


Where the template_cell is the cell address of the template that we just created.

**Remember to absolute anchor this reference like Sheet1!$A$1 since we want to use the same template for every row.

In our example, our formula for the new Subject column is:


What is looks like now is: 

Now you have personalized Subject and Body values for each of your recipients!

5. Write your SENDGMAIL Formula

Lido is better than Google Sheets for automation because of its unique Action formulas, which allow you to trigger things like emails and slack messages using spreadsheet formulas.

The formula to send an email from Lido is:

=SENDGMAIL(<sender-credential>, recipient, subject, body, status)

In our example this will be:

Sender = a credential you create for (This should be associated with a Gmail or Google Apps account)

Recipient = Email in B2

Subject = D2

Body (message) = E2

Status = G2 (this is the cell location where you'll put the result for whether the email was sent successfully. This should be the cell that is one column to the right of wherever you're currently entering your SENDGMAIL formula)

Final Formula:


Create another computed column with this formula in column F.

Start typing =SENDGMAIL(. When you're in the first argument, click on the "+" to add a sender credential to your Gmail (or Google Apps) account.

When you've completed your SENDGMAIL formula, it should look something like this: 

Make the Status Column in Column G

Next, we need to make a status column to keep track of whether we've already sent an email for the row. Create a new Linked column immediately next to the "Send the Email" column by typing "Status" in the first cell. This should be the same column that you specified in the status argument of your SENDGMAIL formula in step 5; update your formula if not. In our example it is column G.

For a Linked Column to work, you need to tie it to another column that is guaranteed to have unique values. For this example, we'll choose the email column as that will be expected to be unique. Timestamp columns are also good to use as the Unique ID if you have it.

We can quickly test this is working by Right clicking on an action cell and clicking Run Action to send an email. You’ll need to authenticate into the Gmail account used as the sender in your formula the first time. Notice how the status ("success") of the action gets added to the cell specified in the formula.

6. Add Conditional Trigger Logic for SENDGMAIL

We don’t want to send emails to everyone, only those who have met our criteria for when a cell value has changed. Lido’s action formulas work with all other spreadsheet formulas, so we can create an IF statement to make this work.

In this example, we only want to send emails if our Task Done column is TRUE.  We will add an IF statement to our SENDGMAIL formula to reflect this:

=IF(condition=TRUE, SENDGMAIL(<sender-credential>, recipient, subject, body))

Our new formula will now be:

```=IF(C2=TRUE, SENDGMAIL(<sender-credential>,B2,D2,E2, G2))```

You can now see that the action only appears in the column for the row where column C = TRUE.

And now finally, we need to update the IF statement logic in our Send Emails column so if an email has already been sent for that row, we don't display the SENDGMAIL formula. This will ensure that an email only gets sent once when a cell value changes when you automate this column in the next step.

Make sure we only send one email to each recipient

We want to make sure we only send one email to each recipient. To do this, we'll check the Status cell to see if an email has already been sent.

We'll do this by adding another condition to our IF statement using AND.

=IF(AND(condition=TRUE, status_cell<> “success”), SENDGMAIL(<sender-credential>, recipient, subject, body, status_cell))

Where status_cell is the location of the cell in your status column. This should match the status_cell argument specified in your SENDGMAIL formula.

Our formula in cell F2 for example will now be:

```=IF(AND(C2=TRUE, G2 <> “success”), SENDGMAIL(<sender-credential>, B2, D2, E2, G2))```

As you can see here, row 5 has an Action to run because column C is both TRUE and an email has not been sent.  However, while row 6 is TRUE in column C, there is no email to send because the status column already displays success.

7. Create a New Automation

Now that everything is working in our spreadsheet, the last step is to create an automation so everything will run silently in the background.  

Click on the menu icon in your Send Email column, then select Run column on a schedule.

This will open the Lido automation panel.

In this example, every day at 10am Lido will pull in the latest data from Google Sheets, check to see if there are any actions to run based on our IF statement, and trigger all actions that are present in our Send Email column.

Send Email From a Google Sheet when a Cell Value Changes (AppsScript Method)

We will also show you how to send email based on cell value using google apps script by first importing a list of email addresses from a google form to google sheets or microsoft excel.

1. Prepare your Spreadsheet with the relevant data for your email

Email information on Google Sheets

For this example, we want to be able to send email messages to our customers when we update their status to “Shipped” in Column E.

2. Go to Extensions and open Google Apps Script

Opening apps script on Google Sheets

This will open a new tab, wherein we can create a code to send emails on Google Sheets.

3. Input the following code

function sendMailEdit(e){

 if (e.range.columnStart != 5 || e.value != "Shipped") return;

 const rData = e.source.getActiveSheet().getRange(e.range.rowStart,1,1,4).getValues();

 let itemsOrdered = rData[0][2];

 let email = rData[0][0];

 let name = rData[0][1];

 let trackingNo = rData[0][3];

 let msg = "Hello, " + name + ". Your order: " + itemsOrdered + " is for shipping with tracking number " + trackingNo + ". Thank you for your purchase."


 MailApp.sendEmail(email, "Shipped Order", msg)


Code syntax to send email on Google Sheets

This is how our code runs:

  • Line 1 creates a function called sendMailEdit which is the function that runs when we change a cell value
  • Line 2 is a logical test that checks whether our cell value in our specified column is of the value we want (In this case, when a cell is changed to “Shipped”). Replace as “Shipped” and columnStart as necessary
  • Line 3 allows our dataset to be handled as an array
  • Lines 5 to 8 are columns in our spreadsheet that contain information regarding the contents
  • Line 9 gets the contents of our sheet to draft an email message with the indicated form. Adjust this message as necessary.
  • Line 11 triggers to send email with the use of the script indicating the recipient's email, subject and message contents with the use of assigned variables.

4. Save your code by clicking the Save project icon

Saving your project on Google Sheets Apps Script

5. On the left hand menu, go to Triggers and click Add Trigger

Adding triggers to send email on Google Sheets

6. Edit Trigger to the following:

  • Choose which function to run: sendMailEdit
  • Event Source: From Spreadsheet
  • Event Type: On edit
Trigger contents to send email on edit on Google Sheets

7. Click Save.

8. To execute your email, go to Google Sheets and test your trigger word.

Testing your send email function on Google Sheets

The email should be sent to its intended recipient after your Google Sheets spreadsheet loads and saves.

Send Email successfully via Google Sheets

You can adjust the code to other cell values as needed to be able to trigger an email as they are changed. You can also edit the same code and trigger to send email to you or anyone else who needs to be notified regarding the changes in your Google Sheets. Here’s a simple guide on how to do this:

Send A Notification to Your Team When Cell Value Changes in Google Sheets

In this example an email will be sent to you or other team members when a value is changed. In every instance that a status is updated to “Shipped”, the team will be notified of the movement.

1. Open Apps Script and input the following code on the editor:

function sendEditNotification(e){

 if (e.range.columnStart != 5 || e.value != "Shipped") return;

 const rData = e.source.getActiveSheet().getRange(e.range.rowStart,1,1,4).getValues();

 let itemsOrdered = rData[0][2];

 let email = "";

 let name = rData[0][1];

 let trackingNo = rData[0][3];

  let msg = "Order with tracking number " + trackingNo + " has been updated to shipped."

 MailApp.sendEmail(email, "Order Movement", msg)

Send email code in Apps Script on Google Sheets

In this code, we’ve collapsed the contents of our previous function to make it easier to focus on our new function: sendEditNotification. In this code, remember to input your own email address or the email addresses of the people you want to notify in line 18 and edit the contents of the email message as necessary.

2. Save your project

3. On the left hand menu, go to Triggers and click Add Trigger

Adding New Email Trigger on Google Sheets

4. Edit Trigger to the following:

  • Choose which function to run: sendMailEdit
  • Event Source: From Spreadsheet
  • Event Type: On edit

Contents of trigger on Google Sheets Apps Script

5. Save your trigger and test the function by editing your spreadsheet.

6. You should now be able to see in your email the notification.

Notification Email from change in Google Sheets

This can be used for simple email notifications, event reminders, order tracking and more. Since we have both sendMailEdit and sendEditNotification functions in place, these will trigger the creation of the email message simultaneously so that both recipient and owners are updated at the same time. Using this send email generator in Google Sheets can make your data handling easier and open up a whole new world of possibilities.

*Note you can also send email notification messages based on a cell value from microsoft outlook by using the =SENDOUTLOOK() formula instead of =SENDGMAIL()

Be sure to also check out our article on how to send email from google sheets. if you enjoyed this article. Alternatively, many readers who read this article also read our article on how to automatically send email from google sheets.

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.
Get your copy of our free Google Sheets automation guide!
  • 27 pages of Google Sheets tips and tricks to save time
  • Covers pivot tables and other advanced topics
  • 100% free

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->