Automatically Send Email from Google Sheets (Easiest Way 2023)
In this article, we will show you how to automate emails in Google Sheets so that emails are automatically sent by the use of a button or an extension. If you simply want to send an email from Google Sheets please read this article instead.
Automation in google sheets previously required knowledge of its Script Editor, Apps Script, which can be difficult to learn and use. Lido is a new spreadsheet software that is fully compatible with Google Sheets, and has automation built in, allowing you to more easily automate tasks without writing code.
Automate Sending Emails in Google Sheets
Imagine you have a list of multiple customers and you have to create multiple customized emails for each of them. Doing this for a long database will take you a while to finish. Using Google Sheets makes this easier and here’s a simple guide for you to follow to automatically send them.
1. Input Recipient Details in Google Sheets
We will put recipient details in Sheet 1, including the receiver name, mailing address, and other details you want to customize in your text body. Remember to separate each in a new column.
2. Create a Lido Account
Lido is a new spreadsheet software built for automation that is fully compatible with Google Sheets. Using Lido will allow us to create an automation with normal spreadsheet syntax in just a few simple steps instead of having to use Apps Script or other complicated code.
You can create a free account at https://www.lido.app/go/signup.
3. Connect your Google Sheet to Lido
Click on the green Connect Data button in the upper left corner, then click Google Sheets.
Paste in the Google Sheets URL you created earlier and click connect to Google Sheets.
.png)
This will load a box asking you to authenticate your google account. Make sure that you check the option that gives Lido access to your Google Sheets. Once that has been done you can click 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 Message Templates in a new Worksheet
In a new worksheet, type in your email subject and body content. Reference your columns as variables using this format: [@Column].
In the example below we entered the text below in Column B:
Email subject:
Thanks for your inquiry, [@Name]
Email body:
Dear [@Name], thank you for your inquiry about [@Service].
5. Create Email Subject and Body Columns Referencing the Message Template
Lido has a formula called =STRINGTEMPLATE() that replaces column references (like [@Name]) with the values of a row in a table. We will use this to create dynamic email subjects and bodies.
Make a computed column by entering this formula in the second row of the blank column next to your table:
=STRINGTEMPLATE("email_body_cell")
Where:
email_body_cell is the cell location of the email body we made in step 5. (Remember to use an absolute reference - like Sheet1!$B$1).
This will automatically create a computed column. A computed column is a special type of column in Lido that automatically applies the same formula to every row in your table. The column will be created with a default name of Column1 and you can rename them to Subject and Body.
Our formula in cell D2 for email subject will be:
```=STRINGTEMPLATE(Sheet1!$B$1)```
And then for cell E2 for email body it is:
```=STRINGTEMPLATE(Sheet1!$B$2)```
Your table should now have subject and body columns with dynamic content, like this:
6. Configure the =SENDGMAIL() Formula
The formula to send an email from Lido is:
=SENDGMAIL(sender, recipient, subject, body)
SENDGMAIL is a special type of formula in Lido called an Action formula. Action formulas don't evaluate immediately when you type them (as normal spreadsheet formulas do). You need to explicitly run them manually or with an automation.
In our example this will be:
Sender: the email address you want to send emails from. This must be a Gmail or Gsuite account.
Recipient: email address in B2
Subject: D2
Body: E2
```=SENDGMAIL("your@email.com",B2, D2, E2)```
Enter this formula in a cell directly next to your body column in the second row and click enter to create another Computed Column. You can rename this column Send Email.
7. Send a Test Email
Let’s test one of these actions to run. Right click on a SENDGMAIL formula and click Run action. You’ll need to authenticate into your Gmail account the first time.
8. Click 'Run Column Now' to Send all Emails in the List
If you want to send all emails in your column at once, click on the 3 dot menu in your SENDGMAIL column, then select Run column now.
Automate Sending Emails on a Recurring Schedule
If you don’t want to manually run your email actions every time, you can set up a Lido automation to do it for you.
In the 3 dot menu for your SENDGMAIL column, select Run column on a schedule.
This will open the automation panel. Select an option for how frequently you want to run every SENDGMAIL Action in the column. Before every automation run, Lido will automatically fetch the latest data from Google Sheets as well.
In this example, we are going to automatically send out our emails once per day at 9:30am.
Click Save to create your new automation.
How Do I Email Each Person Only Once?
If you are planning to run this automation regularly and don't want to email the entire list each time, it is a good idea to create a status column where we can keep track of who has already been sent an email.
Step 1. Create a Status Column
Create a status column by typing "Status" in the top cell of the next empty column. This will automatically create a Linked Column in Lido. A Linked Column is a special type of column in Lido where you can put any text value and it magically stays in sync with your Google Sheets data.
Note: for Linked Columns to work, you need to have a column in your Google sheets data whose values are unique - and that includes blank values. Timestamps work well, email addresses often do, or other record IDs
Step 2. Edit the SENDGMAIL Formula to Update the Status Column
Next, add a 5th parameter to SENDGMAIL that will update the status column with the result of sending the email ("success" or an error). For our example:
```=SENDGMAIL("your@email.com", B2, D2, E2, G2)```
When SENDGMAIL is run, it will put the status in cell G2. If it runs successfully, the value "success" will be saved in G2. If there is an error, the error message will be saved in cell G2. .
Step 3. Add an IF Statement to Check if an Email has Been Sent
Finally, we will wrap our SENDGMAIL formula in an IF statement to check if an email has been sent already (column G = "success") and only show SENDGMAIL if it has not been sent. Otherwise, FALSE is displayed.
When the column is run now, only rows that have SENDGMAIL in them will trigger emails (these are the rows that have not yet had an email sent). Once any new emails have been sent, the status column will be updated with "success", and emails will not be sent a second time for that row.
=IF(CELL REF <> “success”), SENDGMAIL(your@email.com, recipient, subject, body, CELL REF))
Where:
CELL REF <> “success” - This is the cell in your status column that is not filled with success
For our example below, we use the following formula in row 2.
Remember, a computed column automatically uses the same formula in all rows, so you can edit any row, just make sure you use the correct row number. We generally use row 2 in examples
```=IF(G2<>"success", SENDGMAIL("your@email.com", B2, D2, E2, G2))```
You can see that for emails that have already been sent (column G = "success") they display false instead of SENDGMAIL.
And those are a few ways to make sending emails easier for work or personal use. Having the power to do these with multiple emails in scheduled automations based on data in Google Sheets save you a lot of time and effort.
How to Send Emails From Google Sheets
It's easy to send email from google sheets. Simply follow the steps below:
1. Input Recipient Details in Google Sheets
2. Create a FREE Lido Account
3. Connect your Google Sheet to Lido
4. Create Email Subject and Body Message
5. Configure the =SENDGMAIL() Formula
6. Send a Test Email
7. Click 'Run Column Now' to Send all Emails in the List
8. Set up Automation to Send on a Schedule.
By following the steps above you can easily send email in google sheets. Simply scroll up to the top of this article and follow each step.
If you enjoyed this article you might also enjoy our article on how to send email when cell value changes or how to send email when a new row is added.
