How to save data from your website to Google Spreadsheet easily.
Let’s assume you are like me, you work for an organization who happens to want to store form data from a website to the company’s Google spreadsheet account. Also, let’s assume you have gone to google developers console to try to figure this out, but it seems weird to you. You are here to find out how to save data from your website to Google spreadsheet easily, more like using Google spreadsheet as your database. Let me tell you one truth… you have come to the right place.
LET’S GET STARTED
I’ll assume you already know what Google spreadsheet is, so I will just skip formalities and introductions. Let jump right into it.
The first thing we need to do is to enable the Google Spreadsheet API, as depicted in the image above. Without activating this API service, your code will throw a 400 error.
Next, click on Google Sheet API.
Now, we have to create credentials (The singular data all users of your website form would use). Call this your singular authorized user that all users of your form would be masked under.
I explained in step 4 how the credentials you would be creating would be the singular user that all your users would be masked under right?. Now, to put it plainly, this account would be your service account. The main account that can only access your spreadsheet document. It is this account we would connect our website form to, so each time a user clicks on submit, we call an API that allows this service account to mutate our spreadsheet.
Fill up the form to set details for the service account.
Give the service account a role. I gave mine the “owner”.
Now, in this step, you will be doing something very important. You will be creating your key. Click on “CREATE KEY”, after this a form will open up on the right side of your computer screen, leave it as JSON, and click “CREATE”. After this, a file would be downloaded to your system. Take note of the download path.
Check “Enable G Suite Domain-wide Delegation”, then click on SAVE
Now that you have done the setup, let us Create Our Google SpreadSheet.
Sign in to your Google account and create a new spreadsheet.
Create row header titles for each field in your form. In mine, I simply want to collect the “Name”, “School” and “Age” of my students.
Next, click on the “Share” button as depicted on the image
Do you remember the JSON file you downloaded when you created a key? open that JSON file and look for the key “client_email”. Copy the value.
Paste your “client_email” into the desired form input, then click on the suggested email URL.
I like to make this account the “Owner” of the spreadsheet. You may choose to give it different permission, but ensure the permission allows write-to access.
We are almost through. We have been working on the boring stuff since. Let us now get to the more interesting part – the coding part.
Take a look at the full presentation HERE before you proceed.
First, we need to install an NPM package.
within your project folder, run
npm i google-spreadsheet
Then follow the code presentation HERE to finish the setup. To ensure it works well. Fill up the spaces in the code base with data from your own spreadsheets ID, Private Key, and client email. Then click update to see the changes take place.
The codebase has been well documented for ease of use. You may study the code, copy it, and use in your own project. The code was written in react, but it uses the same principle all around JS and any frameworks.
I hope you learned something? Leave a comment below