Use Google Sheets as a database to collect emails

Dhruv Kar | November 1, 2016 -- GET UPDATES OF NEW POSTS HERE

It can be fatiguing to choose a tool to collect emails. Comparing costs, functionalities, limits on subscribers etc. is a lot of (non-value added) work.

There’s more than one solution that’s very good. Often times, I’ve ended up using more than one solution, which means I now have several lists in several services. This can be a pain to consolidate and act on.

One solution I’ve found is to use Google Forms & Google Sheets, and then funnel them into any number of email marketing service providers using Zapier. That way I have a master spreadsheet (which is easier to manipulate), and switching/adding providers is a cinch.

Steps

  1. To create a form, navigate to: https://forms.google.com and click the big “+” sign to create a blank, new form.
    New Google Form
    New Google Form
  2. Once you’ve created your form (I just created an email and a name field, both short answer), hit the “Send” button on the top right and copy the link to your clipboard from the middle tab.
    Copy Form Link
    Copy Form Link
  3. In an incognito Chrome window and paste in the link to your new form. You should not be signed into a Google account at this stage. Open up Chrome Developer Tools by:
    • Windows: F12 or Ctrl + Shift + i
    • Mac: Cmd + Opt + i
  4. Find and note the entry values in the Developer Tools console in the elements tab. You can search using Ctrl + f (Mac: Cmd + f) when in the console:
    • entry.<sometoken>: Google uses these fields to capture your responses in a form. There should be as many of these as there are fields. In our case there are two — an email and a name field.
      Find entry.<sometoken> fields
      Find entry.<sometoken> fields
  5. Next, submit a test entry using your form. We need the URL that this Google Form submits its data to. Note this URL. We’ll use it to build our own form.
    Note Submission URL
    Note Submission URL
  6. To create our custom form, you can use the template below. Substitute the bolded fields with the values you collected above. Replace http://yourdomain.com/confirmed with the page you want your visitor redirected to after submitting the form.

    NOTE: This code doesn’t include validating the form (e.g. what happens if the user leaves a field blank and submits the form). Email me if you want the form validation code.

    <script type="text/javascript">var submitted=false;</script>
    
    <iframe 
      name="hidden_iframe" 
      id="hidden_iframe" 
      style="display:none;" 
      onload="if(submitted) {window.location = 'http://yourdomain.com/confirmed';}">
    </iframe>
    
    <form id="subscribeForm" action="https://docs.google.com/forms/d/e/<token_string>/formResponse" method="POST" target="hidden_iframe" onsubmit="submitted=true;">
    <input id="fullname" type="text" name="entry.<another_token>" placeholder="Name">
    <input id="emailaddress" type="email" name="entry.<someother_token>" placeholder="Email">
    <input id="subscribe" type="submit" value="submit">
    </form>
    
  7. Now you should have a form that looks like the picture below. It’s basic so you can integrate and tailor it to the rest of your site using CSS. There’s plenty of resources to style a form using CSS. Here’s one.

    Email Capture Form
    Email Capture Form

Now, any collected emails can be funneled (or not) into any number of email marketing providers Zapier:

Depending on what I’m doing, I’ll often email my initial subscribers personally, and then later add in a Zap, when I can no longer keep up with signups.

comments powered by Disqus