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

Posted by Krasen Slavov8 mins read

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

Codeable.io
blank

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.

Why Build Your Own Newsletter System?

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:

  1. Google Sheet (Data Layer)
    • Contacts database with subscription status
    • Release notes with version history
  2. Apps Script (Logic Layer)
    • Newsletter sending automation
    • Dynamic content assembly
    • Subscription management
  3. 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:

  1. Click Deploy → New deployment
  2. Select “Web app”
  3. Settings:
    • Execute as: Me
    • Who has access: Anyone
  4. Click Deploy
  5. Copy the Web App URL

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

How It Works

Newsletter Sending Flow

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

Unsubscribe Flow

  1. User clicks unsubscribe link with their email
  2. Web app receives the request
  3. Finds their row in the Contacts sheet
  4. Changes Subscribed from 1 to 0
  5. 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

Send Your First Newsletter

  1. Add release notes to ReleaseNotes sheet with Sent = 0
  2. Add contacts to Contacts sheet with Subscribed = 1
  3. Run sendNewsletter() function
  4. 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

  1. Test with yourself first – send to your own email
  2. Check spam score – use mail-tester.com
  3. Verify unsubscribe link – click it and confirm it works
  4. Review Gmail quota – ensure you’re under daily limits
  5. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *