Save Web/HTML Form Data to Google Sheets
DISCLAIMER: This article uses our product API Spreadsheets, which our users say is the easiest way to save web/html form data to Google Sheets. It has a generous free tier.
If you are like us and often create websites where nothing needs a server and a database except a few forms, then you must also get frustrated with all the extra work you have to do to simply save data from a few fields.
Step 1: Set up a contact form in an HTML file or through Google Form
Okay, if you don’t know any HTML, then this might be a steep learning curve. The example is small so you should Google terms you don’t understand and it might make sense.
Let’s create a form in HTML. It’s a basic contact form and doesn’t contain any styling.
The form will look like this
The full code for the Contact Form and how to handle the submission is below.
Open up a blank file in the text editor you use for programming, copy and paste the code below and save the file with a [.html] extension. We saved ours as ContactForm.html
We will walk through the HTML part now and the Javascript part later.
The contact form has 4 fields.
Pay attention to the name attribute of the <input> tags. These will be the column headers of our spreadsheet we save the data in and they MUST match up.
We are going to name the four input tags as follows. The lines refer to the line of code:
- full_name, line 29 This will be a text field with the full name of the person
- email, line 33 This will be a text field with the email of the person
- age, lines 37–38 This will be a radio selection field with two age options. 18–35 and 35+. We will denote them in the value attribute of the radio fields
- message, line 42 This will be a text area that will contain the message the person types
All four <input> tags will be inside a <form> tag (line 26) that we will give an id attribute of myForm. Do not give it any other id as the data submission to spreadsheet function that we will write later in the Javascript is dependent on it.
Finally, our main component will be a <button> (line 47) that will be submitting the data. In its onclick attribute we are going to specify the function SubForm().
Again, do not give the function another name as we will be writing this exact function later in Javascript to handle submitting the data.
Step 2: Prepare your spreadsheet that the data will be saved to
This part is straightforward
- Open up a blank Googlesheet
2. Write column headers equal to the name(s) of the <input> tags
Each time someone submits the data, it will save in the appropriate column. This step is super important so make sure these headers are EXACTLY the same as the names of the input tags above.
Step 3: Get your API URL from API Spreadsheets
1. Sign up for an Account
- Click on Sign Up in the Navbar
- Sign up with your email and password
2. Upload the file you created in Step 2 by clicking on “Google Sheets”
3. Copy the API URL for your file under the Create Tab
Copy the API URL for your file and save it somewhere handy. We are going to be using this in the Javascript below to submit the data.
If the private button is on, then the link will be super long and contain the access and secret key for this API. Our private button is off below so the link is short. It’s up to which you want to use.If you want to make it private, you will need to buy an account and the code would look like the following.
Step 3: Configure the Javascript to submit data from the form
We are going to use AJAX to submit the form. Again, if you are not familiar with Javascript, jQuery or AJAX, it might be a steep learning curve.
We are going to include the full HTML code here again. The Javascript part is within the <head> tags (lines 5–23).
1. Add jQuery from a CDN (lines 5–8)
We need to ensure the FULL jQuery library is included in our HTML to use AJAX.
<script src=”https://code.jquery.com/jquery-3.4.1.js" integrity=”sha256-WpOohJOqMqqyKL9FccASB9O0KwACQJpFTUBLTYOVvVU=” crossorigin=”anonymous”>
</script>
2. Write the SubForm() function between two script tags (lines 9–23)
The SubForm() function is below. You will replace the URL below with the URL you obtained in Step 3. The rest of the function will remain the same.
We will go through this function in more detail at the end of this post but for now all you need to know is if the data was successfully saved an alert will pop up saying Form Data Submitted :) otherwise an alert will pop up saying There was an error :(.
That’s it! We have done everything needed to save data from our web form to our spreadsheet.
Now let’s test it out and see how to view our data.
Testing
- Save your HTML file and open it in a browser to see the contact form
2. Fill out your form and click Submit
An alert window should pop up saying Form Data Submitted :)
If the alert window says There was an error :( then feel free to email us at info@apispreadsheets.com and we can try to help you out.
3. View your data in your spreadsheet!!
Go check your google spreadsheet and you should see the data we just submitted!
There you have it! The nice thing about using Google Sheets is that your data will automatically keep adding onto it and you can view it anytime.
If you’re more of a visual learner, check out our YouTube tutorial for this!
If you want a custom app, data cleaned, or a project built using spreadsheets, you can check out our consulting service here: https://www.lovespreadsheets.com!
