Save Web Form Data to Spreadsheets

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.

For the thumbnail

Worry not because there is a simple way to save that data directly into Spreadsheets, Google Sheets or Dropbox files.

We are going to show you how to do it using our tool API Spreadsheets.

DISCLAIMER: This does require you signing up for our tool API Spreadsheets. We have a very generous free tier that should be more than enough for small to medium websites. However, we totally understand if you skip out on this post right here because you don’t want to sign up for another service.

Step 1: Set up a contact form in an HTML file

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

As you can see super basic

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.

Full HTML Code for the Contact Form

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:

1. full_name, line 29

• This will be a text field with the full name of the person

2. email, line 33

• This will be a text field with the email of the person

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

4. message, line 42

• This will be a textarea 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 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.

1. Open up a blank spreadsheet

Totally Blank

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.

3. Save this spreadsheet with any name you want

We decided to name it ContactFormDemo

Step 3: Get your API URL from API Spreadsheets

1. Go to www.apispreadsheets.com and sign up for an account

• Click on Sign up in the Navbar

Sign Up Button on the Toolbar

• Sign up with your email and password

2. Upload the file you created in Step 2

• Click on Upload or Drop Files and select your file from Step 2

3. Copy the Create API URL for your file

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

4. [Optional] If your API is Private you will also need to copy the Access and Secret Key and save them somewhere handy

Step 4: 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 :(.

<script>
function SubForm (){
    $.ajax({
        url:'https://api.apispreadsheets.com/data/410/',
        type:'post',
        data:$("#myForm").serializeArray(),
        success: function(){
          alert("Form Data Submitted :)")
        },
        error: function(){
          alert("There was an error :(")
        }
    });
}
</script>

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

1. Save your HTML file and open it in a browser to see the contact form

It should look mostly like this depending on the browser

2. Fill out your form and click Submit

An alert window should pop up saying Form Data Submitted :)

Yayy! It worked!

If the alert window says There was an error :( then feel free to email us at info@lovespreadsheets.com and we can try to help you out.

3. View your data in your spreadsheet!!

• Go to www.apispreadsheet.com
• Log in if necessary
• Click on the Files tab

• Find your spreadsheet and click on the Download File button to download it

• Open it up and you should see the data we submitted above!!!

Some Parting Thoughts

• You can keep submitting data to this spreadsheet and it will contain all the records whenever you download it.
• If you need to manually change something in the spreadsheet, then you can change it and reupload it using the Reupload File button. This will preserve your API URL and still contain any updates you make. NOTE: whatever you reupload will be the master version so use this with caution.
• We will write another tutorial on how to do this with Google Sheets or a file on your dropbox.
• Finally, the Javascript function to submit the data is below. Let’s walk through it:

<script>
function SubForm (){
    $.ajax({
        url:'https://api.apispreadsheets.com/data/410/',
        type:'post',
        data:$("#myForm").serializeArray(),
        success: function(){
          alert("Form Data Submitted :)")
        },
        error: function(){
          alert("There was an error :(")
        }
    });
}
</script>

1. We use jQuery syntax to call ajax. That’s what the $.ajax does
2. The url is the URL we are calling the request. In this case it is a type post request.
3. The data is our data from #myForm. The serialiazeArray() method creates a JavaScript array of objects, ready to be encoded as a JSON string (don’t worry if you don’t understand this too deeply. As long as you name your inputs and spreadsheet headers properly this whole demo should work)
4. If the request was successful then the success function is called.
5. If the request had an error then the error function is called.

If you’re more of a visual learner, check out our YouTube video for this tutorial!

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!