- Home
- Guides
- Google Workspace
- How to mail merge in Gmail with Google Sheets for free
How to mail merge in Gmail with Google Sheets for free
Send a personalized email to every row in a Google Sheet from your own Gmail account, using Google's free mail merge sample sheet and a Gmail draft with placeholders. No paid add-ons or extensions.
By Nur Islam- 5:52 video
- 5 steps
- 105.2K views
Jump to a step (5)
Sending the same reminder or offer to many customers from Gmail usually means either one email with everyone's address visible, or copying and pasting names and amounts into dozens of messages. Mail merge add-ons often cost money or ask for broad access to your account.
Google publishes a mail merge sample for Apps Script. You copy its Google Sheet, which already contains the script. You fill the sheet with your recipients and data, write one Gmail draft with {{placeholders}} that match the column headers, and run Mail Merge > Send Emails from the sheet.
Each person gets their own email with their own details, and the sheet records when each email was sent. The example is a payment reminder with first name, service name, amount and due date.
What you need
- A Gmail or Google Workspace account
- A copy of Google's Apps Script mail merge sample sheet
- Your recipient data with an email address column and any fields you want to personalize
- One Gmail draft that uses
{{Column Name}}placeholders
Step by step
0 of 5 done-
01
Copy Google's mail merge sheet
Search for Google's Apps Script mail merge sample on the Google for Developers site and click the link to make a copy of the sample spreadsheet. The copy includes the script that connects to your Gmail. It starts with 10 rows; you can add as many as you need.
-
02
Set the column headers and paste your data
Rename the sample columns to match your data. In the video they become
First name,Last name,Recipient,Service Name,Amount,Due DateandEmail Sent. Headers are case sensitive, so note exactly how you type them.Do not rename
RecipientorEmail Sent. Those two names are defined inside the script (you can see them under Extensions > Apps Script). Everything else can change. Then paste or type your data under the headers. -
03
Write the email as a Gmail draft
In Gmail, compose a new message and write the body with placeholders in double curly brackets, for example
Dear {{First name}},{{Service Name}},{{Amount}}and{{Due Date}}. Each placeholder must match its column header exactly, including capital letters.You can format placeholders like normal text, for example highlight
{{Amount}}and{{Due Date}}. Add a subject (the video usesReminder: Upcoming Payment Due) and close the message so it stays in Drafts. Leave the To field empty. -
04
Authorize the script
Back in the sheet, click Mail Merge > Send Emails. The first time, Google says the script needs permission. Choose your account, continue past the warning to the script, and allow the access it asks for. The script needs this to send email from your Gmail.
-
05
Send the mail merge
Click Mail Merge > Send Emails again. When asked, paste the exact subject line of the draft you want to send and click OK. If you have several drafts, only the one with that subject is used.
While it runs you see a running script message. When it finishes, the
Email Sentcolumn fills with the date and time for each row. Check your Sent folder to see one separate email per recipient.
Tips and common problems
- Do not rename the Recipient or Email Sent columns. The script looks for those exact headers.
- Placeholders are case sensitive. {{First name}} will not match a column called First Name.
- The script picks the draft by subject line, so copy the subject exactly. Other drafts are ignored.
- Test with your own address first. In the video, demo addresses bounced with address not found, which is expected for fake emails.
Questions
Can you do a mail merge in Gmail for free?
Yes. Google provides a free Apps Script mail merge sample. Copy its Google Sheet, fill in your data, write a Gmail draft with {{placeholders}}, and run Mail Merge > Send Emails.
Will recipients see each other's email addresses?
No. The script sends a separate email to each row, so every person only sees their own address.
Can I use a different draft for the mail merge?
Yes. When you run Send Emails, the script asks for a subject line and uses the draft with that subject. You can keep several drafts and choose one each time.
How many emails can I send with Gmail mail merge?
Apps Script has daily email quotas, which are lower for free Gmail accounts than for Google Workspace accounts. Check Google's current Apps Script quota page before sending to a large list.
