Click here to Skip to main content
Click here to Skip to main content

Store your form data in Google Spreadsheet

, 23 Jun 2014
Rate this:
Please Sign up or sign in to vote.
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.

  1. 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.

  2. 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:
    <form action="https://docs.google.com/forms/d/1PTIFxKDZBqKdrkGAgrsa28wus8FyP6XrMTWzabRuC18/formResponse" 
    method="POST" id="ss-form" target="_self" 
    onsubmit=""><ol style="padding-left: 0"> 
  3. 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.
    <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.

  4. 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.
    <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> 
  5. 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.
        <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.

History

I never liked history teachers. Wink | ;)

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Dejan Mauer
Software Developer (Senior) CENT SI d.o.o.
Slovenia Slovenia
I have strange hobby - programming.
While I am not programming I am tasting some good beers Wink | ;)
Follow on   Twitter

Comments and Discussions

 
QuestionWindow location / page to apear after submit PinmemberMember 1101982718-Aug-14 5:15 
GeneralMy vote of 5 Pinmemberkhanhamid92215-Jul-14 11:51 
GeneralMy vote of 4 PinprofessionalBrian A Stephens26-Jun-14 2:42 
GeneralRE: Store your form data in Google Spreadsheet PinmemberMember 1059181624-Jun-14 7:19 
QuestionMessage Removed Pinmembergalations2204-Jun-14 8:06 
QuestionThis is not working for me - nothing is getting saved to the google doc PinmemberMember 1082256215-May-14 20:56 
AnswerRe: This is not working for me - nothing is getting saved to the google doc PinmemberDejan Mauer15-May-14 23:10 
GeneralRe: This is not working for me - nothing is getting saved to the google doc PinmemberMember 1082256216-May-14 4:41 
GeneralRe: This is not working for me - nothing is getting saved to the google doc PinmemberMember 1082256216-May-14 5:48 
Questionis it code given enough?im using a notepad to test. unsuccessfull.please help PinmemberCik Miko ZaMija22-Apr-14 0:29 
AnswerRe: is it code given enough?im using a notepad to test. unsuccessfull.please help PinmemberDejan Mauer22-Apr-14 1:15 
GeneralRe: is it code given enough?im using a notepad to test. unsuccessfull.please help PinmemberMember 107950294-May-14 21:28 
Questionother entries which are not text PinmemberMember 107269625-Apr-14 6:39 
Questioncan we get the entry ID by code? Pinmembergraytay24-Mar-14 2:06 
AnswerRe: can we get the entry ID by code? Pinmembergraytay24-Mar-14 2:39 
Questioncheckbox PinmemberMember 1061643921-Feb-14 21:18 
AnswerRe: checkbox PinmemberDejan Mauer21-Feb-14 22:49 
Questionfetch from google Spreadsheet PinmemberJubayer Ahmed9-Feb-14 20:03 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140827.1 | Last Updated 23 Jun 2014
Article Copyright 2014 by Dejan Mauer
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid