Skip to end of metadata
Go to start of metadata

About

A Google Form easily integrates in a web page by just embedding the code for calling their forms, in that way we only have to worry about placing it in the right place and Google makes the rest. Nevertheless, Google forms have a standard view which could break your site design, moreover, you'll probably want to manipulate the Form DOM through jQuery. As long as forms are embedded in an iFrame, you will not be able to do DOM Manipulation.

Solution: Build your own form and send data back to Google. But how do I do that? In this article we will describe some alternatives that can be used.

IMPORTANT

  • The solutions described on this article are alternatives that can be used,  are NOT formal ways to send data to the Google spreadsheet linked to a Google Form.
  • The alternatives described on this article are compatible with legacy browsers like IE7+ and modern versions of the most common browsers like IE, FF, Chrome, Safari, Opera.
  • Also works on mobile devices.
  • In our scenarios we are getting a Cross-site HTTP requests (Access-Control-Allow-Origin) but the data is recorded on the spreadsheet (our goal). Remember, both scenarios are alternative solutions, are not a formal way, since the only valid way is, of course, to use the form provided by Google.

UPDATE

Since June of 2017 we have received a couple of comments asking if the solution described on this article is still working, well, unfortunately we haven´t had time to validate it yet, feel free to add your results. We will keep alive this article as much as we can and as a basis for exploring new alternatives to deal with similar situations like the one described here.

 

A Google Form easily integrates in a web page by just embedding the code for calling their forms, a distinctive functionality from this platform is that it allows to save data from a web page to a spreadsheet (or other types of documents).

In this article, we will focus on the spreadsheets.

There are two ways to upload information to Google. 

  1. Thru the embedded Google Forms.
  2. Thru an Ajax request for posting data to Google.

Embedded Google Forms

The first case scenario is the easier way, but it has as inconvenient that you will not be able to modify this form. Nevertheless its great advantage consists on its easy embedding a single line of Code. Google will make the rest. When you use the embedded form, the form automatically is added on your web page and the data is sent to the spreadsheet automatically.

The image shows a common form with Google legends and you can change really few things on it. In order to get the Google Form as you can see in the last image with your spreadsheet created, create a form through the assistant in File > New > Form. Follow the assistant and you will get the html tag to embed in your code. If you already have your form you just need to retrieve the embedding code from Form Menu > Embed form. Put the code in your html in the place you want the form to be rendered.

Note: this form can't be changed and it will be difficult to keep control over it. In order to have full and easy control over it, there is the second option, create your own form and send the data using an Ajax call.

Save data to Google Docs through Ajax

We used this second option to allow users to provide their contact data for newsletter subscriptions. And the data was saved on a Google Spreadsheets.

Scenarios

We identified two scenarios "version A" and "version B". It is quite simple to differentiate between both of them, simply by looking at the forms, version A contains on its footer the legendwhile the version B has the legend "Powered by Google Drive"


Another option to differentiate them is through the destination (or update) address, being https://spreadsheets1.google.com/formResponse for the version A and https://docs.google.com/ for version B.

Solution on details

In order to reach our spreadsheet is necessary to know the associated form key from Google (also we can get the embed address where we can find it). It can be obtain by opening the spreadsheet, click on "Form" menu > "Embed form in a web page". When the form loads in a new browser tab you can get the form key. In version A, a modal window will be opened, showing the iframe code to embed the form, you need to look for the parameter "formkey" and copy that key. In version B the form will be opened in a new tab, you will get the direct URL access to the form. The form key can be found as the number/letter sequence after "forms/d/" and before "/viewform".

Ajax request

The Ajax request must contain the next settings:

  • url: the url address for the google service, it can include or not the form key according to the update scenario.

On version A: "https://spreadsheets1.google.com/formResponse". Note: apparently, it also works without the number one in the "spreadsheets" part of the URL.

Note: Since July 6, 2014, we discovered that for version A, the url: https://spreadsheets1.google.com/formResponse, IS NOT VALID anymore, instead we needed to use: https://docs.google.com/spreadsheet/formResponse


On version B: "https://docs.google.com/a/YOURDOMAIN.com/forms/d/XXXXXXX/formResponse", where XXXXXXX must be replaced by the form key

Examples:

      • url: "https://spreadsheets1.google.com/formResponse"
        • Since July 6, 2014: "https://docs.google.com/spreadsheet/formResponse"
      • url: "https://docs.google.com/a/YOURDOMAIN.com/forms/d/15QFO2VE44-9gAwcJeTPPWvxAX7v_1Ye9qmjdX2VzLBw/formResponse"

 

  • data: a JSON array containing the information we want to store in our spreadsheet.

For version A is necessary to provide the form key along with the data. The JSON keys will identify the column where the information will be stored in our spreadsheet. The adequate syntax is shown below:

data: {formkey: "xxxx", "entry.0.single": var1, "entry.1.single": var2, "entry.2.single": "yyyy", "entry.3.single": var3}

Note

Data have the form "entry.x.single", where x corresponds to the column where the data will be stored.

For version B, only is necessary to provide the data (be careful, do not provide the formkey in this scenario). The adequate syntax is:

data: {"entry.1" : var1, "entry.2" : var2, "entry.3": "A String"}

Note

In version B, you will only provide the keys under the form "entry.x" where x corresponds to the column where the data will be stored.

For both version, if you have required fields, they must be passed in the data, and must match the entry number with the column number in the spreadsheet.

 

  • type: "POST"
  • dataType: "xml"
  • statusCode:  Version A returns a statusCode = 200 when the request is properly handled, while in version B returns a statusCode = 0.

Code example

Consider the next HTML form:

<div id="form" class="contact-us-form">
	<div class="title">
		<strong>Have any questions?</strong>
	</div>
	<div class="subtitle">
		<strong>Drop us a line</strong>
	</div>
	<form id="callus" target="_self" onsubmit="" action="javascript: postContactToGoogle()">
		<fieldset>
			<label for="name">What's your name? *</label>
			<input id="name" type="text" name="name">
		</fieldset>
		<fieldset>
			<label for="email">What's your email? *</label>
			<input id="email" type="text" name="email">
		</fieldset>
		<fieldset>
			<label for="feed">Questions or Feedback?*</label>
			<textarea id="feed" name="feed"></textarea>
		</fieldset>
		<div style="text-align: right; padding-bottom: 15px;">* Required</div>
		<div style="width: 100%; display: block; float: right;">
			<button id="send" type="submit">
				Contact Us
			</button>
		</div>
		<div style="width: 100%; display: block; float: right; padding-top: 15px;">
			<div class="requestSubmited" style="display:none; text-align: center;">Your request has been sent!</div>
		</div>
	</form>
</div>

Note

The form calls a JavaScript function which after validating the fields will post the data to Google. Fields names can have the name we want just as the form.

 
<script type="text/javascript">
	function validateEmail(email) {
		var re = /^(([^<>()[\]\\.,;:\s@\"]+(\.[^<>()[\]\\.,;:\s@\"]+)*)|(\".+\"))@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\])|(([a-zA-Z\-0-9]+\.)+[a-zA-Z]{2,}))$/;
		return re.test(email);
	}"
	function postContactToGoogle(){
		var name = $j('#name').val();
		var email = $j('#email').val();
		var feed = $j('#feed').val();
		if ((name !== "") && (email !== "") && ((feed !== "") && (validateEmail(email)))) {
	 		$j.ajax({
				url: "https://docs.google.com/yourFormURL/formResponse",
				data: {"entry.1" : name, "entry.3" : email, "entry.4": feed},
				type: "POST",
				dataType: "xml",
				statusCode: {
					0: function (){

						$j('#name').val("");
						$j('#email').val("");
						$j('#feed').val("");
						//Success message
 					},
					200: function (){
						$j('#name').val("");
						$j('#email').val("");
						$j('#feed').val("");
						//Success Message
 					}
				}
			});
	 	}
		else {
			//Error message
		}
	}
</script>

The validateEmail function check if the parameter email is a valid string for email address. This function will be called next to the corresponding verification of fields not empty; if fields have values and email is a valid email address  then we call our ajax function, containing the url (explained in requirements  "yourFormURL" will be replaced by the text explained in that section), data, type, dataType and status code described.

If the Ajax call returns the status code number 200, it means we succeeded, Google also can return an empty status, with a 0 for status code number, in this case we have also succeeded. In these two cases we just need to clean up our form (if necessary) and show a success message. If the user left an empty field or the email is not a valid address, we show the corresponding error message. Remember that url and data will be built according to the scenario we face. 

 

UPDATE: Thanks to Rahul, Daniel, Joe Kale for their valuable feedback, we found some interesting issues that could arise when you experiment on sending data to Google docs through an Ajax call. If your customized form is reaching Google servers, but it is not saving the values check the next cases:

  1. It is possible that you need to check the entry identifiers, by using the Google form (via a code inspector, Net tab). Sometimes (we don't know the reason) Google uses specific entry ids, different to the consecutive one's we are using in our example, because of this reason, you need to use your Google Form to insert a record in your spreadsheet, and then using the Net Tab of your favorite code inspector, look for the entry names in the form's post call. You will notice if there is a different name when you find something like "entry.1023121230". In this case, you need to place this name just as used by the Google Form, instead of entry1, entry2, etc. (See next images).

    Google Form before sending DataAfter data has been sent, note the entry names in Firebug's Net tab

  2.  Verify the URL parameters on your Ajax call. In this scenario, when you copy the form key be careful to only copy that key, because when you form the url for your Ajax call, you need it ends with "/formResponse".
  3. Finally, sometimes Google inserts the values in a different sequence to the one provided. When this happens, just arrange the order in which you send the data on the Ajax call. Refer to the code next for more reference.

Replicating Joe Kale's scenario:

Demo code for non-sequential entry ids
<!DOCTYPE html>
<html>
    <head>
        <title>Test Google Forms</title>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.10.1/jquery.min.js"></script>
    </head>
    <body>
        <div>
            <div>
                <strong>Test Google Form</strong>
            </div>
            <form id="form" target="_self" onsubmit="" action="">
                <fieldset>
                    <label>Question 1</label>
                    <input id="qs1_op_1" type="radio" value="Yes" name="qs1">
                    <input id="qs1_op_2" type="radio" value="No" name="qs1">
                </fieldset>
                <fieldset>
                    <label>Question 2</label>
                    <input id="qs2_op_1" type="radio" value="Yes" name="qs2">
                    <input id="qs2_op_2" type="radio" value="No" name="qs2">
                </fieldset>
                <fieldset>
                    <label>Text</label>
                    <textarea id="feed" name="feed"></textarea>
                </fieldset>
                <div style="width: 100%; display: block; float: right;">
                    <button id="send" type="submit">
                        Send
                    </button>
                </div>
            </form>
        </div>
        <script type="text/javascript">
            function postToGoogle() {
                var field1 = $("input[type='radio'][name='qs1']:checked").val();
                var field2 = $("input[type='radio'][name='qs2']:checked").val();
                var field3 = $('#feed').val();

                $.ajax({
                    url: "https://docs.google.com/forms/d/FORM_KEY/formResponse",
                    data: {"entry.1023121230": field3, "entry.1230072460": field1, "entry.2113237615": field2},
                    type: "POST",
                    dataType: "xml",
                    statusCode: {
                        0: function() {
                            //Success message
                        },
                        200: function() {
                            //Success Message
                        }
                    }
                });
            }
            
            $(document).ready(function(){
                $('#form').submit(function() {
                    postToGoogle();
                    return false;
                });
            });
        </script>
    </body>
</html>

In this example, we have two radio buttons and a text box, whose values we want to record to Google Docs. Note that entries names now are formatted as entry.1023121230. In our Google Spreadsheet and in our HTML form we have placed the optional values as first and second columns (HTML controls) and the text as third column (HTML control), while in our Ajax call we send first the text value and then the option values.

UPDATE: Our teammate Ben Shoemate found another approach that solve the cross domain error, but just works in the case 2  (the current way Google forms works), we have tested that approach and works!!

Feel free to check it too: Tip: Adding a submission form to your blog

 

About the author

Enterprise Web Developer
Ernesto Rivera has more than 11 years of experience in the Information Technology industry. He is an experienced developer and his programming skills span a variety of programming languages and environments including Java, C++ and PHP. Ernesto earned his Bachelors and Masters degrees in Computer Science. He is currently a PhD candidate at ITESM (Instituto Tecnológico y de Estudios Superiores de Monterrey).