Spreadsheets may not always look the prettiest, but they’re the unsung heroes of the business world. They can do everything from tracking inventory to organizing project plans and managing budgets.
But while Google Sheets is great for storing data, you also probably need to do something with that information later. For example, if you use a spreadsheet to track inquiries from new leads, wouldn’t it be nice if you could automate the process of following up on those inquiries by email?
Zapier is a no-code automation tool that can help you connect apps and set those business-critical tasks on autopilot. In this tutorial, we’ll show you how to use a Zap (our word for our automated workflows) to automatically send emails from spreadsheet data. Use it to follow up on leads, send confirmation emails, notify your team of updates, and more—all without the manual data entry.
Zapier is the leader in workflow automation—integrating with thousands of apps from partners like Google, Salesforce, and Microsoft. Use interfaces, data tables, and logic to build secure, automated systems for your business-critical workflows across your organization’s technology stack. Learn more.
Before you begin
Create your spreadsheet in Google Sheets if you haven’t yet. Make sure you have your columns labeled. This Zap will not work if your columns aren’t labeled.
If you anticipate that the value in a cell will change between two values often—for example, if a cell value changes from yes
to no
, and then back to yes
—it requires an extra step to make sure this Zap works. Set up a separate column where you can enter the timestamp when you make that change.
Note: You can quickly enter a timestamp while working in Google Sheets using the following keyboard shortcuts: Ctrl + Alt + Shift + ;
to insert date and time on Windows machines; or Cmd + Alt + Shift + ;
to insert date and time on a Mac. Using the formula =TODAY
in Google Sheets will not trigger the Zap, because it changes automatically, rather than by manipulating other cells in the spreadsheet.
Send email notifications from updates to Google Sheets spreadsheets
You can create your own Zap from scratch with our point-and-click editor, but we also have quick templates to get you started.
To get started with a Zap template—what we call our pre-made workflows—just click on the button. It only takes a few minutes to set up. You can read more about setting up Zaps here.
Here’s how to set it up:
Connect and customize Google Sheets
Start by setting up your Google Sheets trigger—the event that starts a Zap.
If you’re creating a Zap from scratch, select Google Sheets as the app and New or Updated Spreadsheet Row as your Event. (If you’re using our Zap template, the Zap editor will do this for you.) Next, if you haven’t connected Google Sheets before, you’ll need to sign in to your Google account. Otherwise, select your Google Sheets account from the dropdown menu. Click Continue.
For each app you connect, Zapier will ask for a broad set of permissions, which allows you to be flexible with your Zaps. The only actions Zapier takes on your app accounts are those a given Zap needs to accomplish what you’ve set up.
Then, select the Google Sheet and specific worksheet you’d like to use. Click on the dropdown under Trigger Column and select the column in your spreadsheet you want Zapier to watch for.
Whenever a cell in your selected Trigger Column changes, Google Sheets will tell Zapier to kick off your workflow.
Note: If your trigger column is empty, the Zap will still trigger. Add a filter step—available on our paid plans—to stop the Zap from triggering on empty columns.
Once you’ve customized your trigger, click Continue.
Now it’s time to test your trigger. Zapier will find a recent spreadsheet row in your Google Sheets account, according to the instructions you’ve customized in the previous step.
Once you see a success message, be sure to check the results of your test to make sure you have the data you want. If something doesn’t look right, repeat the earlier step until you get the data you want. You’ll use this information in later steps to customize your Zap. When you’re happy with your test data, click Continue.
Optional: Add a filter if your spreadsheet includes checkboxes
In the example below, our result shows empty rows, even though we specified a trigger column earlier. This may occur if you’ve formatted a spreadsheet column to include a checkbox.
If this occurs, it’s an easy fix. Just add a filter step. Below, I’ve added a filter to ensure this Zap only triggers if the Email column has an email and Product Qualified is checked.
Connect and customize Gmail
Now you’ll set up your action—the event your Zap will perform once it’s triggered.
If you’re creating a Zap from scratch, the Zap editor will prompt you to choose your app and action event. Select Gmail for your app and Send Email as the Event. If you’re using the Zap template above, your action app and event will be pre-selected for you. Next, like you did with Google Sheets, click Sign in if you haven’t connected your Gmail account to Zapier before. Otherwise, select your Gmail account from the dropdown menu and click Continue.
Customize your email
Now you’ll customize the email notification you’d like to send each time your Zap triggers. In this example, our spreadsheet is an email list, but you may be using this Zap to communicate spreadsheet changes to your team. The same principles apply.
In order to pass information from Google Sheets into your email, you need to map fields from your trigger. Click the plus sign in the To field and choose the Google Sheets step in the modal to see all the data from your trigger, then select the information you want to map.
Since this spreadsheet is an email list, the Email column field is the recipient. It will act as a placeholder field; any time there’s an updated row that meets the criteria you set, the Zap will email that address.
Following this method will dynamically populate the To: email address right from your spreadsheet. If you want the email to go to the same person or people every time, you’ll want to manually type in the addresses instead of choosing from the dropdown in this step.
You can customize the fields for additional recipients, sender name, and a reply address if you’d like, but it’s not required for the Zap to work. Under the Subject field, you can either type in your subject (for something that will be the same each time), use your spreadsheet fields if you’d like it to change based on spreadsheet data, or a mix of both.
If you plan to include hyperlinked text or any special formatting in your email, you’ll need to click on the dropdown menu under Body Type and select HTML. When you use HTML as the body type, you’ll need to use HTML for hyperlinks, paragraphs, and other formatting to show up in your email.
Type your email copy in the Body field. You can also use spreadsheet information in your email body copy. Just type /
or click the plus sign inside the Body field to add whichever spreadsheet information you’d like. In our example, we have a list of names and emails in our spreadsheet, so we’re using that to personalize our email message.
When you’re finished customizing your email, click Continue.
Test your Zap
Now it’s time to test the Zap to ensure it’s configured properly and your email shows up how you’d like. Zapier will send a new email according to how you’ve customized your message in the previous step.
Testing the Zap will send the email. If you want to test your Zap but don’t want to email a lead, for example, you can go back and change the recipient to a personal email and then try to test the Zap. Another option is to make sure you have a row of test data in your spreadsheet that includes your own email address, and use that information as you test your Zap.
Once you’ve tested the Zap, check your email to see how the message looks.
Once everything is set up correctly, you’re ready to begin using your Zap.
Power business-critical communication with automation
Spreadsheets don’t have to bog you down. With Zapier, you can use information you store in Google Sheets to send business-critical emails to your team or customers.
Zapier is the leader in workflow automation—integrating with thousands of apps from partners like Google, Salesforce, and Microsoft. Use interfaces, data tables, and logic to build secure, automated systems for your business-critical workflows across your organization’s technology stack. Learn more.
Related reading:
This article was originally published in September 2020. It was most recently updated in November 2024 by Nicole Replogle.