How to Use Mail Merge in Google Sheets 

Sending personalized emails in bulk is a powerful way to connect with clients, students, or team members. While Google Sheets doesn’t have a built-in “Mail Merge” button like some other software, you can easily achieve this using Google’s powerful and free automation tool, Google Apps Script.

This guide will walk you through the entire process of setting up and running a mail merge directly from your Google Sheets.

What is Mail Merge?

Mail merge is a process that automates sending personalized emails or generating documents by pulling unique information from a spreadsheet (like names, dates, amounts) into a template. Instead of writing each email individually, you create one template with placeholders, and the merge tool fills in the details for each recipient automatically.

Prerequisites

• A Google Sheets spreadsheet with your data.
• A Gmail account (the same one you use for Google Sheets).
• Basic comfort with copying and pasting code (no advanced coding knowledge required!).

Step-by-Step Guide to Mail Merge in Google Sheets

Step 1: Prepare Your Data in Google Sheets

1. Create a new or open an existing Google Sheet.
2. Organize your data with clear column headers in the first row. Each column represents a unique piece of information you want to personalize (e.g., FirstName, Email, Company, InvoiceAmount).
3. Crucial: The column header names will be your placeholders in the email template. Avoid spaces; use camelCase or underscores (e.g., First_Name is better than “First Name”).
4. Fill in the data for each recipient under the appropriate headers.

Example Data Sheet (Sheet1):

Step 2: Create Your Email Template

1. In the same Google Sheet, create a new sheet and name it Template or EmailTemplate.
2. In this new sheet, you will define your email’s subject and body.
   • Cell B1: Enter your email subject.
   • Cell B2: Enter your email body.
3. Use placeholders that exactly match your data column headers, wrapped in ${ }. The script will automatically replace these with the actual data.

Example Template Sheet:

Step 3: Add the Apps Script Code

This is the engine that makes everything work.

1. From your Google Sheet menu, click Extensions > Apps Script.
2. A new tab will open. Delete any default code in the script editor.
3. Copy and paste the code below into the script editor.

javascript

function sendEmails() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // 1. GET DATA
  const dataSheet = ss.getSheetByName('Sheet1');
  const dataRange = dataSheet.getDataRange();
  const data = dataRange.getValues();
  const headers = data[0];
  
  // 2. GET TEMPLATE
  const templateSheet = ss.getSheetByName('Template');
  const subjectTemplate = templateSheet.getRange('B1').getValue();
  const bodyTemplate = templateSheet.getRange('B2').getValue();
  
  // 3. PROCESS EACH ROW
  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    let subject = subjectTemplate;
    let body = bodyTemplate;
    
    // 4. REPLACE PLACEHOLDERS WITH DATA
    for (let j = 0; j < headers.length; j++) {
      const placeholder = `\${${headers[j]}}`;
      const value = row[j] || ''; // Use empty string if cell is blank
      subject = subject.replace(new RegExp(placeholder, 'g'), value);
      body = body.replace(new RegExp(placeholder, 'g'), value);
    }
    
    // 5. GET RECIPIENT EMAIL
    const emailAddress = row[0]; // Assuming email is in the first column
    
    // 6. SEND EMAIL
    try {
      if (emailAddress) { // Only send if email exists
        GmailApp.sendEmail(emailAddress, subject, body, { htmlBody: body });
        console.log(`Email sent to: ${emailAddress}`);
      }
    } catch (e) {
      console.error(`Failed to send email to ${emailAddress}: ${e.toString()}`);
    }
    
    // 7. SLOW DOWN TO AVOID RATE LIMITS
    Utilities.sleep(1000); // Pauses for 1 second between emails
  }
}

Step 4: Run the Script and Authorize It

1. In the Apps Script editor, click the Save project icon (floppy disk) and give your project a name, like “Mail Merge”.
2. Click the Select function dropdown near the top toolbar and choose sendEmails.
3. Click the Run (►) icon.
4. A pop-up will appear asking you to Review Permissions. This is normal. The script needs access to your Gmail to send emails and your Sheets to read data.
5. Select your Google account and click Allow. Go through the security prompts (Google will warn you that the app isn’t verified—this is because you wrote it yourself. Click “Advanced” and then “Go to Mail Merge (unsafe)” to proceed).
6. Once authorized, the script will run.

What Happens Next?

The script will process each row in your data sheet, create a personalized email, and send it via your Gmail. You can view the execution log in the Apps Script editor to see a status for each email. The Utilities.sleep(1000); command helps avoid hitting Gmail’s rate limits for sending messages.

Beyond Email: Other Uses for Mail Merge

The same principle can be used for more than just emails:

• Generate Personalized Documents: Use Google Docs templates with similar placeholders and Apps Script to create and save unique contracts, invoices, or certificates.
• Create Address Labels: Pull address data from a Sheet to generate labels for holiday cards or shipping.
• Automate Feedback Requests: Send customized quiz links or feedback forms to students or clients after a project.

Conclusion

You can now efficiently send personalized bulk emails directly from Google Sheets using Apps Script. This method provides powerful flexibility without any cost.

Remember:

• Your data must be well-organized with clear headers.
• Placeholders in the template (e.g., ${FirstName}) must match the column headers exactly.
• Always review and authorize the script permissions the first time you run it.

This automation can save you hours of manual work, reduce errors, and help you communicate more effectively.