<img height="1" width="1" style="display:none" src="https://www.facebook.com/tr?id=1822615684631785&amp;ev=PageView&amp;noscript=1"/>

How to Get Subscriber Emails From a Web Form Into Google Sheets (Then GMass)

How to get subscriber emails from a web form into Google Sheets

It’s simple to use GMass for email marketing — put your subscribers in a Google Sheet, connect it to GMass, and send your newsletters and all your other correspondence.

But what’s the best way to process/collect those subscribers’ emails when they sign up through a form on your website?

How do you get those emails into a Google Sheet, then over to GMass?

And — even better — how can you drop those new subscribers into an automated welcome series?

In this article, we’ll go step-by-step through three solutions for collecting email addresses on your website and automatically adding them to a Google Sheet to use in GMass.

  • The free way that’s more complicated/technical
  • The free way that’s less complicated but has some tradeoffs
  • The least-expensive paid way

Fortunately, with whichever you choose, you can use the same quick technique to have GMass automatically send each new subscriber a welcome email or series.

Also, one more note before we jump in: We won’t be covering Google Forms in this article. While Google Forms is free and pretty easy way to get people into a Google Sheet, it’s really not intended for the use we’re talking about here — a signup form on your website. I’ve never seen a website using Google Forms in this way and this isn’t a situation where you would want to be the first.

Now… let’s get into these form-to-Sheets-to-GMass methods.

Emails from Web Form to Google Sheets and GMass: Table of Contents

The 100% Free (But Somewhat Technical) Method to Submit a Web Form Directly to Google Sheets

This method is totally free and keeps third-parties out of it, but will require lots of steps — and copying and pasting some code.

If you want to modify the form fields, you’ll need to make some changes to the code. And you’ll have to create your own HTML form and handle all of the styling of that form.

The techniques described here were largely created and shared by Levi Nunnink and Jamie Wilson, so big shout-out to those two.

Step 1: Create your Google Sheet to collect addresses

Create a new Google Sheet. You can give it whatever name you’d like. Set up your column headers (to save a whole bunch of headaches, don’t use spaces).

Create a new spreadsheet for your form

Step 2: Create a Google Apps script

In your Google Sheet, click into the Extensions menu, then Apps Script.

Apps Script in the Google Sheets menu

That should open a new browser tab with your Apps Script. Replace “Untitled Project” with a name for your script.

Rename Google Apps Script

Delete the default code in the editor. (function myFunction() { })

Delete default code

And paste in this code snippet (via Levi Nunnink on Github):

// Original code from https://github.com/jamiewilson/form-to-google-sheets
// Updated for 2021 and ES6 standards

const sheetName = 'Sheet1'
const scriptProp = PropertiesService.getScriptProperties()

function initialSetup () {
  const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  scriptProp.setProperty('key', activeSpreadsheet.getId())
}

function doPost (e) {
  const lock = LockService.getScriptLock()
  lock.tryLock(10000)

  try {
    const doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
    const sheet = doc.getSheetByName(sheetName)

    const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
    const nextRow = sheet.getLastRow() + 1

    const newRow = headers.map(function(header) {
      return header === 'Date' ? new Date() : e.parameter[header]
    })

    sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])

    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))
      .setMimeType(ContentService.MimeType.JSON)
  }

  catch (e) {
    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
      .setMimeType(ContentService.MimeType.JSON)
  }

  finally {
    lock.releaseLock()
  }
}

Here’s how it will look in the editor.

Code in the Google Apps Script editor

Save your process by clicking the floppy disk icon.

Save your Google Apps script

Step 3: Run your app and give it the necessary permissions

Click Run to run your app for the first time.

Run your Google Apps Script for the first time

A pop-up will ask you to authorize the app. Click Review permissions.

Review permissions for app

You’ll get the standard permissions pop-up from Google. Click Allow.

Give Google permissions

Note: You may get a warning from Google because the app is unverified. If so, click the link that says “Go to whatever-you-named-your-app (unsafe)” to finish the process.

Once the permissions are set, you should see this on your Apps Script execution log.

Permission successful notifications

Step 4: Create a trigger for the Apps Script

Click the clock icon on the left sidebar to open the Triggers dashboard.

Open the triggers section

Click the Add Trigger button.

Add a trigger

The settings you need:

  • Choose which function to run doPost
  • Choose which deployment should run Head
  • Select event source From spreadsheet
  • Select event type On form submit

Then click Save. (I had to once again authorize permissions at this step.)

Your trigger settings

Step 5: Deploy the script

Click the Deploy button on the top right, then choose New deployment from the dropdown menu.

Button to deploy script

Click the gear icon next to Select type and choose Web app.

Deploy web app

A form will appear. Fill it in with:

  • Description [anything you want]
  • Web app, execute as Me
  • Web app, who has access Anyone

Then click Deploy.

Deploy options

Copy the web app URL.

Copy the web app URL

Then click Done.

Step 6: Set up your HTML web form

Now you can create the HTML form for your site. Based on how we set up the spreadsheet, we’re only asking for a name and email. (The script we pasted earlier fills in the subscribe date automatically.)

In this code sample, replace my web app URL with yours.

Notice how it says input name="Name" and not input name="name" — the fields are case-sensitive, so they need to match the column headers in the Google Sheet exactly. Since I capitalized the column headers in my Google Sheet I’m capitalizing the names of the fields in my form.

<form 
  method="POST" 
  action="https://script.google.com/macros/s/AKfycbwT0RyDphHI3OtmoBmOnK1RlZFM6-vpwon07dLzBpMd-VKbZRlQHnSU3rWv/exec"
>
Name:<br />
<input name="Name" type="text">
<br />
Email:<br />
  <input name="Email" type="email">
  
  <button type="submit">Subscribe!</button>
</form>

HTML code for web form with Google Apps Script

Step 7: Add a success alert after someone subscribes

The process should work when you try it out — your HTML form now adds the subscriber to your Google Sheet. But after someone submits the form, it takes them to this screen pictured below, which is not particularly friendly or compelling.

The default script completion browser screen

So rather than sending users to that screen after they submit the form, you can trigger a small browser alert pop-up instead.

To do that, you’ll need to add an id value for your form as well as a bit of Javascript. Inside the <form> HTML tag, add id=myform. And in the header of your page (or an external Javascript file), add this script. (This script uses Fetch, which is supported in all modern browsers. It also uses FormData, which is supported in virtually all the browsers your visitors are likely to use.)

window.addEventListener("load", function() {
  const form = document.getElementById('myform');
  form.addEventListener("submit", function(e) {
    e.preventDefault();
    const data = new FormData(form);
    const action = e.target.action;
    fetch(action, {
      method: 'POST',
      body: data,
    })
    .then(() => {
      alert("You've subscribed — check your inbox shortly for a message!");
    })
  });
});

The end result should look something like this in HTML:

The new HTML with pop-up alert

And now when someone subscribes with the form, this is the (more attractive) result.

The alert in Chrome

Do I love Chrome’s default “This page says” dialogue? Not at all. Is it better than taking someone to an entirely new dead-end page like this form was doing without this Javascript? Definitely.

That’s it! You now have a web form that sends emails straight to a Google Sheet (which we’ll use for the automated welcome campaign in GMass). You can skip down to the GMass process now, or read on about the other solutions for going from web form to Google Sheets.

The Free (with Trade-Offs) Third-Party Method to Submit a Web Form Directly to Google Sheets

We’ve covered the 100% free version above. Now we’re going to cover a free third-party option via Tally.

This method is significantly easier than the Google Apps Script method. You can build way more complex forms way easier and don’t have to worry about HTML or CSS.

But… the trade-offs.

Your form will have “Made by Tally” branding unless you pay for the premium version ($29/mo or $290/yr). You won’t be able to add custom CSS on your form unless you upgrade. Your form will be hosted by an external third party. And we can’t guarantee this is going to be free forever, so one day you may need to get a paid plan.

Still, this is a nice, comfy middle ground between the totally free Google Apps Script and the paid options. Here’s how to create a web form that submits to Google Sheets in Tally.

Step 1: Create your form in Tally

I signed up for a Tally account before diving in to creating my form. On the Tally dashboard, I clicked the Create form button — it’s bright pink and tough to miss.

Create a form in Tally

I clicked the link on the next screen that said “Or start with a template.” From there I chose the lead generation form template.

Choosing a form template

I deleted the fields I didn’t need, which was most of them, and renamed the form.

My revised form

Once it looked good enough for the purposes of this test, I clicked the Publish icon.

Publishing the form

Step 2: Connect your form to a Google Sheet

That took me to the form’s dashboard screen. I clicked on Integrations, then the Connect button for Google Sheets.

Connecting to a Google Sheet

I gave Tally all the necessary Google permissions. Tally created a new Google Sheet for me automatically to collect form responses and linked to it in the Integrations page.

Tally created a new sheet

Step 3: Add your form to your website

Now to try it out. Back in the Share tab in Tally, I clicked the Standard Embed Form option.

Sharing the form

All looks good, so I clicked the button to Get the code.

Get the embed code

I copied the Embed code from there.

Copy the code embed

Now I whipped up another quick HTML page where I could embed the form.

Adding the form to an HTML page

Here’s how it looks in the browser (the formatting could use tweaking, but since this is just a test I’m not going to sweat it):

The form in my browser

I entered a name and email in the form and clicked the Subscribe button. I got the results message in the spot where the form originally was:

Submitted form

Step 4: Check out your results

And heading on over to the Google Sheet (created by Tally), I see my submission is in there as expected.

Email in the Google Sheet

Not too bad. Other features you might want to add to your form would be a checkbox for explicit opt-in and spam/bot prevention. And if you can live with the “Made with Tally” branding and without some other features like custom CSS, you can use Tally for free. (Also, if you’re obsessive about page speed, Tally is embedded from a third-party source, so it could have a small impact on page load speed.)

The (Cheapest) Paid Method to Submit a Web Form Directly to Google Sheets

There are several form building tools that offer a Google Sheets integration. All of these offer extremely limited free plans so you’ll need a paid plan if you want to collect any realistic volume of subscribers. And you’ll pay a lot for your subscriber volume. Two examples:

  • Paperform – $240/yr or $480/yr. The $240 plan only gets you 1,000 form submissions per month.
  • Typeform – $300/yr for just 100 responses per month; $600/yr for 1,000 responses per month.

Unless you have a large budget to blow, these paid options don’t make sense if you’re only making an email signup form. (If you’re using other forms on your site and need complex form building options, they become more viable.)

Sheet Monkey is not a form builder, but it is a third-party method to get subscribers from a web form into a Google Sheet. In many ways, it’s like a paid replacement for the Google Apps Script method with a few other features built in.

Sheet Monkey is far less expensive than form building tools; the paid plan is $60/yr.

Note: I’ll be using the free plan for this demo. The free plan only allows for 100 form submissions per year and is missing some features. All that is fine for testing purposes; in real-world deployments, you’ll almost certainly need a paid plan if you choose to go this route.

Step 1: Create a form in Sheet Monkey

I signed up for a free account by connecting my Google account to Sheet Monkey. I gave it all of the possible permissions it asked for.

Creating a SheetMonkey account

Then I clicked the yellow button to create a New Form.

Create a new Sheet Monkey form

I chose to save my data in a Google Sheet. Then chose their Mailing List sheet template option and clicked Save.

Form creation options

Step 2: Tweak any necessary form settings

After I created my form, Sheet Monkey took me to the settings for the form. You can manually set up a form submission confirmation page, spam protection, and notifications. I’d definitely recommend spam protection if you’re using this method. (I couldn’t use it because I was on the free plan.)

Options in Sheet Monkey

Step 3: Adding the form to your website

I created an HTML page and used the sample form SheetMonkey provided in the Embed HTML section of the settings.

I went with their suggested fields, but you could add any fields you want to the form; Sheet Monkey will detect them and add them to your Google Sheet.

Adding the Sheet Monkey form to a page

And here’s how that looks page I made looks in the browser. (My form is totally unformatted; you could format this however you like.)

In the browser

Testing out the form

I tested out the form by entering a name and email and hitting the Subscribe button.

I didn’t set up a results page since I was doing all of this locally; you would want to create a results if you were using Sheet Monkey on your actual website. So I got Sheet Monkey’s default results page instead.

Default post-submission page

And the info I submitted was in my Google Sheet as expected.

Data in the Google Sheet

This method worked great. But — the form design falls on you. If you want a tool with a form builder (like some of the ones mentioned earlier), the price goes up significantly.

Sending an Automated Welcome Email to Your New Subscriber with GMass

Regardless of which method you picked to get emails from a web form into a Google Sheet, the endgame is the same — you got your subscriber into your sheet, now it’s time to send them an automated welcome email or series.

And we’ll do that with GMass’s recurring campaign feature.

Step 1: Connecting to your new subscriber Google Sheet in GMass

Head to Gmail, open the Compose window, click into the Recipients field, then click the GMass icon.

Click the GMass icon

Choose From a Google Sheet.

From a Google Sheet

In the dropdown menu, choose the Google Sheet you’re feeding with your new web form.

I recommend turning off Update Sheet with reporting data in the Optional Settings area since you’ll probably use your subscriber sheet for more than one campaign.

Then click the Connect to spreadsheet button.

Connect to spreadsheet options in GMass

Step 2: Compose your welcome email

Now that you’re back in the Gmail compose window, write up your welcome email.

You can use mail merge with any of the columns in your Google Sheet.

You can also format this email however you want; to keep this example simple, I just went with basic text.

Compose your welcome email

Step 3: Create your email sequence

We’ll make our welcome sequence a drip series — where each subscriber gets every email in the sequence regardless of whether they open, click, or reply to any of the emails in the series.

If you want to send a single welcome email and not a series, skip this step.

Click the arrow next to the GMass button to open the campaign settings. Then head into the Auto Follow-ups section.

Auto follow ups section

Check the box under Stage 1. Set to send this email If Everyone after 3 days. (I picked three days arbitrarily; you may want a different time frame.)

Then click the radio button to Send rich-text email in new thread. For a drip series like this, it’s common to send each email with its own subject line (not have all the emails come as part of one thread).

Settings for welcome email

Now click the + icon to compose your follow-up message.

Add a follow-up

A new Gmail compose window will pop up. Erase the subject line and message and write your own. Do not alter the To address.

Compose your follow up

Once you’re happy with your follow-up message, click the GMass button. That “sends” your follow-up to GMass to get it into the system.

Use the GMass button to save

Now go back into the Auto follow-up settings in your original email.

Click the refresh icon 🔄 next to the dropdown box.

Refresh templates

Then select your newly-composed follow-up message from the dropdown.

Select your follow up

Repeat this process for each follow-up stage you want to send.

You can test the entire sequence as well by clicking the arrow next to the Send Test button in the settings, checking the box to Send all stages, then clicking Send Test.

Test the sequence

Step 4: Set up recurrence so GMass can monitor your Google Sheet for new subscribers

Now that you’ve created your welcome email and follow ups, we need to set up this campaign so it automatically goes out to new subscribers.

In the GMass settings for the welcome email, head into the Schedule section. We’ll set up recurrence using the Repeat option.

The repeat option for your campaign

Check the box next to Repeat, then set your campaign to repeat Every 1Hour to new rows.

That means: Every hour, GMass will check your Google Sheet to see if there are any new rows. If so, it will send the campaign to the email addresses in those rows (then send the follow-up drip emails as scheduled in the days that follow).

Repeat hourly

And yeah, that’s actually all it takes to make this recurring.

Step 5: Set your campaign live

Once you’re happy with your email and follow-ups, you’ve tested the campaign, and you’ve set up the recurrence, click the GMass button in your initial welcome email.

That will set the campaign live and it will continue to automatically go out to new subscribers until you decide to stop it.

Set your campaign live

What if you can’t wait between one and 59 minutes to send the first email?

GMass will check your Google Sheet once an hour for new rows. That means most new subscribers will get the welcome email relatively quickly from you.

But… there is a chance some will get the welcome email a whole 59 minutes later.

For many of us, the potential lag is fine (and most of the time will be a lot shorter than 59 minutes).

But if you want to immediately send an email whenever you get a new subscriber, you can do that with GMass’s Zapier integration. Depending on the volume of subscribers, you may need a paid Zapier plan.

With Zapier, set up a trigger for a new spreadsheet row.

Create a zap trigger of a new spreadsheet row

Then set up the action to send a transactional email in GMass.

Send a transactional email in GMass

Since transactional emails are business-focused, I made this message a subscription confirmation — basically proof to the subscriber that they’re on the list and letting them know further info will be coming soon.

Confirmation email from Zapier

Set your Zap live and now your subscribers will get an immediate message when they subscribe.

Emails from a Web Form to Google Sheets and GMass: Which Method to Choose?

Each of the methods we’ve covered has its own pros and cons. So as you’re choosing which method to use, here’s what you should keep in mind.

Free Google Apps Script

Pros:

  • 100% free
  • Won’t slow down page loading
  • Fully customizable (in form fields and style)
  • Doesn’t bring a third-party into the mix

Cons:

  • Can be technical, especially if you want to update/change the script or even form fields
  • You’ll have to figure out your own spam/bot prevention
  • You’ll have to build your own form in HTML
  • You’ll have to design your own form style

Free form building and processing with Tally

Pros:

  • Form builder to create attractive forms quick
  • No coding required
  • Spam/bot prevention and lots of other small, helpful features

Cons:

  • Tally’s branding on your form unless you upgrade to the $29/mo plan
  • You can style your form, but custom CSS is only part of the paid plan
  • Form is embedded from a third-party, which can affect page load speed
  • We can’t guarantee the free plan will always stay free (or be this generous)

Pros:

  • Won’t slow down page loading (form is not embedded)
  • Fully customizable
  • Spam/bot prevention option

Cons:

  • Paid plan, though the cheapest, still can be a lot without that much difference from the free Google Apps script option
  • You’ll have to build your own form in HTML
  • You’ll have to design your own form style

While we didn’t go step-by-step through the process of using an expensive form builder plan, there are some pros and cons to cover here as well.

Pros:

  • Create attractive forms quick
  • No coding required
  • Spam/bot prevention and lots of other small, helpful features
  • Can use for other types of forms on your site as well

Cons:

  • Very high cost, especially if you’re only using the form builder for email collection

Get started with GMass

Regardless of which form-to-Sheets option you choose, once you’re ready to create your automated welcome series — and all your other marketing emails — it’s time to bring GMass into the fold.

If you aren’t a GMass user, you can get started by downloading the Chrome extension from the Chrome Web Store. Hop on the free trial, which allows you to send up to 50 emails per day during your testing period — and when you’re ready to send a higher volume, you can upgrade to a paid plan.

GMass is a great option for sending your marketing emails, cold emails, other mass emails, and personal emails — it’s the only email platform that lets you hop seamlessly between all those types of emails. And it works right inside of Gmail, so it can easily slot into your regular email workflow.

See why GMass has 300k+ users and 7,500+ 5-star reviews


Email marketing. Cold email. Mail merge. Avoid the spam folder. Easy to learn and use. All inside Gmail.


TRY GMASS FOR FREE

Download Chrome extension - 30 second install!
No credit card required
Love what you're reading? Get the latest email strategy and tips & stay in touch.
   


1 Comment
  1. Do you really feel that this statement still applies?

    “Also, one more note before we jump in: We won’t be covering Google Forms in this article. While Google Forms is free and pretty easy way to get people into a Google Sheet, it’s really not intended for the use we’re talking about here — a signup form on your website. I’ve never seen a website using Google Forms in this way and this isn’t a situation where you would want to be the first.”

    If so, can you expand as to why? It seems very simple and naturally integrated to me.

Leave a Reply

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

Transform your Gmail account into an email marketing powerhouse

GMass is easy to learn and easy to use — but brings unbelievable email power into Gmail

Try GMass for free Then check out the quickstart guide to send your first mail merge email in minutes!

GMass

Share This