How to Save Form Data in Google Spreadsheet with Validation

• Download GoogleSpreadsheetWithValidation.zip - 3.8 KB

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:

   • https://docs.google.com/forms/d/1p9ov9fIMl4cFwKg513QYvG0QDI-E0vIDf52isyTUwTA/formResponse

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