Mail Merge in Google Sheets Without YAMM: Free Apps Script Alternative

Stop Paying $400/Year for Mail Merge: Build a Free, Reliable System in Google Sheets

You are paying up to $400 a year for brittle tools like YAMM that fail when you hit a low email threshold. Your client updates, sales outreach, and onboarding sequences get stuck. Your team wastes hours checking quotas or waiting for the "Daily limit exceeded" error to clear.

This is a solved problem. Stop paying for unreliable add-ons. This guide shows you how to build a free, robust mail merge system inside Google Sheets using Google Apps Script. It leverages your account's native quotas, provides real error handling, and costs you nothing to run.

Build vs. Buy: Why Apps Script Dominates Subscription Tools

For an Operations Manager, the math is simple. Subscription tools sell convenience but create dependencies and recurring costs. Building it yourself with Apps Script gives you total control, deep customization, and eliminates the recurring bill.

The technical advantage is clear. Apps Script uses the native MailApp class (Class MailApp) and your account's existing Gmail quota. This gives you direct control over error handling and batching—something a third-party wrapper cannot offer.

The Reliable Mail Merge Architecture

The script executes four critical steps:

1. Reads Data in Bulk: Fetches all contact data from the active Google Sheet in a single API call.
2. Iterates and Personalizes: Loops through each row in memory, personalizing a pre-defined email template.
3. Sends and Logs: Sends the email using your Gmail account via MailApp.sendEmail().
4. Updates Status in Bulk: Writes the status ("SENT", "ERROR", etc.) back to the sheet in a single API call to prevent duplicates and provide a clear audit trail.

This workflow includes logic to stop cleanly if it hits your daily Gmail quota and resume later.

this graph show - how the script below works

To handle Google's script execution limits (6 min/consumer, 30 min/Workspace), the process is designed to stop (K) and restart, picking up where it left off by only processing rows not marked 'SENT'.

The Core Script: Building the Engine

Prerequisites

1. Permissions: You need editor access to the Google Sheet.
2. Sheet Structure: Your active sheet must have these exact column headers, starting in A1: Email, Name, CustomField, Status. The script populates the Status column.
3. OAuth Scopes: The script requires authorization. When you first run it, a pop-up will ask for permission.

Setup & Code

1. Open your Google Sheet.
2. Go to Extensions > Apps Script.
3. Delete any placeholder code and paste the following two functions.
4. Save the project.

/**
 * Configuration settings for the mail merge script.
 */
const CONFIG = {
  // Set to a specific sheet name, or null to use the active sheet.
  SHEET_NAME: null,
  COLUMN_MAP: {
    EMAIL: 'Email',
    NAME: 'Name',
    CUSTOM_FIELD: 'CustomField',
    STATUS: 'Status',
  },
  STATUS_TEXT: {
    SENT: 'SENT',
    ERROR: 'ERROR',
    PENDING_QUOTA: 'PENDING - Quota Hit',
    SKIPPED_INVALID: 'SKIPPED - Invalid Email',
  },
  LOCK_TIMEOUT_MS: 10000, // 10 seconds
};

/**
 * Creates a custom menu in the Google Sheet UI to run the mail merge.
 */
function onOpen() {
  try {
    SpreadsheetApp.getUi()
      .createMenu('Mail Merge')
      .addItem('Send Emails', 'sendPersonalizedEmails')
      .addToUi();
    console.log('Mail Merge menu created successfully.');
  } catch (e) {
    console.error(`Failed to create Mail Merge menu: ${e.message}`);
    SpreadsheetApp.getUi().alert(`Error creating menu: ${e.message}`);
  }
}

/**
 * Sends personalized emails based on data in the active Google Sheet.
 * Uses LockService to prevent concurrent executions and handles errors and quotas.
 *
 * @throws {Error} If required columns are missing or the sheet is not found.
 * @returns {void}
 */
function sendPersonalizedEmails() {
  console.log('Starting mail merge process...');
  const startTime = new Date();

  // LockService prevents the script from running multiple times simultaneously.
  const lock = LockService.getScriptLock();
  try {
    if (!lock.tryLock(CONFIG.LOCK_TIMEOUT_MS)) {
      const message = 'Could not run mail merge. Another instance is already running.';
      console.warn(message);
      SpreadsheetApp.getUi().alert(message);
      return;
    }
    console.log('Script lock acquired.');

    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = CONFIG.SHEET_NAME ? ss.getSheetByName(CONFIG.SHEET_NAME) : ss.getActiveSheet();

    // --- Input Validation ---
    if (!sheet) {
      throw new Error(`Sheet not found. Check CONFIG.SHEET_NAME or active sheet.`);
    }
    console.log(`Processing sheet: "${sheet.getName()}"`);

    const dataRange = sheet.getDataRange();
    // Batch Read: Get all values at once to minimize API calls.
    const values = dataRange.getValues();

    if (values.length <= 1) {
      console.log('No data to process (only header or empty sheet).');
      SpreadsheetApp.getUi().alert('No data to process.');
      return;
    }

    const header = values[0];
    const emailIndex = header.indexOf(CONFIG.COLUMN_MAP.EMAIL);
    const nameIndex = header.indexOf(CONFIG.COLUMN_MAP.NAME);
    const customFieldIndex = header.indexOf(CONFIG.COLUMN_MAP.CUSTOM_FIELD);
    const statusIndex = header.indexOf(CONFIG.COLUMN_MAP.STATUS);

    if ([emailIndex, nameIndex, statusIndex].includes(-1)) {
      throw new Error(`Missing required columns. Ensure "${CONFIG.COLUMN_MAP.EMAIL}", "${CONFIG.COLUMN_MAP.NAME}", and "${CONFIG.COLUMN_MAP.STATUS}" exist.`);
    }

    let emailsSent = 0;
    const emailRegex = /.+@.+\..+/; // Simple regex for email validation.

    // --- Main Processing Loop (in-memory) ---
    // Skip header row (index 0) by starting the loop at 1.
    for (let i = 1; i < values.length; i++) {
      const row = values[i];
      const status = row[statusIndex];

      // Only process rows that have not been successfully sent.
      if (status === CONFIG.STATUS_TEXT.SENT) {
        continue;
      }

      const recipientEmail = row[emailIndex];
      if (!recipientEmail || !emailRegex.test(recipientEmail)) {
        console.warn(`Skipping row ${i + 1}: Invalid or empty email address.`);
        values[i][statusIndex] = CONFIG.STATUS_TEXT.SKIPPED_INVALID;
        continue;
      }

      const recipientName = row[nameIndex];
      const customField = row[customFieldIndex];

      // --- Simple Email Template ---
      const subject = `Update for ${recipientName}`;
      let body = `Hello ${recipientName},\n\n`;
      body += `Your custom field value is: ${customField}.\n\n`;
      body += `This email was sent via a free Apps Script solution.`;

      try {
        // Proactive Quota Check: Check remaining email quota before sending.
        const remainingQuota = MailApp.getRemainingDailyQuota();
        if (remainingQuota < 1) {
          console.warn(`Quota exhausted. Stopping script. Remaining: ${remainingQuota}`);
          values[i][statusIndex] = CONFIG.STATUS_TEXT.PENDING_QUOTA;
          break; // Exit the loop to prevent further attempts.
        }

        MailApp.sendEmail({
          to: recipientEmail,
          subject: subject,
          body: body
        });
        // Update status in the 2D array (in memory).
        values[i][statusIndex] = CONFIG.STATUS_TEXT.SENT;
        emailsSent++;
        console.log(`Email sent to ${recipientEmail} (Row ${i + 1})`);

      } catch (e) {
        // Log the full error for debugging.
        console.error(`Error sending to ${recipientEmail} (Row ${i + 1}): ${e.stack}`);
        // Update status with a truncated error message for the sheet.
        values[i][statusIndex] = `${CONFIG.STATUS_TEXT.ERROR}: ${e.message.substring(0, 250)}`;
      }
    }

    // Batch Write: Write all updated statuses back to the sheet in one operation.
    dataRange.setValues(values);
    console.log('Batch update of statuses complete.');

    const duration = (new Date() - startTime) / 1000;
    const summary = `Mail merge finished in ${duration.toFixed(2)}s. Sent ${emailsSent} emails.`;
    console.log(summary);
    SpreadsheetApp.getUi().alert(summary);

  } catch (e) {
    // Catch any top-level errors (e.g., permission issues, validation failures).
    console.error(`A critical error occurred: ${e.stack}`);
    SpreadsheetApp.getUi().alert(`A critical error occurred: ${e.message}`);
  } finally {
    // Always release the lock, even if errors occurred.
    lock.releaseLock();
    console.log('Script lock released. Execution finished.');
  }
}

To run it, refresh your spreadsheet. A new "Mail Merge" menu will appear. Click Mail Merge > Send Emails. The first time, you must grant permission to access your Sheet and send email on your behalf.

How to Avoid the "Daily Limit Exceeded" Error

The core technical constraint is Google's daily email quota. For Apps Script's MailApp service, this is 100 recipients/day for consumer Gmail accounts and 1,500 recipients/day for Google Workspace accounts. This is separate from the general Gmail sending limit.

Here is how the script handles this constraint:

1. Mandatory State Management: The Status column is the source of truth. If the script stops for any reason (quota hit, network error, 6/30-minute timeout), it can restart without emailing the same person twice.
2. Proactive Quota Checks: The script uses MailApp.getRemainingDailyQuota() before attempting to send. This prevents the script from failing on a quota error and allows it to stop cleanly, marking remaining rows as PENDING - Quota Hit.
3. Use Time-Based Triggers for Large Lists: For lists over a few hundred contacts, do not run the script manually. Set it up to run automatically.
   • In the Apps Script editor, go to the Triggers (clock) icon.
   • Click + Add Trigger.
   • Choose sendPersonalizedEmails to run.
   • Select Time-driven as the event source.
   • Set it to run on a Day timer at a specific time. This trigger will automatically process your list each day until every row not marked SENT has been processed.

Next Steps: Build It or Ship It

You now have a reliable, free mail merge system.

The code's above. Build it yourself this weekend.

Or book a call and I'll have it running by Friday with more features like templating engine, email verification, and many more . 30-day code warranty.