How to Build an Email Newsletter Sender with Google Apps Script and Google Sheets

✉️ Part 1: Project Overview and Setup

🎯 Goal:

By the end of this section, you will:

• Understand what this project is about
• Create a Google Sheet to manage your newsletter content
• Open and name your Apps Script project

🧠 What You’ll Build

You’re going to build an automated email newsletter sender using Google Sheets and Google Apps Script.

You’ll:

• Store names, email addresses, and messages in a Sheet
• Write a script to send personalized emails with Gmail
• Create a simple custom HTML layout for your newsletter
• Add automation tools like a “Send Emails” menu button

This is just like what companies use for email campaigns — and you’re about to make your own version!

🛠️ Step 1: Create the Google Sheet

1. Open Google Sheets
2. Create a new blank spreadsheet
3. Name it: Newsletter Contacts
4. In the first row, add these column headers:

A1: Name  
B1: Email  
C1: Subject  
D1: Message  
E1: Status  

💡 What each column is for:

• Name – The recipient’s name (used to personalize the email)
• Email – Their email address
• Subject – The subject line for the email
• Message – The body of the email
• Status – Will update to “Sent” after emailing

Your sheet should look like this:

🧪 Step 2: Open the Script Editor

1. In your spreadsheet, click the menu:
   Extensions → Apps Script
2. A new tab will open the Apps Script editor.
   Rename the project:
   NewsletterSender
3. Delete any starter code (like myFunction) so you have a blank editor.

You’re now ready to write your first email-sending script in the next part!

✅ Recap

So far, you’ve:

• Created a spreadsheet to store newsletter data
• Added columns for Name, Email, Subject, Message, and Status
• Opened the Apps Script editor and set up your project

👉 Up Next: In Part 2, you’ll write a script to loop through the rows in your sheet and send a simple email using Gmail!

✉️ Part 2: Writing Your First Script

🎯 Goal:

By the end of this section, you will:

• Read data from your spreadsheet using Apps Script
• Loop through each row
• Send a basic email using Gmail
• Mark emails as “Sent” in the sheet

🖊️ Step 1: Start Your sendEmails() Function

In the Apps Script editor (from Part 1), paste the following code:

function sendEmails() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();

  for (let i = 1; i < data.length; i++) {  // Start at 1 to skip header row
    const row = data[i];
    const name = row[0];
    const email = row[1];
    const subject = row[2];
    const message = row[3];
    const status = row[4];

    if (status !== "Sent") {
      GmailApp.sendEmail(email, subject, message);
      sheet.getRange(i + 1, 5).setValue("Sent");  // Column E
    }
  }
}

🧠 What This Code Does

▶️ Step 2: Run the Script

1. Click the play ▶️ button in the toolbar to run sendEmails()
2. The first time you run the script, you’ll see a permission popup
   • Click Review Permissions
   • Choose your Google Account
   • Click Allow

✅ Your emails will now be sent, and “Sent” will appear in the Status column!

📌 Tip: Don’t Spam Yourself!

Use your own email address or a test one first. You can copy the same email multiple times with different names to see how it behaves.

🧪 Experiment Time

Try editing one of the names or messages in your sheet, delete the “Sent” text, and run the script again. Notice how only rows without “Sent” are processed? That’s your first bit of control logic!

✅ Recap

You now have a working script that:

• Reads data from a spreadsheet
• Sends plain-text emails via Gmail
• Prevents duplicate sends by checking the “Status” column

👉 Up Next: In Part 3, you’ll learn how to personalize your messages using each person’s name and create more dynamic content!

✉️ Part 3: Personalizing the Emails

🎯 Goal:

By the end of this section, you will:

• Personalize each message with the recipient’s name
• Use a template string to customize your email body
• Add a date/time stamp to each sent message
• Make your email sender tool feel more professional!

💬 Why Personalization Matters

Generic emails feel robotic. But adding a name, a kind greeting, or a reference to a recent event makes your message friendlier — and way more likely to be read.

In this part, we’ll update your script to use each person’s name in their email message.

🖊️ Step 1: Update Your Script to Use a Template

Replace your previous sendEmails() function with this improved version:

function sendEmails() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();

  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    const name = row[0];
    const email = row[1];
    const subject = row[2];
    const message = row[3];
    const status = row[4];

    if (status !== "Sent") {
      // Personalize the message
      const personalizedMessage = `Hi ${name},\n\n${message}\n\nBest regards,\nThe Newsletter Team`;

      GmailApp.sendEmail(email, subject, personalizedMessage);
      
      // Mark as Sent and add timestamp
      sheet.getRange(i + 1, 5).setValue("Sent");
      sheet.getRange(i + 1, 6).setValue(new Date()); // Optional: add sent timestamp in column F
    }
  }
}

🧠 What’s New?

Optional: Add a Header for Column F

In your sheet, add a new header in cell F1:

F1: Sent Time

Now you’ll know when each email was sent. ✨

🧪 Try It Out

1. Edit your spreadsheet so at least one row doesn’t say “Sent”
2. Run the updated sendEmails() function
3. Check your inbox — your message should include your name!
4. Confirm “Sent” and the timestamp appear in the sheet

✍️ Optional Challenge: Change the Signature

Try customizing the signature line in the personalizedMessage. For example:

"\nCheers,\nJamie from IT Club"

This is a great way to reinforce your school’s branding or personalize your classroom project!

✅ Recap

You now know how to:

• Customize emails with names from your spreadsheet
• Add a friendly greeting and signature
• Log the time each email is sent

👉 Up Next: In Part 4, you’ll learn how to use HTML formatting to style your emails — bold names, add links, and make your messages look amazing.

✉️ Part 4: Designing the Email in HTML

🎯 Goal:

By the end of this section, you will:

• Write an HTML version of your email
• Use bold text, paragraphs, and links
• Send styled emails with Gmail using Apps Script

💡 Why Use HTML?

Plain-text emails work, but HTML lets you:

• Add headings, colors, and links
• Break content into clear sections
• Make your message more visually appealing and professional

🖊️ Step 1: Update the Script with an HTML Template

Replace your sendEmails() function with the HTML version below:

function sendEmails() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();

  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    const name = row[0];
    const email = row[1];
    const subject = row[2];
    const message = row[3];
    const status = row[4];

    if (status !== "Sent") {
      // Create HTML email body
      const htmlBody = `
        <div style="font-family: Arial, sans-serif; line-height: 1.5;">
          <h2 style="color: #2c3e50;">Hi ${name},</h2>
          <p>${message}</p>
          <p>Best regards,<br><strong>The Newsletter Team</strong></p>
          <hr>
          <p style="font-size: 0.9em; color: #777;">
            This message was sent via the Newsletter Sender Project
          </p>
        </div>
      `;

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

      // Mark as Sent and timestamp
      sheet.getRange(i + 1, 5).setValue("Sent");
      sheet.getRange(i + 1, 6).setValue(new Date());
    }
  }
}

🔍 Key Differences

🧪 Try It Out

1. Edit a message in your sheet (e.g., include a link or long text)
2. Run the sendEmails() function
3. Check your inbox — your email should look more like a real newsletter

Example result:

Hi Jamie,
Thanks for joining our community!

Best regards,
The Newsletter Team

✍️ Optional Challenge: Add a Link

Update this line in your template:

<p>Check out our <a href="https://yoursite.com">latest blog post</a>!</p>

You can include this after the message content or as part of your signature.

✅ Recap

You now know how to:

• Send rich, styled emails using HTML
• Use GmailApp with htmlBody for advanced formatting
• Make your messages more engaging and professional

👉 Up Next: In Part 5, you’ll make it even easier to use by adding a custom menu to your spreadsheet, so anyone can send the newsletter with just one click.

🧭 Part 5: Adding a Custom Menu Button

🎯 Goal:

By the end of this part, you’ll:

• Add a custom menu to your Google Sheet
• Allow users to send emails without opening the script editor
• Make your tool more user-friendly and accessible

🖱️ Why Add a Menu?

Right now, you (or someone else) needs to:

1. Open the Script Editor
2. Run the sendEmails() function manually

Not ideal for everyday use! Adding a custom menu solves this by placing a “Send Emails” button right in the sheet’s toolbar.

🖊️ Step 1: Add the onOpen() Function

At the top of your script (or after your existing functions), add:

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

What this does:

• onOpen() runs automatically whenever someone opens the sheet
• It adds a new menu called “📬 Newsletter”
• Clicking “Send Emails” will run your sendEmails() function

✅ Step 2: Save and Reload

To test your menu:

1. Save the script (File → Save)
2. Return to the sheet and reload the tab
3. You should see a new menu called “📬 Newsletter” next to Help

Click it, and choose Send Emails — voilà! Your emails are sent.

🧪 Try It Out

1. Reset one of the rows in your sheet (delete “Sent” from column E)
2. Reload the sheet, click 📬 Newsletter → Send Emails
3. Watch it send right from the sheet — no Script Editor required!

🧠 Optional Upgrade: Add Confirmation Message

Update the sendEmails() function to show a pop-up when it’s done:

function sendEmails() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();
  let emailsSent = 0;

  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    const name = row[0];
    const email = row[1];
    const subject = row[2];
    const message = row[3];
    const status = row[4];

    if (status !== "Sent") {
      const htmlBody = `
        <div style="font-family: Arial, sans-serif; line-height: 1.5;">
          <h2 style="color: #2c3e50;">Hi ${name},</h2>
          <p>${message}</p>
          <p>Best regards,<br><strong>The Newsletter Team</strong></p>
          <hr>
          <p style="font-size: 0.9em; color: #777;">
            This message was sent via the Newsletter Sender Project
          </p>
        </div>
      `;
      GmailApp.sendEmail(email, subject, '', { htmlBody: htmlBody });
      sheet.getRange(i + 1, 5).setValue("Sent");
      sheet.getRange(i + 1, 6).setValue(new Date());
      emailsSent++;
    }
  }

  SpreadsheetApp.getUi().alert(`✅ ${emailsSent} email(s) sent successfully!`);
}

✅ Recap

You’ve just made your script:

• More professional
• Easier to run for any user
• One click away from sending your newsletter

👉 Up Next: In Part 6, we’ll wrap up the tutorial with a review checklist and some ideas for extending this project into a fully-featured newsletter tool.

✅ Part 6: Wrap-Up & Extensions

🎯 Goal:

In this final section, you’ll:

• Review what you’ve built so far
• Go through a checklist to ensure it’s working
• Explore ideas for how to extend your project even further

🔁 What You’ve Accomplished

By following this tutorial, you’ve created a fully functional email newsletter sender using Google Sheets and Apps Script.

Here’s a recap of the key features:

📝 Final Checklist

Use this checklist to make sure everything is ready:

✅ The spreadsheet has these headers: Name, Email, Subject, Message, Status, Date Sent
✅ Your script includes the sendEmails() and onOpen() functions
✅ The custom menu appears when you reload the spreadsheet
✅ Test emails send correctly and appear styled in your inbox
✅ Sent emails are tracked in the “Status” and “Date Sent” columns

If anything’s missing, go back to the relevant part and update your code.

🚀 Extension Ideas

Here are some cool ways to level up your project:

🔄 1. Add a “Reset” Function

Allow users to clear the “Status” and “Date Sent” columns for re-testing.

function resetStatus() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = sheet.getLastRow();
  sheet.getRange(2, 5, lastRow - 1, 2).clearContent();
}

Then update your onOpen() to include:

ui.createMenu('📬 Newsletter')
  .addItem('Send Emails', 'sendEmails')
  .addItem('Reset Status', 'resetStatus')
  .addToUi();

📦 2. Include File Attachments

Use GmailApp.sendEmail()’s attachments option to send PDFs, docs, etc.

🧠 3. Personalize Even More

Let users write a full HTML email in the spreadsheet using template variables like {{name}} and use replace() to customize each message.

🌐 4. Connect to a Google Form

Collect names and emails with a Google Form and auto-send a welcome email when they submit.

🎉 You’re Done!

You’ve built a tool that:

• Automates sending newsletters
• Looks polished and professional
• Can be reused, expanded, and improved upon