How to send personalized emails from a spreadsheet

by

TL;DR

You can send personalized emails directly from a spreadsheet using Google Sheets and a short Apps Script — no paid tools required. The whole setup takes about 45 minutes the first time and runs in under five minutes on every campaign after that. You need a Google account, a clean contact list, and a Gmail address with enough daily sending quota for your list size.

What You Need Before You Start

  • A Google account (free tier is fine; free Gmail allows 500 sends per day, Google Workspace allows 2,000)
  • Google Sheets — runs in any browser, no install
  • A contact list with at minimum: first name, email address, and at least one personalization field beyond the name
  • Basic comfort copying and pasting code — no prior programming experience needed
  • Optional: Mailmeteor if you want a point-and-click UI that sits on top of your Sheet instead of writing any code
  • Optional: GMass if you need open-rate tracking, click tracking, or automated follow-up sequences
  • Your email copy drafted and ready before you open a single tool — subject line included

Step 1: Build Your Spreadsheet with the Right Columns

Open Google Sheets and create a new spreadsheet. Name it something you will recognize later, like “Email Campaign — May 2026.”

Row 1 is your header row. Use lowercase column names with underscores, because you will reference these names as placeholders in your email template. A solid starting structure looks like this:

A B C D E
first_name email company offer sent

The first_name, email, company, and offer columns hold your contact data. The sent column is a status tracker — your script will write “SENT” into it after each successful delivery so you never email the same person twice if you rerun the script.

Fill in real data starting from row 2. If a personalization field is blank for a contact, leave the cell empty rather than writing “N/A” — blank is easier to handle in code.

You should now see a clean sheet with headers in row 1 and contact data starting in row 2, with no merged cells and no blank rows between records.

Step 2: Clean and Validate Your Data

One bad email address or a name that is all caps will make your campaign look sloppy. Audit your list before you write any code.

Use this formula in column F to flag malformed email addresses:

=IF(REGEXMATCH(B2,"^[a-zA-Z0-9._%+\-]+@[a-zA-Z0-9.\-]+\.[a-zA-Z]{2,}$"),"OK","CHECK")

Paste it in F2, then drag it down the column. Every cell showing “CHECK” needs fixing.

Next, remove duplicates. Go to Data > Data cleanup > Remove duplicates, check column B only, and let Sheets clean it up.

Finally, trim stray spaces from names. In a helper column, run:

=TRIM(A2)

Copy the output, then paste-as-values back into column A using Ctrl+Shift+V (or Cmd+Shift+V on Mac). This prevents your emails from starting with “Hi Sarah” (two spaces before the name — a classic sign of dirty data).

You should now see no “CHECK” flags in column F and no duplicate rows in your list.

Step 3: Write Your Email Template with Placeholders

Before touching Apps Script, write your email in a plain text document or a Google Doc. Use double curly braces around any word that should be replaced with spreadsheet data. The text inside the braces must match your column header names exactly, including capitalization.

Example subject line:

Quick question for you, {{first_name}}

Example body:

Hi {{first_name}},

I noticed that {{company}} has been expanding this year and wanted to reach out. 
{{offer}} might be worth a look if saving a few hours a week sounds useful.

Worth a 15-minute call this week?

[Your name]

The power of this approach is the offer column. If you write a one-sentence custom pitch for each contact before you run the script, your emails read like individual outreach rather than a batch send. That one column separating generic from genuinely personalized is where most solopreneurs leave reply rates on the table. For a deeper look at what makes outreach copy work, see how to write cold email sequences that get replies.

You should now have your subject line and body saved somewhere you can copy from, with placeholders that exactly match your column headers.

Step 4: Open Google Apps Script

In your Google Sheet, go to Extensions > Apps Script. A new browser tab opens with the Apps Script editor — think of it as a lightweight IDE that lives inside your Google account.

Delete the default placeholder function in the editor. You are starting fresh.

Rename the project at the top left from “Untitled project” to something like “Spreadsheet Mail Merge” so it does not get buried.

Apps Script uses V8 JavaScript and runs on Google’s servers. It has built-in access to your Sheets data and your Gmail account through Google’s service classes. You do not install packages or configure environments.

You should now see a blank Code.gs tab open in the editor, cursor ready on line 1.

Step 5: Write the Mail Merge Function

Paste this complete function into the editor:

function sendPersonalizedEmails() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var headers = data[0];

  var subjectTemplate = "Quick question for you, {{first_name}}";
  var bodyTemplate = 
    "Hi {{first_name}},\n\n" +
    "I noticed that {{company}} has been expanding this year. " +
    "{{offer}} might be worth a look if saving a few hours a week sounds useful.\n\n" +
    "Worth a 15-minute call this week?\n\n[Your name]";

  for (var i = 1; i < data.length; i++) {
    var row = data[i];
    var sentIndex = headers.indexOf("sent");

    if (row[sentIndex] === "SENT") continue;

    var email = row[headers.indexOf("email")];
    var subject = subjectTemplate;
    var body = bodyTemplate;

    headers.forEach(function(header, index) {
      var placeholder = "{{" + header + "}}";
      subject = subject.split(placeholder).join(String(row[index]));
      body = body.split(placeholder).join(String(row[index]));
    });

    try {
      GmailApp.sendEmail(email, subject, body);
      sheet.getRange(i + 1, sentIndex + 1).setValue("SENT");
    } catch (e) {
      sheet.getRange(i + 1, sentIndex + 1).setValue("ERROR: " + e.message);
    }

    Utilities.sleep(1000);
  }
}

Replace subjectTemplate and bodyTemplate with your actual copy from Step 3. The Utilities.sleep(1000) adds a one-second pause between sends — this keeps you inside Gmail’s rate limits and prevents throttling. The try-catch block means one bad address writes an error to the sent column and the script keeps going rather than crashing on row 12 of a 200-row list.

You should now see the complete function in the editor with no red underlines.

Step 6: Test with One Recipient

Before sending to your full list, test against a single row. Temporarily delete all rows except one — use your own email address or a test address you control.

Click the run button (▶) at the top of the Apps Script editor.

Google will prompt you for permissions the first time. Click Review permissions, select your account, scroll down, and click Allow. This grants the script access to Gmail and Sheets for your account only.

Check your inbox. The email should arrive within 30 seconds. Verify that:

  • The subject has your actual name, not the raw text {{first_name}}
  • Every placeholder in the body is replaced
  • The sent column in your sheet now reads “SENT”

If you see raw placeholder text in the email, your column header does not match your template exactly. Check for accidental spaces, capital letters, or a mismatched underscore.

You should now see a personalized test email in your inbox and “SENT” in the status column.

Step 7: Run a Small Batch Before Going All-In

Restore your full list. Add a temporary column G called test_batch and put the word “yes” next to five contacts you trust (colleagues, friends, or secondary inboxes you control). Modify the loop condition temporarily to skip any row where column G is not “yes.”

Send that five-person batch. Check all five inboxes if you can. Confirm:

  • Names render correctly for names with apostrophes (O’Brien) and accented characters (José)
  • The offer field for each person shows the right copy, not another person’s copy
  • None of the five received a duplicate

Once you are satisfied, remove the test_batch filter from the script and delete column G. You are ready for the full send.

You should now have five verified emails in five separate inboxes, each with the correct personalization.

Step 8: Send to Your Full List

Run the script one final time against your full list. The script skips every row already marked “SENT” from your test, so previous sends are safe.

For a 200-contact list at one second per send, expect the script to finish in about three to four minutes. Watch the sent column populate in real time. When it stops updating, your campaign is done.

Go to Gmail > Sent and spot-check five random emails to confirm they look right. Any rows showing “ERROR:” in the sent column are failed sends — fix the email address and rerun. The script will skip all the “SENT” rows and only retry the error rows.

You should now see your sent column fully populated, a full “Sent” folder in Gmail, and zero duplicate sends.

Common Mistakes To Avoid

  • Mismatched placeholder names. If your header is first_name but your template says {{firstname}} (no underscore), nothing gets replaced. Always copy-paste placeholder names from the header row.
  • Skipping the test step. Sending 500 emails with {{offer}} visible as literal text is a hard mistake to walk back. Always test with one row first.
  • Ignoring Gmail’s daily sending limit. Free Gmail stops at 500 sends per day. If your list has 600 contacts, the script will error out at send 501. Split your list across two days or upgrade to a Google Workspace account.
  • Forgetting to strip the BOM character from CSV exports. If you imported your list from a CSV, the first cell sometimes contains an invisible byte-order mark that breaks the first placeholder replacement. Open the cell and retype the header manually if you suspect this.
  • Sending HTML-formatted email with the plain-text method. GmailApp.sendEmail() with just a body string sends plain text. If you paste HTML into the body, recipients see raw tags. Use the htmlBody option parameter for HTML: GmailApp.sendEmail(email, subject, body, {htmlBody: htmlBody}).
  • No unsubscribe link. Even for 50-person outreach lists, a plain “reply to unsubscribe” line at the bottom protects your sender reputation and keeps you on the right side of CAN-SPAM.

When To Level Up

The spreadsheet-plus-script approach works well up to a few hundred contacts per campaign. Past that point, the cracks start to show.

Gmail’s daily limits become a real constraint once your list grows. You end up splitting sends across multiple days and manually tracking which batch went out when. The sent column helps, but it is a workaround for a proper suppression list.

You also get no open tracking, no click tracking, and no automated follow-up sequences. If contact A opened your email three times but never replied, you have no idea. If contact B bounced, your script just writes “ERROR” and moves on — no bounce management, no list hygiene automation.

At that stage, tools like Lemlist or GMass give you a proper sending infrastructure with warm-up, sequence automation, and deliverability controls without requiring you to switch away from Google Sheets as your data source. Mailmeteor sits in between — it keeps the Sheets interface but adds tracking and a sending dashboard.

For a full comparison of tools that pick up where this workflow leaves off, browse /category/automation/ to find the right next step for your list size and outreach style. Also worth reading: best email outreach tools for solopreneurs and how to avoid spam filters when sending from Gmail.

Frequently Asked Questions

Can I use this with Microsoft Excel instead of Google Sheets?
Not directly — Apps Script only works inside Google Sheets. If you are on Excel, you would need a different approach, such as a Python script using the smtplib library or a tool like Yet Another Mail Merge that has an Excel-compatible workflow.

What happens if the script crashes halfway through my list?
Because the script writes “SENT” to the sheet immediately after each successful send, a crash mid-list means you can simply rerun the function. It will skip every row already marked “SENT” and pick up where it left off.

Will these emails look different from emails I write manually in Gmail?
Recipients see a standard Gmail email in their inbox. There is no “sent via mail merge” header visible to them. The email comes from your actual Gmail address, which is both a trust advantage and a reason to keep your list quality high — replies and bounces come back to your real inbox.

How do I add a plain-text unsubscribe link?
Add a line at the bottom of your bodyTemplate string: \n\nTo stop receiving these emails, reply with UNSUBSCRIBE. Then manage replies manually. For anything above 200 contacts, consider using a proper ESP with a one-click unsubscribe footer instead.

Is there a risk my Gmail account gets flagged for spam?
Yes, if your list has low-quality addresses, high bounce rates, or you send to people who never opted in. Keep your list clean (Step 2), send relevant content, and stay well under your daily limit. Starting with 50 to 100 sends per day and gradually increasing gives Gmail’s filters time to learn your sending pattern is legitimate.

Bottom Line

Sending personalized emails from a spreadsheet is genuinely achievable in an afternoon, even without a technical background. You structure your data in Google Sheets, write a template with double-curly placeholders, drop a single Apps Script function into the editor, test with one row, and run it against your full list. The sent column keeps you from duplicating sends, the try-catch keeps errors from killing your campaign, and the one-second sleep keeps Gmail from throttling you. For lists under a few hundred people and campaigns you run every few weeks, this workflow is faster and cheaper than any paid tool. When your volume grows or you need tracking and sequences, that is the moment to look at dedicated outreach platforms. Start browsing your options at /category/automation/ to see what fits your next stage of growth.