Photo by Nangialai Stoman on Unsplash

How to Create a Powerful Email Subscription Form and Automate Emails with Google Sheets (No Backend Required!)

In today’s digital world, collecting email addresses is crucial for growing your audience. Whether you’re a blogger, a small business owner, or running a website, having a reliable subscription form is a must. What if I told you that you could build a simple but powerful subscription form, store subscribers in Google Sheets, send thank-you emails automatically, and prevent duplicate sign-ups—all without needing a backend?

In this guide, I’ll walk you through how to:

Create a simple email subscription form with HTML.
Integrate the form with Google Sheets to manage your subscribers.
Automatically send thank-you emails to new subscribers.
Prevent multiple sign-ups with the same email address.

Step 1: Create the HTML Subscription Form

The first step is to create a basic form where users will input their email address to subscribe. Here’s the HTML for the form:

<form class="subscribe-form">
  <div class="subscribe-input">
    <input
      type="email"
      name="email"
      required="required"
      placeholder="Your Email Address"
    />
    <input
      type="submit"
      name="submit"
      id="subscribe1"
      value="Subscribe"
      class="btn-subscribe disableEnable-btn"
    />
    <!-- success and error message container -->
    <div id="subscribe-success-message"></div>
  </div>
</form>

This form includes:

An input field where users enter their email.
A subscribe button to submit the form.
A hidden container for displaying success or error messages.

Step 2: Set Up Google Sheets as Your Database

We’ll use Google Sheets to store subscriber information. Here’s how to do it:

Open Google Sheets and create a new spreadsheet.
Label the first row with headers like: Timestamp, Email, and Email Sent Date.
Go to Extensions > Apps Script.

In Apps Script, you can write JavaScript to automate email handling and database updates when someone subscribes. Here’s the code you’ll need:

function doPost(e) {
  // Get the active spreadsheet and the specific sheet where the data will be stored
  const subscribeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Subscribe data');

   // Extract the email from the form submission
  let data = e.parameter;
  let email = data.email;

  // Get all the data from subscribe form sheet
  const subscribeData = subscribeSheet.getDataRange().getValues(); // Get all data from the sheet
  const headers = subscribeData[0]; // Get the first row (headers)

  // Find the column indexes
  const headersIndexes = headers.reduce((acc, header, index) =>{
    acc[header.trim().toLowerCase()] = index;
    return acc;
  }, {});

  // Get the column index for "email" (find "email" column in the header)
  const emailColumnIndex = headersIndexes['email'];

  if (emailColumnIndex === undefined){
    return ContentService.createTextOutput("Email column not found!");
  }

  // Check if the email already exists in the the sheet using forEach
  let emailExists = false;
  subscribeData.slice(1).forEach((row) =>{
    // Get the column index for an email
  const emailColumnIndex = headersIndexes['email'] // Case-insensitive lookup for "email"

    if (row[emailColumnIndex] === email){
      emailExists = true;
    }
  });

  // If email exists, return a response saying "Already subscribed!"
  if (emailExists) {
    return ContentService.createTextOutput("You are already subscribed!");
  }

  // If email does not exits, continue with subscription process


  // Get the current timestamp for subscribe date
  let subscribeDate = new Date();

  // Send thank you message to the subscriber
  const subject = 'Then you for subscribing to themajhi.com';
  const message = `
     Hi there,

    Thank you for subscribing to receive actionable tips and other exclusive contents! We'll also send you notifications when we publish new content. We’re excited to have you on board.

    As a subscriber, you’ll receive the latest updates, news, and exclusive offers about our services. We strive to provide valuable information, and we look forward to staying connected with you.

    If you have any questions or are interested in learning more about our services, feel free to reach out. We'd love to assist you!

    Best regards,
    The Majhi - Digital Transformation
    www.themajhi.com
  `
  try{
  // Send email to the subscriber
    MailApp.sendEmail({
      to: email,
      subject: subject,
      htmlBody: message}
    );

    // Get the timestamp of when the email was sent (Email sent date)
    let emailSentDate = new Date();

      // Append the subscribe date, email, and email sent date to the sheet
      subscribeSheet.appendRow([subscribeDate,email,emailSentDate]);

    } catch (error){
      //Log any errors (optional)
      Logger.log("Error sending email: " + error.massage);
    }
return ContentService.createTextOutput('Thank you for subscribing!');
}

Step 3: Connect the Form to the Apps Script

Now, you need to make the subscription form functional by connecting it to the Apps Script you just created. This can be done with JavaScript and AJAX:

document.addEventListener("DOMContentLoaded", function () {
  const subscribeForm = document.querySelector(".subscribe-form");
  if (!subscribeForm) {
    console.error("subscribe forms not found.");
  }
  if (subscribeForm) {
    subscribeForm.addEventListener("submit", function (e) {
      e.preventDefault();
      const subscribeSuccessMessage = document.querySelector(
        "#subscribe-success-message"
      );
      const subscribeEnableDisablebtn =
        document.querySelector(".disableEnable-btn");
      const subscribeUrl =
        "YOUR_GOOGLE_APPS_SCRIPT_URL";

      // Change the text color of the success message
      subscribeSuccessMessage.style.color = "#4CAF50"; // Green color for success

      // Show initial processing message
      subscribeSuccessMessage.innerHTML =
        "Processing... Please wait for a seconds!";
      subscribeEnableDisablebtn.disabled = true;

      // Send form data to Google Apps Script
      fetch(subscribeUrl, {
        method: "POST",
        body: new FormData(subscribeForm),
      })
        .then((response) => {
          // Handle the response from the Apps Script
          return response.text(); // Get the response text
        })
        .then((responseText) => {
          // Change the text color of the success message
          subscribeSuccessMessage.style.color = "#4CAF50"; // Green color for success

          // Update the success message with the response from Apps Script
          subscribeSuccessMessage.innerHTML = responseText;
          subscribeSuccessMessage.style.display = "block"; // Ensure the message is shown

          // Clear the success message after 3 seconds
          setTimeout(function () {
            subscribeSuccessMessage.innerHTML = "";
          }, 3000);

          // Reset the form and enable the submit button again
          subscribeForm.reset();
          subscribeEnableDisablebtn.disabled = false;
        })
        .catch((error) => {
          console.error("Error!", error.message);
          // Optionally, show a message if an error occurs
          subscribeSuccessMessage.innerHTML =
            "There was an error processing your request. Please try again.";
        });
    });
  }
});

Make sure to replace YOUR_GOOGLE_APPS_SCRIPT_URL with the URL of your published Google Apps Script.

Step 4: Prevent Multiple Sign-Ups for the Same Email

This is handled within the Apps Script code above. The script checks whether the email already exists in the Google Sheets database before processing the subscription. If it’s a duplicate, it will return the message "You are already subscribed!" and prevent the email from being added again.

Step 5: Bonus—Send a Personalized Thank You Email

Once someone subscribes, they will automatically receive a thank-you email. This is handled by the MailApp.sendEmail() function in the Apps Script. You can customize the message to match your brand tone.

Conclusion

Now you’ve created a simple yet powerful subscription system that:

Allows users to subscribe via an email input form.
Prevents duplicate sign-ups by checking the email in the database (Google Sheets).
Sends an automatic thank-you email to new subscribers.

This setup is lightweight, easy to maintain, and doesn't require a backend, making it perfect for small to medium-sized websites. By automating this process, you can focus on creating great content while effortlessly building your email list.