Store your form data in Google Spreadsheet

Dejan Mauer

Rate me:

4.81/5 (12 votes)

23 Jun 2014CPOL2 min read

157.8K

This is probably the simplest solution for storing web form data into database. We will use Google Forms and Google Spreadsheets as our 'database'.

Introduction

HTML <form> tag is available as a part of HTML from the beginning of the Internet. It is used on web pages to collect data from the users. After clicking Submit button, form data is sent to server, which usually stores them in one of the databases. While use of <form> tag is easy, server side part can be sometimes challenging to set up and operate. In this tip, I will show how we can use Google Docs Forms and Google Docs Spreadsheets as our 'database' for saving user inputs.

Using the Code

To use Google Forms and Spreadsheets as your server 'database', you will need to complete some easy steps. Google account is required to complete this task.

Go to docs.google.com and create a new form. In my sample, I have created form with the following fields: Email, Firstname, LastName and Company. To keep this simple, I've set all fields to type text and without any validation rules.
Click 'View Live Form' button and check the source of the form that is shown. As the source might not be very easy to read and understand, you will have to find a lot of information. First one is the form post URL. Search for '<form action' and mind URL in action value:
HTML
```
<form action="https://docs.google.com/forms/d/1PTIFxKDZBqKdrkGAgrsa28wus8FyP6XrMTWzabRuC18/formResponse" 
method="POST" id="ss-form" target="_self" 
onsubmit=""><ol style="padding-left: 0"> 
```
Find the unique names of each field. In my case, we have four different form fields. Look for the field name and mind for <input> tags. Attribute name is the parameter that we will need. Name value start with the 'entry.' keyword. The name of the email field in my example is entry_1402836733.
HTML
```
<input type="text" name="entry.1402836733" 
value="" class="ss-q-short" id="entry_1402836733" 
dir="auto" aria-label="Email  " title=""> 
```
You will need to find names of all the form field.

This is all the information you need. Now let's make our own form in HTML file. You can put the code below into the <body> tag.

HTML

<input id="Email" name="Email" 
type="text"  placeholder="Email Address">
<input id="First" name="First" 
type="text"  placeholder="First Name">
<input id="Last" name="Last" 
type="text"  placeholder="Last Name">
<input id="Company" name="Company" 
type="text" placeholder="Company">
<button id="ButtonSubmit" onclick="postContactToGoogle()" 
type="button" >Send</button>

In the final step, we will create JavaScript function that will post data to Google Form. In order to work, you need to reference jQuery.

JavaScript

<script>
    function postContactToGoogle() {
        var email = $('#Email').val();
        var first = $('#First').val();
        var last = $('#Last').val();
        var company = $('#Company').val();

            $.ajax({
                url: "https://docs.google.com/forms/d/1PTIFxKDZBqKdrkGAgrsa28wus8FyP6XrMTWzabRuC18/formResponse",
                data: { "entry_1402836733": email,
                "entry_1874720748": first, "entry_2092106103":
                last, "entry_944373055": company },
                type: "POST",
                dataType: "xml",
                statusCode: {
                    0: function () {
                        window.location.replace("ThankYou.html");
                    },
                    200: function () {
                        window.location.replace("ThankYou.html");
                    }
                }
            });
    }
</script>

Code is actually simple once we collected relevant data from Google Form source. First, I use jQuery to get user inputs and we store them in local variables. Then I initiate Ajax POST call to the URL from Google Form source. URL is called with key-value parameters. After I receive statusCode from server, I redirect to browser to another page.

Points of Interest

I found that Google Forms and Google Spreadsheets can be an excellent database foundation form small scale projects and prototypes. Doing data collection this way, it is easy to share the responses.