Build an Automated Newsletter System with Google Sheets (No Third-Party Tools)

Managing a newsletter doesn’t have to involve expensive third-party services like Mailchimp or SendGrid. If you’re running a small to medium-sized mailing list (under 2,000 contacts), you can build a powerful automated newsletter system using just Google Sheets and Apps Script.

In this tutorial, I’ll show you how to create a system that:

✅ Automatically sends personalized newsletters
✅ Pulls content dynamically from your release notes
✅ Handles unsubscribe requests automatically
✅ Tracks which releases have been sent
✅ Works entirely within your Google Workspace

By the end of this guide, you’ll have a working automated newsletter system that costs nothing beyond your existing Google Workspace subscription.

The Problem with Third-Party Services

Most newsletter services charge based on subscriber count:

Mailchimp: $20-$350/month
SendGrid: $15-$120/month
ConvertKit: $29-$79/month

For small businesses or indie developers, these costs add up quickly—especially when you’re only sending occasional updates.

The Google Sheets Alternative

With Google Workspace, you get:

2,000 emails per day sending limit (500 for free Gmail)
Unlimited contacts in your spreadsheet
Full control over your data
Zero monthly fees for the newsletter system
Easy integration with your existing workflows

System Architecture

Our automated newsletter system consists of three components:

Google Sheet (Data Layer)
- Contacts database with subscription status
- Release notes with version history
Apps Script (Logic Layer)
- Newsletter sending automation
- Dynamic content assembly
- Subscription management
Web App (Unsubscribe Handler)
- One-click unsubscribe endpoint
- Updates sheet automatically

Step 1: Set Up Your Google Sheet

Create a new Google Sheet with two tabs:

Contacts Sheet

| Email              | Name       | Subscribed |
|--------------------|------------|------------|
| user@example.com   | John Doe   | 1          |
| jane@example.com   | Jane Smith | 1          |

Columns:

Email: Contact’s email address
Name: For personalization
Subscribed: 1 = subscribed, 0 = unsubscribed

ReleaseNotes Sheet

| PluginName   | Version | ReleaseDate | Features              | Sent |
|--------------|---------|-------------|-----------------------|------|
| Super Plugin | 2.5.0   | 2025-11-15  | Feature 1\nFeature 2  | 0    |

Columns:

PluginName: Product name
Version: Version number
ReleaseDate: Release date
Features: Feature list (newline-separated)
Sent: 0 = pending, 1 = sent

Step 2: The Core Script

Open Extensions → Apps Script and paste this code:

// Configuration
const SENDER_NAME = "Your Name";

// Get latest unsent release
function getLatestRelease() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("ReleaseNotes");
  const data = sheet.getDataRange().getValues();

  let latest = null;
  for (let i = 1; i < data.length; i++) {
    const [name, version, date, features, sent] = data[i];

    // Skip if already sent
    if (sent === 1) continue;

    // Find latest by date
    if (!latest || new Date(date) > new Date(latest.date)) {
      latest = {
        name, version, date, features,
        rowIndex: i + 1
      };
    }
  }
  return latest;
}

// Format features as HTML
function formatFeatures(text) {
  return text.split('\n')
    .filter(line => line.trim())
    .map(line => `<li>${line}</li>`)
    .join('\n');
}

// Main newsletter function
function sendNewsletter() {
  const release = getLatestRelease();

  if (!release) {
    SpreadsheetApp.getUi().alert('No unsent releases found!');
    return;
  }

  // Get contacts
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const contacts = ss.getSheetByName("Contacts");
  const data = contacts.getDataRange().getValues();

  let sent = 0;
  const now = new Date();
  const date = `${now.getMonth() + 1}/${now.getFullYear()}`;

  // Loop through contacts
  for (let i = 1; i < data.length; i++) {
    const [email, name, subscribed] = data[i];

    // Skip unsubscribed
    if (subscribed !== 1) continue;

    // Build email
    const subject = `${release.name} - Release ${release.version} - ${date}`;
    const features = formatFeatures(release.features);

    const html = `
      <html>
        <body style="font-family: Arial, sans-serif;">
          <h2>Hi ${name},</h2>

          <p>We're excited to announce <strong>${release.name} ${release.version}</strong>!</p>

          <h3>What's New</h3>
          <ul>${features}</ul>

          <p>Thanks for being a subscriber!</p>

          <hr>
          <p style="font-size: 12px; color: #666;">
            <a href="[UNSUBSCRIBE_URL]?email=${encodeURIComponent(email)}">
              Unsubscribe
            </a>
          </p>
        </body>
      </html>
    `;

    // Send email
    GmailApp.sendEmail(email, subject, '', {
      htmlBody: html,
      name: SENDER_NAME
    });

    sent++;
    Utilities.sleep(500); // Rate limiting
  }

  // Mark release as sent
  const releaseSheet = ss.getSheetByName("ReleaseNotes");
  releaseSheet.getRange(release.rowIndex, 5).setValue(1);

  SpreadsheetApp.getUi().alert(`Sent to ${sent} contacts!`);
}

Step 3: Add Unsubscribe Functionality

Add this function to handle unsubscribes:

function doGet(e) {
  const email = e.parameter.email;

  if (!email) {
    return HtmlService.createHtmlOutput('<h2>Invalid link</h2>');
  }

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Contacts");
  const data = sheet.getDataRange().getValues();

  // Find and unsubscribe
  for (let i = 1; i < data.length; i++) {
    if (data[i][0] === email) {
      sheet.getRange(i + 1, 3).setValue(0);

      return HtmlService.createHtmlOutput(`
        <html>
          <body style="text-align: center; padding: 50px; font-family: Arial;">
            <h2>✓ Successfully Unsubscribed</h2>
            <p>You will no longer receive newsletters.</p>
          </body>
        </html>
      `);
    }
  }

  return HtmlService.createHtmlOutput('<h2>Email not found</h2>');
}

Step 4: Deploy as Web App

To make the unsubscribe link work:

Click Deploy → New deployment
Select “Web app”
Settings:
- Execute as: Me
- Who has access: Anyone
Click Deploy
Copy the Web App URL

Replace [UNSUBSCRIBE_URL] in the script with your actual URL.

How It Works

Script finds latest release where Sent = 0
Loops through all contacts where Subscribed = 1
Generates personalized HTML email with dynamic content
Sends via Gmail API
Marks release as Sent = 1

Unsubscribe Flow

User clicks unsubscribe link with their email
Web app receives the request
Finds their row in the Contacts sheet
Changes Subscribed from 1 to 0
Shows confirmation page

Smart Features

Automatic Release Selection: The system automatically picks the latest unsent release by date. No manual configuration needed.

Prevents Duplicate Sends: Once a release is marked as sent, it won’t be sent again unless you manually reset the flag.

Rate Limiting: Built-in 500ms delay between emails prevents hitting Gmail’s rate limits.

Usage

Add release notes to ReleaseNotes sheet with Sent = 0
Add contacts to Contacts sheet with Subscribed = 1
Run sendNewsletter() function
Check results – release now marked Sent = 1

Testing

Before sending to your entire list:

// Add this test function
function sendTest() {
  const contacts = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName("Contacts");
  const testEmail = contacts.getRange(2, 1).getValue();

  // Copy newsletter logic but only send to testEmail
  // ...
}

Advantages of This System

1. Cost-Effective

No monthly subscription fees
Uses existing Google Workspace quota
Scales to 2,000 emails/day for free

2. Full Control

Own your contact data
Customize email templates freely
No vendor lock-in

3. Easy Management

Spreadsheet interface everyone understands
No learning curve for non-technical team members
Export contacts anytime

4. Automated Workflows

Auto-selects latest release
Tracks sent status automatically
Handles unsubscribes without manual intervention

Limitations to Consider

Gmail Sending Limits:

2,000 emails/day (Workspace)
500 emails/day (free Gmail)

Script Execution Time:

6-minute timeout limit
~700 emails maximum per execution

Basic Analytics:

No open rate tracking (requires external service)
No click tracking built-in
Manual log review for errors

For lists under 2,000, these limitations rarely matter.

Extensions and Improvements

Add Scheduling

Use Apps Script triggers to send automatically:

function setupTrigger() {
  ScriptApp.newTrigger('sendNewsletter')
    .timeBased()
    .everyWeeks(1)
    .onWeekDay(ScriptApp.WeekDay.MONDAY)
    .atHour(9)
    .create();
}

Track Opens (Advanced)

Add a tracking pixel:

const trackingPixel = `<img src="https://yourserver.com/track?email=${email}" width="1" height="1">`;

Requires external server to log requests.

Multiple Templates

Create different email templates for different content types:

function getTemplate(type) {
  const templates = {
    release: '...',
    announcement: '...',
    update: '...'
  };
  return templates[type];
}

Best Practices

Before Going Live

Test with yourself first – send to your own email
Check spam score – use mail-tester.com
Verify unsubscribe link – click it and confirm it works
Review Gmail quota – ensure you’re under daily limits
Backup your sheet – File → Make a copy

During Use

Monitor Status column for failed sends
Check execution logs (Apps Script → Executions)
Respect unsubscribes immediately
Keep content relevant to maintain engagement

Security

Never expose email list publicly
Use “Me” execution for web app (keeps data private)
Regularly backup your contacts sheet
Audit access to your Google Sheet

Conclusion

Building an automated newsletter system with Google Sheets and Apps Script gives you the power of professional email marketing tools without the recurring costs. This solution is perfect for:

SaaS founders sending product updates
Content creators with subscriber lists
Small businesses doing customer outreach
Indie developers announcing releases

The system we built handles everything automatically: finding the latest content, personalizing emails, managing subscriptions, and tracking what’s been sent. All within tools you already have.

Start small: Test with 10-20 contacts first. Once you’re comfortable, scale up to your full list. The beauty of this approach is you can customize every aspect to fit your exact needs—no template limitations, no feature paywalls, just pure flexibility.