In this article we will show you how to send email from Google Sheets. Most people aren't even aware this is possible, but in a few simple steps you will be able send emails like a pro!
Send Email from Google Sheets
1. Prepare your Spreadsheet with the data you need to send emails
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.
2. Create a Lido Account
Lido is a new spreadsheet that has formulas that Google Sheets does not, like SENDGMAIL(), which we'll use to send out our emails. You can create a free account at https://www.lido.app/go/signup.
3. Connect your Google Sheet to Lido (optional)
You only need to do this step if your Google Sheet data might change. Otherwise, you can skip this step and copy / paste your data into Lido directly from your Google Sheet.
Create a new file in Lido, then click on Connect Data.
Select Google Sheets, then paste in your Google Sheet URL.
Then, select the columns from your Google Sheet that you want to connect to Lido. Lido will automatically refresh data from your Google Sheet, so don't worry if you plan to add more records. They'll automatically sync.
Click Add Data and your spreadsheet data has now been imported to Lido.
First, we will create templates in a fresh worksheet. You can reference table columns as variables like this: [@column].
Then, navigate back to your spreadsheet table and select Add computed column.
Name the column Subject. Then, enter this formula in the second row:
Where Sheet1!$B$1 is replaced with the location of the email subject template cell that you just created. This formula will automatically replace any referenced column variables with the actual values from the row in your table.
Now repeat the same process for email body. First, create a new computed column. Then enter in the formula:
Since our body template cell is in a slightly different location than our subject. With the body, we can now see the full impact of using STRINGTEMPLATE since our Name variable is properly replaced in each row.
5. Write your SENDGMAIL Formula
Unlike Google Sheets, Lido has a concept of "Action" formulas. These formulas behave similarly to buttons, in that they don't run unless you trigger them. SENDGMAIL is a Lido Action formula that will send an email when run.
Create another computed column and type the SENDGMAIL formula into cell E2.
The formula to send an email from Lido is:
=SENDGMAIL(sender, recipient, subject, body)
In our example this will be:
Sender = email@example.com (This must be a gmail account, but does not have to be the same account you used to create a Lido account)
Recipient = Email in B2
Subject = C2
Body (message) = D2
6. Send your emails
Lido Actions need to be run, so let's do that. The first time you run the SENDGMAIL formula you'll be asked to authenticate into your Gmail account. Make sure you choose the same account that you entered into the sender field of the SENDGMAIL formula.
To send one email:
Right click on a blue SENDGMAIL formula and select Run Action.
To send all the emails in your column: click on the 3 dot menu and select Run Column Now.
And that's it! You can now send emails from a spreadsheet using Lido.
Sending Multiple Emails / Mail Merge from Google Sheets
If you have multiple messages to be sent, simple configurations on AppScript will allow you to run this smoothly. You can find the script on this article for mail merge.
Send Email in Google Sheets Based on Cell Value
If you have values on your spreadsheet and would like a message to be sent based on the contents of a cell, a coding script will allow you to do that. Follow this article to learn how to send an email when cell value changes.