How to Send Personalized Gmail Newsletters Directly from Google Sheets

Email personalization increases open rates by up to 26% and click-through rates by 760% according to Campaign Monitor. But you don’t need expensive email marketing software to send personalized newsletters. Using Google Sheets and Apps Script, you can create your own mail merge system for free.

In this tutorial, I’ll walk you through creating a complete system to send personalized newsletters directly from Google Sheets.

What You’ll Build

By the end of this tutorial, you’ll have:

• A Google Sheet that stores your contacts and email template
• A script that sends personalized emails to each recipient
• The ability to track who has been emailed
• A simple user interface to trigger the sending process

Prerequisites

• A Google account
• Basic familiarity with Google Sheets
• No coding experience required (though it helps)

Step 1: Set Up Your Google Sheet

First, let’s create the structure for our newsletter system:

1. Create a new Google Sheet
2. Create three sheets (tabs) named:
   • Contacts: For your recipient list
   • Template: For your email template
   • Log: To track sent emails

Sheet 1: Contacts

Set up your Contacts sheet with these columns:

A: Email	B: First Name	C: Last Name	D: Custom Field 1	E: Custom Field 2	F: Sent
john@example.com	John	Smith	Gold Member	Web Developer
jane@example.com	Jane	Doe	Silver Member	Designer

Sheet 2: Template

In the Template sheet, add:

1. Cell A1: Subject line for your email
2. Cell A3: Your email template

Here’s a sample template:

Hello ,

I hope this email finds you well. As a , we wanted to share some exclusive updates with you.

Our latest resources for s are now available!

Check them out at https://example.com/resources

Best regards,
Your Name

The `` will be replaced with each contact’s data.

Sheet 3: Log

The Log sheet will be populated automatically by our script with:

• Timestamp
• Recipient email
• Success/Failure status
• Any error messages

Step 2: Create the Apps Script

Now, let’s create the script that powers our newsletter system:

1. Click Extensions → Apps Script
2. Delete any existing code
3. Paste the following script:

function sendPersonalizedEmails() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const contactsSheet = ss.getSheetByName("Contacts");
  const templateSheet = ss.getSheetByName("Template");
  const logSheet = ss.getSheetByName("Log");
  
  // Get email template and subject
  const emailSubject = templateSheet.getRange("A1").getValue();
  const emailTemplate = templateSheet.getRange("A3").getValue();
  
  // Get all recipient data
  const contacts = contactsSheet.getDataRange().getValues();
  const headerRow = contacts[0]; // First row contains headers
  
  // Prepare log sheet
  if (!logSheet.getRange("A1").getValue()) {
    logSheet.getRange("A1:D1").setValues([["Timestamp", "Recipient", "Status", "Notes"]]);
    logSheet.getRange("A1:D1").setFontWeight("bold");
  }
  
  // Track how many emails we've sent
  let emailCount = 0;
  
  // Loop through each row starting from row 2 (skip headers)
  for (let i = 1; i < contacts.length; i++) {
    // Check if already sent
    if (contacts[i][5] == "YES") continue; // Skip if already marked as sent
    
    const emailAddress = contacts[i][0];
    
    // Skip empty email addresses
    if (!emailAddress) continue;
    
    try {
      // Create personalized email by replacing placeholders
      let personalizedEmail = emailTemplate;
      
      // Replace each placeholder with actual data
      for (let j = 1; j < headerRow.length; j++) {
        const placeholder = `}`;
        personalizedEmail = personalizedEmail.replace(new RegExp(placeholder, 'g'), contacts[i][j]);
      }
      
      // Send the email
      GmailApp.sendEmail(
        emailAddress, 
        emailSubject, 
        personalizedEmail
      );
      
      // Mark as sent in the contacts sheet
      contactsSheet.getRange(i + 1, 6).setValue("YES");
      
      // Log success
      logSheet.appendRow([new Date(), emailAddress, "SUCCESS", ""]);
      
      // Count emails sent
      emailCount++;
      
      // Stay within Gmail quotas (100 emails per day for regular accounts)
      if (emailCount >= 100) {
        logSheet.appendRow([new Date(), "", "LIMIT REACHED", "Daily limit of 100 emails reached"]);
        break;
      }
      
      // Add a small delay to avoid hitting rate limits
      Utilities.sleep(1000);
      
    } catch (error) {
      // Log errors
      logSheet.appendRow([new Date(), emailAddress, "ERROR", error.toString()]);
    }
  }
  
  // Return summary
  return `${emailCount} emails sent successfully.`;
}

What This Script Does

1. Retrieves your email template and contacts: Reads data from your sheets
2. Personalizes each email: Replaces placeholders like `` with actual data
3. Sends emails one by one: Uses Gmail’s API to send each personalized message
4. Tracks progress: Marks each recipient as “sent” and logs the activity
5. Handles errors: Catches and logs any issues without stopping the entire process
6. Respects limits: Pauses between sends and stops after 100 emails (Gmail’s daily limit for personal accounts)

Step 3: Add a User Interface

Let’s make it easy to trigger the email sending with a custom button:

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Newsletter')
    .addItem('Send Personalized Emails', 'showSendConfirmation')
    .addToUi();
}

function showSendConfirmation() {
  const ui = SpreadsheetApp.getUi();
  const response = ui.alert(
    'Send Newsletters',
    'Are you sure you want to send personalized emails to all contacts who haven\'t received one yet?',
    ui.ButtonSet.YES_NO
  );
  
  if (response == ui.Button.YES) {
    const result = sendPersonalizedEmails();
    ui.alert('Email Status', result, ui.ButtonSet.OK);
  }
}

Add this code to your script and save it. This creates a new menu in your spreadsheet called “Newsletter” with the option to send emails.

Step 4: Test and Authorize

1. Save your script (give it a name like “Newsletter System”)
2. Reload your Google Sheet
3. You should see a new “Newsletter” menu item
4. Before using it with real emails, test with your own email address

Important: When you first run the script, Google will ask for permission to send emails on your behalf. You’ll need to authorize these permissions.

Step 5: Send Your Newsletters

Now you’re ready to send your personalized newsletters:

1. Make sure your Contacts sheet contains real data
2. Verify your email template looks good
3. Click Newsletter → Send Personalized Emails
4. Confirm that you want to proceed
5. The script will run, sending emails and updating the log

Advanced Customization Options

Once you’re comfortable with the basic system, consider these enhancements:

Add HTML Formatting

For rich text emails with formatting, links, and images, modify the script to send HTML emails:

GmailApp.sendEmail(
  emailAddress, 
  emailSubject, 
  "", // Plain text fallback (empty here)
  {
    htmlBody: personalizedHtmlEmail,
    name: "Your Newsletter Name" // The sender name that appears
  }
);

Add Attachments

Add PDF brochures or images to your emails:

// Get a file from Google Drive
const fileId = "YOUR_DRIVE_FILE_ID";
const attachment = DriveApp.getFileById(fileId);

GmailApp.sendEmail(
  emailAddress, 
  emailSubject, 
  personalizedEmail,
  {
    attachments: [attachment.getBlob()],
    name: "Your Newsletter Name"
  }
);

Schedule Regular Newsletters

Set up a time-driven trigger to send newsletters automatically:

1. In the Apps Script editor, click the “Triggers” icon (clock)
2. Click “Add Trigger”
3. Choose:
   • Function: sendPersonalizedEmails
   • Event source: “Time-driven”
   • Type of time: “Week timer” (or your preferred schedule)
4. Save the trigger

Troubleshooting

Common Issues

1. Emails not sending: Check your Gmail quota (100 emails/day for personal accounts)
2. Placeholder not working: Ensure placeholder names match exactly with column headers
3. Script timeout: For large lists, consider breaking into batches or using triggers

Gmail Limitations

• Personal Gmail accounts: 100 emails per day
• Google Workspace accounts: 1,500+ per day (varies by plan)
• Emails sent through Apps Script count toward your daily quota

Conclusion

You now have a powerful, free alternative to expensive email marketing tools. This Google Sheets mail merge system is perfect for:

• Small business newsletters
• Club or organization announcements
• Classroom communications
• Event invitations and updates

The best part? You have complete control over your data and can customize every aspect of the system.

What’s Next?

In future tutorials, we’ll cover:

• Building a subscription/unsubscription system
• Adding email tracking to see who opened your newsletters
• Creating a template library for different types of communications

Have you used Apps Script for email automation before? Share your experiences or questions in the comments below!

---

Want to explore more Google Sheets automation? Check out our guide to Top 10 Google Sheets Time-Saving Scripts.