How to use Google Sheets data to send emails automatically. Step-by-step guide for Google Sheets automation workflows with Zenphi.
Table of Contents
Why would you need to auto send email from Google Spreadsheet
Sending out emails automatically using Google Spreadsheet data can be beneficial in many circumstances. Primarily, we’re discussing the process of automating your communication with counterparts using a Google Sheet as a database.
You would likely use the sheet’s columns to specify your counterpart’s first name, last name, email address, maybe job title, or other variables important for your use case (more on that later). While tools like Zapier can connect your Google Sheets with an email service provider (e.g., Mailchimp), using Zenphi — the best Google Sheets automation tool — allows you to keep all your communications within your Google Workspace. This ensures data security, as you’re not sharing your counterpart’s data with Zapier or other third-party solutions, and cost optimization, since you won’t need to pay for additional services like an email provider and Zapier to perform this task.
Automated emails from Google Sheets: how to build a workflows
With Zenphi, unlike other platforms, you can trigger your flows manually or automatically, ensuring that every time you have a new row in your Google sheet, an email is sent out automatically. Let’s deal with both these use cases separately.
Triggering Google Sheets automated emails manually
Create a Google sheet you’d want to use as your database to store variables needed for personalized automated emails from Google sheets.
The only must-have variable here is an email address. All other rows will be used for extra personalization (Name or personalized greeting, or order details, personal education schedule etc.) Learn more in Advanced personalization automated emails sent from Google Sheets section.
Use a Read a Row action
- Establish a connection between Zenphi and your desired Google drive.
- Specify the file and the sheet you want Zenphi to read rows from
Use Load columns button
Use this action to automatically preload the columns and map them to the variables in your flow. For example, you have to tell Zenphi that Name in your Google sheet should be used to replace the variable {{Name}} in your Google doc.
Use Foreach action
What this means is that the flow you’re creating will be repeated for every row. Specify the action that should be taken for each row — in this case, it’s Read a row. In other words, you’re telling Zenphi that you need it to read not just one row. But all the rows in the sheet and perform a flow you describe below for every variable.
Use Gmail => Send Email action
This action will allow you to send emails using your Gmail account.
To establish a connection, you’ll have to:
Set up the connection between Zenphi and your Gmail account
You have to be authorized Google workspace Admin! You also can only use Google Workspace, not your personal Gmail account!
Set up the sender's email address
You can hard-code it
Set up the receiver's email address
For this, you have to tell Zenphi that email address should be taken from the Foreach Loop action
Set up subject
Hard-code or use a variable from your Google Sheet
Set up the body
Here you can also write the copy of your email using individual variable for personalization (for example, Name). Or you can create fully personalized copy and store it in Google sheet, adding a reference to the {{copy}} variable on this stage.
Add attachments if needed
Trigger the flow to test
Also check out our detailed tutorial here
Automated email from Google Sheets: no manual trigger
This is a use case where you don’t want to trigger your emails manually. You want to use Zenphi to put email communications on autopilot. It basically means that Zenphi will monitor certain activities for you. And if something happens, the workflow will be triggered automatically.
The important condition here is “something happening”. Therefore, you have to tell Zenphi what to look for. In this post, we’ll describe the workflow that you can build to auto send email from google spreadsheet using New Signup as an automated trigger. Let’s assume that every time somebody signs up for your service or your webinar, you need this person’s data to be stored in Google sheet, and an automated Thank you email being sent to the very same person.
Let’s begin.
For this workflows, you need to choose an automated trigger — New Signup
Select and automated trigger
In this case, it will be a New email arrival
Create a connection to the Gmail account
You have to be authorized Google workspace Admin! You also can only use Google Workspace, not your personal Gmail account!
This step means you’re telling Zenphi what email account to monitor.
Once the connection is successfully established, data becomes accessible, including subject lines, text bodies, HTML content, and more, from incoming emails in the designated Gmail account. You can then utilize this information in the subsequent actions.
Add If Condition
You need to tell Zenphi that action should be taken only if certain events happen. If it doesn’t happen, the flow should not be run. Therefore, you use True/False dichotomy. If New signup happens — do this. If not, do nothing.
But how does Zenphi know which emails are signups and which belong to a different type?
The great thing about Zenphi is that you can control this selection!
For example, by saying that “if email’s subject is New SignUp this is a Signup email. Everything else should be disregarded”.
You can use other conditions obviously by parsing data types from the previous step (new email arrival). It can be “if a name contains” or “if order doesn’t contain X item” etc.
Use Add a Row action
You need to tell Zenphi that as soon as you have a Signup, you need person’s data specified in the email to be added to Google sheets. And not just added, you need to create a row with new data.
To add a row:
1. Create a connection with your Google drive
2. Specify the name of the sheet you need your rows to be added
3. Map the variables
Use Send email action
We described the Send email action in detail above, the automated triggering of auto emails from Google sheets is not different from using a manual trigger, in this case.
If you’re up to something more advanced and don’t want to store email copy in your Google Sheets as one of the fields or hard-code it while setting up the Send email action, you can use Zenphi’s AI model to generate clean and personalized emails.
Access this tutorial on Sending out Thank you emails to learn the details
Advanced personalization in automated emails sent from Google Sheets
In many cases, you’d want to not only send a personalized email with a certain text in the body. You’d want to send an attachment (like a PDF document) that will be also created automatically and stored on your Google Drive.
For this example, we’ll be creating a personalized Work From Home Policy document that you’ll be sending out to your employees who requested work from home, and their request was approved.
For this purpose you’ll need the same workflow that we described above plus one extra step — personalized document generation.
Create a document template on your Google Drive and insert {{variable}} for personalization
Automate the process of creating a personalized document
Use a Read a Row action
- Establish a connection between Zenphi and your desired Google drive.
- Specify the file and the sheet you want Zenphi to read rows from
Use Foreach function
What this means is that the flow you’re creating will be repeated for every row. Specify the action that should be taken for each row — in this case, it’s Read a row. In other words, you’re telling Zenphi that you need it to read not just one row. But all the rows in the sheet and perform a flow you describe below for every variable.
Add Generate a Document Step
This action will be taken not once but as a part of FOREACH flow (meaning, it will repeated multiple times).
- Add connection to your Google drive
- Specify where to save your new document
- Specify the file name (for example {{Name}}_policy.doc)
You can use template offered by Zenphi that will automatically map all your variables in Google sheet to Google doc. - Toggle on Generate PDF
- Test the flow and check the logs stream
Watch more detailed video tutorial here
Advanced Document Sharing
If you’re using a link in the body of the email to a document stored on your Google drive, you’d want to provide access to this document to a person who receives your email without providing access to all your Google Drive.
Use Share Access action
In this case you need to add Share Access action between Generate a Document and Send an Email action.
- Make sure you provide a correct role to the user
- Use a user’s email address stored in your Google sheet to identify the user who has access to this file (again, use Loop foreach => email address to tell Zenphi where emails are stored)
Talk to us
Not sure how to start your Google Sheet to email automation? Let us guide you on every step!
