Submit HTML Form to Google Spreadsheet

The purpose of this tutorial is to use Google Forms to submit data from our HTML form into a Google Spreadsheet. We’ll be using jQuery to handle the actual submission, so make sure it is available to your page. You’ll also need to have access to some sort of developer tools during this tutorial. We used Google Chrome and the Chrome Developer Tools.

Also, the starting point for this tutorial is our Responsive Popup Contact Form, so please check it out if you need more context!

Here’s the relevant code we’re going to be working with:

// Submit to Google Form/Sheet

// replace this example URL
var googleFormsURL = “a url goes here”;

// replace these example entry numbers
var spreadsheetFields = {
“entry.1124515254”: name,
“entry.658678567454”: email,
“entry.4565663643”: message
}
$.ajax({
url: googleFormsURL,
data: spreadsheetFields,
type: “POST”,
dataType: “xml”,
statusCode: {
// Handle status code responses
0: function () {},
200: function () {}
}

This code should be placed in the submit function for your contact form. The Responsive Popup Contact Form example shows where you might place this code.

We’ve got some housekeeping to do before we can work with the code though. Come back to this once we get to that part of the tutorial. So here we go!

  • First, you’ll need to log into a Google account with access to Google Drive.
    • Navigate to [New] -> [More] -> [Google Forms]
    • 1
  • This will open up a new form. Go ahead and name the form something appropriate.
  • In order for this to work, we’ll need to make sure the question types are all set to “Short Answer” or “Paragraph.”
    • 2
  • Add in two more questions for a total of three. Name them:
    • Name
    • Email
    • Message
    • 3
  • When you’re done with this part, your form should look something like this:
    • 4
  • Click on the [Responses] tab at the top of the form.
    • Make sure “Accepting response” is checked.
    • Now, look for the little green icon on the right.
    • Click this to create a spreadsheet linked to this form.
    • 5
    • Go ahead and “Create a new spreadsheet”…
    • 6
    • …and it’ll open your new spreadsheet!
    • 7
  • Now we need to open the source for this page…
    • Navigate the Chrome menu to [View] -> [Developer] -> [View Source]
    • Search for the section that looks like the screenshot below:
      • You can search for the word “timestamp” to help.
      • You’re looking for the section with a bunch of numbers mixed in with the “null” values.
    • 9
  • Mixed in with all these numbers are three sets of values that are a little different from the rest.
    • For example:
      • [null,”720796005″,0.0,0,0,0]
      • They will end with a bunch of zeroes in a row.
    • You want the long numbers in the middle of those collections.
    • 10
    • In order, they will correspond with the “Name,” “Email,” and “Message” fields.
  • Using the example code from above, replace the number values after each “entry” with your values!
    • “entry.1124515254“: name,
      “entry.658678567454“: email,
      “entry.4565663643“: message
  • Put that URL in the example code here
    • var googleFormsURL = “a url goes here“;
  • That’s it! Using our example code and the Responsive Popup Contact Form, the data submitted will be posted to the Google Sheet. This will provide a persistent database of form submissions without clogging your email!
Advertisements

One thought on “Submit HTML Form to Google Spreadsheet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s