Mail Merge from Google Sheets with Apps Script (Free Script)

A mail merge from Google Sheets with Apps Script lets you send a personal email to everyone on a spreadsheet, straight from your own Google Workspace address, without paying for an add-on. Apps Script is Google’s free tool for writing small programs that run inside Sheets, Gmail and Docs.

You’ll get a free, copy-paste script that sends in small batches every hour, skips bad rows, and marks each row as sent. You don’t need to know how to code.

Batches matter. Sending hundreds of emails at once from an address with little sending history is one of the quickest ways to land in spam.

What you need before you start

You need two things, and neither costs extra:

• A Google Workspace account on your own domain, like you@yourcompany.com. The script also runs on a free Gmail account, but with a much smaller daily allowance (see the limits table below).
• A Google Sheet with one row per person.

If you’d rather not touch code, Gmail’s built-in mail merge, YAMM and GMass all send from a sheet too. Our guide to how to mail merge in Gmail compares them.

How the free mail merge script works

The script reads your sheet, picks the next few rows that haven’t been emailed yet, sends them from your address, and writes "Sent" and the time into the row. A timer runs it again every hour until everyone has been emailed.

On each run it checks you’re inside your sending hours, counts what it sent in the last 24 hours so it never passes your daily cap, and skips rows with a broken address or an empty placeholder, saying why in the Status column. Because it only sends to rows with an empty Status, you can stop, fix something and restart without emailing anyone twice.

Step 1: Set up your Google Sheet

Open a new or existing Google Sheet and name the tab Contacts. Put these headers in row 1, spelled exactly like this:

You can add as many extra columns as you like. Any column header can become a placeholder in your email, written with double curly brackets, like {{Company}}. Placeholders must match the header exactly, including capital letters.

Before you go on, tidy the list. Remove duplicates, anyone who has asked not to hear from you, and anyone who wouldn’t expect an email from you. A clean list does more for your inbox placement than any script setting.

Step 2: Add the mail merge script to Google Sheets with Apps Script

1. In your sheet, click Extensions > Apps Script. A new tab opens with a file called Code.gs.
2. Delete everything in that file.
3. Copy the whole script below and paste it in.
4. Click the save icon (or press Ctrl + S, or Cmd + S on a Mac).

/**
 * Warmuply free template: mail merge from Google Sheets in hourly batches.
 * Sends from your own Google Workspace address with MailApp.
 * Edit the settings in CONFIG, then use the "Mail merge" menu in your sheet.
 */
const CONFIG = {
  SHEET_NAME: 'Contacts',      // the tab that holds your list
  EMAIL_COLUMN: 'Email',       // header of the column with email addresses
  STATUS_COLUMN: 'Status',     // the script writes "Sent", "Skipped: ..." or "Error: ..." here
  SENT_AT_COLUMN: 'Sent at',   // the script writes the send time here

  BATCH_SIZE: 10,              // most emails to send each hour
  DAILY_CAP: 50,               // most emails to send in any 24 hours
  START_HOUR: 8,               // only send from this hour...
  END_HOUR: 18,                // ...until this hour (24-hour clock, your script's time zone)

  SENDER_NAME: 'Your Name',    // the name people see next to your address
  SUBJECT: 'Quick question, {{First name}}',
  BODY: [
    'Hi {{First name}},',
    '',
    'Write your email here. You can use any column header as a placeholder, like {{Company}}.',
    '',
    'Thanks,',
    'Your Name',
    '',
    'If you would rather not get emails like this, just reply and say so.'
  ].join('\n')
};

/** Adds the "Mail merge" menu when the sheet opens. */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Mail merge')
    .addItem('Send a test to me', 'sendTest')
    .addItem('Start sending', 'startSending')
    .addItem('Stop sending', 'stopSending')
    .addToUi();
}

/** Sends the first unsent row's email to you only, so you can check it. */
function sendTest() {
  const data = readSheet_();
  const row = data.rows.find(function (r) { return !r.status; });
  if (!row) {
    SpreadsheetApp.getUi().alert('No unsent rows found.');
    return;
  }
  const msg = buildMessage_(row.values);
  if (msg.missing.length) {
    SpreadsheetApp.getUi().alert('Row ' + row.rowNumber + ' is missing: ' + msg.missing.join(', '));
    return;
  }
  const me = Session.getActiveUser().getEmail();
  MailApp.sendEmail(me, '[TEST] ' + msg.subject, msg.body, { name: CONFIG.SENDER_NAME });
  SpreadsheetApp.getUi().alert('Test sent to ' + me + ' using row ' + row.rowNumber + '.');
}

/** Creates the hourly trigger and sends the first batch now. */
function startSending() {
  stopSending();
  ScriptApp.newTrigger('sendBatch').timeBased().everyHours(1).create();
  sendBatch();
}

/** Removes the hourly trigger. Rows already sent stay marked as sent. */
function stopSending() {
  ScriptApp.getProjectTriggers()
    .filter(function (t) { return t.getHandlerFunction() === 'sendBatch'; })
    .forEach(function (t) { ScriptApp.deleteTrigger(t); });
}

/** Sends one batch. The hourly trigger runs this. */
function sendBatch() {
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(10000)) return; // another batch is still running
  try {
    const hour = Number(Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'H'));
    if (hour < CONFIG.START_HOUR || hour >= CONFIG.END_HOUR) return;

    const data = readSheet_();
    const pending = data.rows.filter(function (r) { return !r.status; });
    if (pending.length === 0) {
      stopSending(); // everyone on the list has been handled
      return;
    }

    const dayAgo = Date.now() - 24 * 60 * 60 * 1000;
    const sentLastDay = data.rows.filter(function (r) {
      return r.status === 'Sent' && r.sentAt instanceof Date && r.sentAt.getTime() > dayAgo;
    }).length;
    let allowed = Math.min(
      CONFIG.BATCH_SIZE,
      CONFIG.DAILY_CAP - sentLastDay,
      MailApp.getRemainingDailyQuota()
    );

    for (let i = 0; i < pending.length && allowed > 0; i++) {
      const row = pending[i];
      const email = String(row.values[CONFIG.EMAIL_COLUMN] || '').trim();
      if (!/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(email)) {
        writeStatus_(data, row, 'Skipped: invalid email', '');
        continue;
      }
      const msg = buildMessage_(row.values);
      if (msg.missing.length) {
        writeStatus_(data, row, 'Skipped: missing ' + msg.missing.join(', '), '');
        continue;
      }
      try {
        MailApp.sendEmail(email, msg.subject, msg.body, { name: CONFIG.SENDER_NAME });
        writeStatus_(data, row, 'Sent', new Date());
        allowed--;
      } catch (e) {
        writeStatus_(data, row, 'Error: ' + e.message, '');
        if (/too many times|limit/i.test(e.message)) break; // stop when Google says slow down
      }
    }
  } finally {
    lock.releaseLock();
  }
}

/* ---------- helpers ---------- */

function readSheet_() {
  const sheet = SpreadsheetApp.getActive().getSheetByName(CONFIG.SHEET_NAME);
  if (!sheet) throw new Error('No tab called "' + CONFIG.SHEET_NAME + '"');
  const values = sheet.getDataRange().getValues();
  const headers = values.shift().map(function (h) { return String(h).trim(); });
  [CONFIG.EMAIL_COLUMN, CONFIG.STATUS_COLUMN, CONFIG.SENT_AT_COLUMN].forEach(function (h) {
    if (headers.indexOf(h) === -1) throw new Error('Missing column: ' + h);
  });
  const statusIdx = headers.indexOf(CONFIG.STATUS_COLUMN);
  const sentAtIdx = headers.indexOf(CONFIG.SENT_AT_COLUMN);
  const rows = values.map(function (r, i) {
    const obj = {};
    headers.forEach(function (h, j) { obj[h] = r[j]; });
    return {
      rowNumber: i + 2,
      values: obj,
      status: String(r[statusIdx] || '').trim(),
      sentAt: r[sentAtIdx]
    };
  });
  return { sheet: sheet, statusIdx: statusIdx, sentAtIdx: sentAtIdx, rows: rows };
}

function buildMessage_(values) {
  const missing = [];
  function fill(text) {
    return text.replace(/\{\{\s*([^{}]+?)\s*\}\}/g, function (match, key) {
      const v = values[key];
      if (v === undefined || v === null || String(v).trim() === '') {
        if (missing.indexOf(key) === -1) missing.push(key);
        return '';
      }
      return String(v).trim();
    });
  }
  return { subject: fill(CONFIG.SUBJECT), body: fill(CONFIG.BODY), missing: missing };
}

function writeStatus_(data, row, status, sentAt) {
  data.sheet.getRange(row.rowNumber, data.statusIdx + 1).setValue(status);
  data.sheet.getRange(row.rowNumber, data.sentAtIdx + 1).setValue(sentAt);
  row.status = status;
  SpreadsheetApp.flush();
}

Step 3: Change the settings at the top

Everything you need to change is in the CONFIG block at the top of the script. Leave the rest alone.

The sending hours use your script’s time zone, shown under Project Settings in the Apps Script editor.

Write the email the way you’d write to one person. Keep it short, use one link at most, and skip attachments. Keep a simple line at the end telling people how to opt out, and stop emailing anyone who asks.

Step 4: Send a test, then start sending

1. Go back to your sheet and refresh the page. A new Mail merge menu appears after a few seconds.
2. Click Mail merge > Send a test to me.
3. Google asks you to authorize the script. Choose your account. If you see a screen saying the app isn’t verified, that’s normal for a script you wrote yourself: click Advanced, then the link to go to your project.
4. Check the test email in your own inbox. Look at the subject, the name, the placeholders and any link.
5. When it looks right, click Mail merge > Start sending.

The first batch goes out straight away. After that, the hourly timer takes over, and you can close the sheet. To pause, click Mail merge > Stop sending. Rows already marked "Sent" stay sent.

How to send batches per hour with Apps Script

To send batches per hour with Apps Script, you use a time-driven trigger: a timer that runs a function on a schedule, much like an alarm. The script’s Start sending option creates one that runs sendBatch every hour.

A few things about triggers are worth knowing:

• Timing is approximate. Google may run a trigger a little earlier or later than set, so "every hour" means roughly once an hour, not on the dot.
• Faster timers exist. Apps Script can also run every 1, 5, 10, 15 or 30 minutes. For a mail merge, hourly batches are easier to keep steady, and steady matters more than speed.
• The trigger runs as you. Emails always come from the account that pressed Start sending, even if a colleague has the sheet open.
• Failures come by email. If a run fails, Google emails you a summary. Every run is also listed under Executions in the Apps Script editor.

Apps Script email limits you should know

Apps Script has its own daily email quota, separate from the limit for emails you send by hand in Gmail. These are the numbers Google listed when we checked in October 2026:

Apps Script quotas reset 24 hours after your first email, not at midnight. Google also notes that trial accounts have lower limits. In Gmail, Workspace trial accounts are capped at 500 messages a day, and going over a Gmail sending limit can block sending for up to 24 hours. The script checks your remaining Apps Script quota before each batch, so it won’t try to go past it.

These are ceilings, not targets.

How many emails should you send per hour?

Send far fewer than the limits allow, especially from a new or quiet address. Gmail’s sender guidelines say to send at a steady rate, avoid sudden spikes, and start with a low volume to people who engage with you, then build up slowly.

The defaults in the script, 10 an hour and 50 a day, are a cautious place to start. They’re a starting point, not a rule. The right number depends on how long your address has been sending, how much you usually send, and how people react to your emails. Our guide to how many emails you can send a day without landing in spam goes through it in more detail.

If you want a day-by-day plan to raise your volume, the free warmup schedule calculator builds one for you.

What a script can’t fix

A script controls when and how fast you send. It can’t control the things that decide most of where your email lands.

Who you email. People who don’t expect your email are the biggest risk. When they click "Report spam", your reputation drops fast. Google asks senders to keep their spam rate in Postmaster Tools under 0.10% and never let it reach 0.30%. Postmaster Tools is Google’s free dashboard that shows how Gmail users react to your mail. Old, bought or scraped lists also bring bounces, and Google’s guidelines say not to buy addresses.

What the email says. Single words rarely send an email to spam on their own. But pushy, all-caps, link-heavy emails make people report you. Our guide to spam trigger words explains what matters and what doesn’t.

Your domain setup. SPF, DKIM and DMARC are settings on your domain that prove an email really came from you. If they’re missing, even a careful mail merge can land in spam. You can check all three with the free domain checker.

Troubleshooting common errors

To retry any skipped row, fix the problem and delete its Status. The next batch picks it up.

Before you hit send

Run through these checks before you click Start sending:

• Your list: everyone on it would expect to hear from you, and anyone who opted out is removed.
• Your email: it reads like a note to one person, with one link at most, no attachments and an easy way to opt out.
• Your domain: SPF, DKIM and DMARC are set up. Check them with the free domain checker.
• Your pace: BATCH_SIZE and DAILY_CAP suit how much your address usually sends.

This script sets the pace, and Warmuply tells you what that pace should be. It warms up your Google Workspace inbox in the background and shows you a safe-send number each day, which you can paste into DAILY_CAP. See how it works with Google Sheets and Apps Script.

Start a free 7-day trial. No card needed.