Send Email from Google Sheets When a Cell Value Changes

8 Minutes

Click here to Send Email When a Value Changes

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.

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

send email google sheets spreadsheet

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

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.

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.

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

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

Now let's make these messages dynamic for every row in your table. Go back to your table and add a computed column. This 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 lets you replace the variables in [@Column] syntax with the contents of the row it's in.

The syntax 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 is:


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, recipient, subject, body, status)

In our example this will be:

Sender = (This should be associated with a gmail 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.

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, recipient, subject, body))

Our new formula will now be:

```=IF(C2=TRUE, SENDGMAIL("",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.

First, 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 and name it status. 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.

It's important to choose a column with unique values as your linked column ID, since that's how Lido will keep track of which rows have already been sent.

Next, we will add an AND condition to our IF statement.

=IF(AND(condition=TRUE, status_cell<> “success”), SENDGMAIL(sender, 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("", B2, D2, E2, G2))```

As you can see here, row 4 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)

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 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 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.

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.

Tired of constantly monitoring your spreadsheets? Scale operations with Lido automations and free up time to focus on what really matters.