How to Submit an HTML Form to Google Sheets…without Google Forms
Google Forms is a great service. Easy, good-looking forms with results stored in Google Sheets. But what if you don’t want your form to look like a Google Form? How can you build your own HTML form and record the responses in Google Sheets?
The answer is surprisingly tricky. One would think that Google Forms would have an API to receive AJAX requests but, alas, that is not that case. So we’ll cut out the Google Forms middleman and submit our AJAX requests directly to Google Sheets.
Setting up the Google Sheet
Since we’re skipping Google Forms, head straight to Google Sheets and create a new spreadsheet. The only thing we’ll need to do to the spreadsheet itself is assign column names for each form field we’ll be collecting.
Place the names of each form field in row 1. The order isn’t important, but the name of the cell in the spreadsheet must match the name of the form input. It’s case-sensitive, so choose your names carefully!
Configuring the Google Script
Now that we’ve set up our Google Sheet with our form fields, it’s time to write the script that will allow us to send our form data to it. From the spreadsheet we just created, go to the “Tools” menu and select “Script editor” from the dropdown.
This will open a new Google Script that will look something like this:
Delete that creatively-named function and copy/paste the following gist:
The script above will configure your Google Sheet to handle a GET request (
function doGet()), tells the spreadsheet where to place the received data (lines 21–38), and sets the allowed MIME type (
setMimeType(ContentService.MimeType.JSON)). Give it a read—Will Patera did a great job explaining the script with his comments so I left them in there. (The original gist can be found here.)
Save the script and give it an appropriate name. Then go to the “Run” menu and select “setup.”
You might be asked to give Google Scripts to use your Google account.
Once you’ve given your authorization, go to the “Publish” menu and select “Deploy as web app.”
You will then be presented with a few options with which to customize your script.
The last two of these three options are extremely important to set correctly or you won’t be able to access your script with an AJAX request. You must execute the app as yourself and you must give “Anyone, even anonymous” access to the app. Without these settings your script will reject any request from a different server, like your form’s AJAX request, because it won’t be configured to allow for cross-origin resource sharing (CORS).
Once you’ve configured these options, go ahead and click “Deploy.”
You will the be presented with the URL for your web app. This is where we’ll be sending our AJAX request so copy that URL and save it for later.
Building the HTML Form
So we’ve got a Google Sheet set up with a column for each of our form fields, as well as a script that will accept form data in an AJAX request and write it to the spreadsheet. All that’s left is to create an HTML form so that we can collect some information and persist it in our Google Sheet!
There’s no need to do anything fancy with your form—just make sure that each input’s name matches the name you used as column names in your spreadsheet. Your form might look something like this if you were afraid of styling:
Again, the key is to make sure the name of each input matches the name of each column in your Google Sheet.
The only interesting part is the
data that we’re sending. Our script is expecting a JSON data-type so we’ll have to convert our form data into a JSON object. I chose to do this with the jQuery Serialize Object script, which provides the
Now, when someone submits your HTML form, their responses should be recorded in your Google Sheet. Sweet!
A Google Form-less Google Form
Well, it took a bit more work than should have have been necessary, but we did it! By using Google Sheets in conjunction with Google Scripts, we were able to build our own Google Form…without being forced to use a Google Form. Why there isn’t a Google API to do this is beyond me…but at least it’s possible!
Update! If You’re Having Issues…
- CORS Issues
If you’re seeing an error like the following: “No ‘Access-Control-Allow-Origin’ header is present on the requested resource,” double-check you’re making a GET request and not a POST request.
- Web App URL
The format of the URL is not the same as in David’s original example if you have multiple Google accounts. Specifically, Google’s web app URL looks like this:
You must remove the “/u/1” to hit the endpoint, resulting in the following:
- Accessing Parameters
If you’re struggling to access parameters or properly pass data, try attaching them to the end of the url instead:
If you go this route, I recommend using encodeURIComponent to escape special characters (eg: the “+” in firstname.lastname@example.org).
- Request Libraries
I used the Fetch API to make the request because React supposedly ships with a polyfill by default. If you aren’t using React, I recommend the popular package request or finding a polyfill so you can offer cross-browser support.