This article shows how to log contact form data that a user submits through your website into a Google Spreadsheet.
While a Google Spreadsheet is not the place most people would recommend you store important data — it can be a handy way for a lean organization to avoid dealing with databases, CMS, CRMs, etc, and keep information in an interface they are familiar with. That data is also then very easy to transfer to other applications, to share, or to modify using all the formulae that spreadsheets are famous for.
The last time I experimented with storing user data from a website in a Google Spreadsheet I was using Tabletop, a library that, to my memory, was constantly having to keep up with all of the changes Google made to its API, and exploiting some clever hacks to pull off the feat.
This time around, I knew using a third party library wouldn’t be an option for my project, so I looked into the state of Google’s Sheets API (currently V4 at the time of this writing). Turns out it’s come a long way, and is very easy to implement.
We will be submitting data from the contact to a Google Spreadsheet. In order to do so securely, there are 3 things we need:
- A Blank Google Spreadsheet, and that Spreadsheet’s ID
- An API Key
- An OAUTH Client ID
Step 1: Create Your Sheet
When you create a blank spreadsheet in Google Sheet, you can get its ID from the address bar of your browser. It’s the string of random characters after the
/d and before the
Step 2: Enable the Sheets API
Enable the Sheets API Here, at console.developers.google.com .
From the next screen, search for
On the next screen, click to enable it.
Step 3: Create an API Key
Open the Credentials page in the Google Developers API Console. Click on
Create Credentials > API key
For testing, you may not want to restrict the key at this time, but in production, you may want to ensure that only the websites you choose have access to use your API Key. This will prevent someone from interfacing with your data from another website.
You could also restrict the API Key to only certain Google APIs — which is generally a good idea in order to minimize any potential security vulnerabilities.
When you are done with all of the settings, hit
save and copy down your API Key — which shows up on the right side of the page:
Step 4: Generate an OAUTH Client ID
From the Credentials page in the API Console, Click
Create Credentials > OAUTH Client ID and choose
Web Application . Give the application any name (e.g.
Enter any domains you wish to restrict the authentication to under
http://localhost:8000 , as this is the server address that always gets set up when I run a python simple HTTP server (
python -m SimpleHTTPServer from the terminal in mac). This allows your API to run locally.
After you hit
Save , copy down the client ID that gets generated.
Now you have all of the security stuff out of the way, and you can get to the fun stuff, hooking up your form to the spreadsheet.
Step 1: Create a form
Here, we’re just importing the library and making a form that collects a person’s first name. We import the library
Text components, create a simple react class, and render out the form with a submit button. The form doesn’t do anything yet.
Step 1: Store all your sheets API credentials in variables
You’ll have your own values here, but they’ll look something like this:
SCOPE will be the same.
Place those at the top of your file, before the
export default class stuff, but after the
Step 3: Hook your submit button up to a function
<Form>tag we made? Let’s hook it up to a function
And make the function above our
This would normally work for a react component, but the informed plugin doesn’t know how to tie its own API to the React Class we’re making, unless we bind its functions, like this:
Step 4: Make that submit button do something:
Ok, there’s a lot in this step, and you can learn more about the API itself in this documentation, but here’s the gist of what we want to do:
First, the sheets API gives you a bunch of options (
params). Some of them that we’re using are:
- range: We’ve just got
Sheet1here, because we don’t want to submit to multiple sheets within our spreadsheet.
Sheet1is the default name of your first sheet in any Google Spreadsheet, so as long as you haven’t changed this, you’re good!
RAW. Your options here are Raw or user entered. From the docs:
rows— this is the default, and it just means each entry should take up one row in our spreadsheet (versus 1 column).
We call the sheets API
append functionality to add a new entry to our spreadsheet (you could use
update if you were only ever updating a single entry in the spreadsheet), pass it our options (
valueRangeBody information, and then wait for a response.
You’d have to put in the behavior you want after you get a response (some sort of onscreen “success” message perhaps?), but in this example we’ll just log that success response (or error) out to the console.
Step 5: Initialize the API
To make all that code we just wrote talk to Google, we need to initialize the Sheets . We’ll do that when our React class loads (via the
componentDidMount function, which gets called automatically).
handleClientLoad function needs to signal to Google that we want to use Google authorization:
When this happens, it calls another function,
This is where we pass Google all of our API credentials so it knows we’re authorized to store data in our sheet. That
updateSignInStatus callback function within
listen doesn’t need to be there for our example, but ordinarily this would be a good time to set some React state variables so we know the user is signed in successfully.
But none of this will work unless we actually include the Google Sheets API itself!
Step 6: Include the Google Sheets API codebase
<script> tag somewhere in the HTML of our page (the bottom, before the
</body> is always a popular option:
There’s a bunch of
props here in this script tag, but basically the
crossOrigin property just makes it clear that we’re ok with the sheets API working anywhere someone can host our codebase. And the
onreadystatechange property just calls an
onload function — we don’t really need that to do anything in this example.
Step 7: Test it!
- Start up your webserver and go to your form page.
- Depending on how you shared your spreadsheet from Google, you should be asked to use Google’s login functionality. As long as you have a Google account, you’re good to go once you login successfully.
- enter some data into your form, and hit submit. and go take a look at Google Sheets. The form data should be logging straight to Google Sheets. Here’s mine: