Click here to Skip to main content
13,259,427 members (53,073 online)
Click here to Skip to main content
Add your own
alternative version

Stats

8.8K views
137 downloads
6 bookmarked
Posted 11 Sep 2015

How to Save Form Data in Google Spreadsheet with Validation

, 11 Sep 2015
Rate this:
Please Sign up or sign in to vote.
The easiest way to send form data to Google Docs/Spreadsheet from your web page, using your own form and AJAX. Nowadays, Google Docs/Drive is a widely used service for data storage and documents sharing.

Introduction

How to save your form data in Google Spreadsheet with Validation. You need to use <form>Tag, Google Docs Forms and Google Docs Spreadsheets. I will show you how we can use Google Spreadsheet as database and store form data.

Using the Code

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

After getting all Id fields. put all these Ids in JavaScipt function as given below.

  1. Go to gmail account login panel, login to Google account.
  2. Go to docs.google.com and create a new form. For example, I have created one form with the following fields: Email, Name and Mobile. Set all fields to type text and without any validation.
    Note:- For Dropdownlist,  Please choose Multiple choice in Question Type.

  3. Now on top menu, you will see 'View live form' which shows your form, 'View Responses' which show Google Spreadsheets in which you can see your data.
    You need to get the id of all fields. In my example, email has id entry_1218088147.
  4. Now, you have to make a form like:
    <form id="form1" runat="server">
    <div>Email
     <asp:TextBox ID="email" runat="server" 
     MaxLength="50"></asp:TextBox>
     <asp:RequiredFieldValidator ID="RequiredFieldValidator2" 
     runat="server" ControlToValidate="email"
    SetFocusOnError="true" ValidationGroup="V1" 
    ErrorMessage="Please Enter Email"></asp:RequiredFieldValidator>
    <asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server" 
    ErrorMessage="Please Enter Valid Email" ControlToValidate="email"  
    SetFocusOnError="true"
    ValidationExpression="\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*" 
    ValidationGroup="V1"></asp:RegularExpressionValidator><br />
    
    Name
    <asp:TextBox ID="name" runat="server" 
    MaxLength="100"></asp:TextBox>
    <asp:RequiredFieldValidator ID="RequiredFieldValidator3" 
    runat="server" ControlToValidate="name"
    SetFocusOnError="true" ValidationGroup="V1" 
    ErrorMessage="Please Enter Name"></asp:RequiredFieldValidator><br />
    
    Mobile
    <asp:TextBox ID="mobile" runat="server"  
    MaxLength="10"></asp:TextBox> 
    <asp:RequiredFieldValidator ID="RequiredFieldValidator1" 
    runat="server" ControlToValidate="mobile"
    SetFocusOnError="true" ValidationGroup="V1" 
    ErrorMessage="Please Enter Mobile"></asp:RequiredFieldValidator>
    <asp:RegularExpressionValidator ID="RegularExpressionValidator2" 
    runat="server" ControlToValidate="mobile" ValidationGroup="V1" 
    ValidationExpression="^[0-9]{10}$" 
    ErrorMessage="Please Enter 10 digits Mobile"> 
    </asp:RegularExpressionValidator><br />
    
    <asp:button ID="ButtonSubmit" ValidationGroup="V1" 
    runat="server" text="Submit" onclick="ButtonSubmit_Click"  />  
    </div>
    </form>
  5. Now, create a JavaScript function that will save the form data in Google SpreadSheet and you need to reference JQuery.
    <script type = "text/javascript"
    src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
    

    Url: Replace your form URL with this:

    <script>
     function clear() {           
                document.getElementById("<%=email.ClientID %>").value = "";
                document.getElementById("<%=mobile.ClientID %>").value = "";
                document.getElementById("<%=name.ClientID %>").value = "";         
                document.getElementById("email").focus();
                return true;
            }
    
    
    function SaveDataInGoogleSpreadsheet() {    
                var email = $('#<%=email.ClientID %>').val();
                var name = $('#<%=name.ClientID %>').val();
                var mobile = $('#<%=mobile.ClientID %>).val();
                $.ajax({
                    url: "https://docs.google.com/forms/d/1p9ov9fIMl4cFwKg513QYvG0QDI-E0vIDf52isyTUwTA/formResponse",
                    data: { "entry_1218088147": email,
                        "entry_1128153647": name,
                        "entry_20095427": mobile
                    },
                    type: "POST",
                    dataType: "xml",
                    statusCode: {
                        0: function() {                     
                            alert('ThankYou For Submission');
                        },
                        200: function() {
                            alert('ThankYou For Submission');
                        }
                    }
                });    
               clear();       
            }
    </script>

    Ajax URL is called with key-value parameters. After submission is done. you will receive StatusCode from server, O and 200 means data saved successfully.

  6. On button_Click event, write this code for calling the JavaScript function after all validation is false.
    ScriptManager.RegisterStartupScript(this, GetType(),
    "SaveDataInGoogleSpreadsheet", "SaveDataInGoogleSpreadsheet();", true);
    

That's all. Enjoy the code & have fun.

Points of Interest

Google Forms, Google Docs and Google Spreadsheets are interesting databases for small scale projects.

License

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

Share

About the Author

Rohit Nanda
Software Developer
India India
A life-long-learner, Punjabi music fan. Lives in New Delhi and works as a Software Developer using Microsoft .Net Technologies.

Has years of successful records serving mid and large scale .NET applications in domestic and international client environment.
Expertise in different areas of software development life cycles and Database Architecture.

You may also be interested in...

Pro
Pro

Comments and Discussions

 
QuestionDoesn't work with explorer and firefox browsers Pin
Member 120931691-Nov-15 22:08
memberMember 120931691-Nov-15 22:08 
QuestionA couple of questions Pin
Member 188040314-Sep-15 3:50
memberMember 188040314-Sep-15 3:50 
AnswerRe: A couple of questions Pin
Rohit Nanda14-Sep-15 22:56
memberRohit Nanda14-Sep-15 22:56 
GeneralMy vote of 5 Pin
Humayun Kabir Mamun11-Sep-15 3:03
memberHumayun Kabir Mamun11-Sep-15 3:03 
GeneralRe: My vote of 5 Pin
Rohit Nanda13-Sep-15 22:16
memberRohit Nanda13-Sep-15 22:16 

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

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.171114.1 | Last Updated 11 Sep 2015
Article Copyright 2015 by Rohit Nanda
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid