Google Tutorial HOW TO JAVASCRIPT

How to save data from your website to Google Spreadsheet easily

how to save data from your website to google spreadsheet easily
Written by encodedBicoding

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

This tutorial focuses on software engineers equipped with NodeJS/Javascript skills and also understand how to use NPM.

Don’t know Javascript? 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.

step 1: how to save data from website to google spreadsheet

STEP 1

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.

STEP 2

Next, click on Google Sheet API.

step 2: how to save data from website to google spreadsheet

STEP 3

step 3: how to save data from website to google spreadsheet easily

STEP 4

step 4: how to save data from website to google spreadsheet easily

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.

STEP 5

step 5: how to save data from website to google spreadsheet easily

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.

STEP 6

step 6: how to save data from website to google spreadsheet easily

Fill up the form to set details for the service account.

STEP 7

step 7: how to save data from website to google spreadsheet easily

Give the service account a role. I gave mine the “owner”.

STEP 8

step 8: how to save data from website to google spreadsheet easily

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.

STEP 9

step 9: how to save data from website to google spreadsheet easily

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.

STEP 10

step 10: how to store data from website to google 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.

STEP 11

step 11: how to save data from website to google spreadsheet

Next, click on the “Share” button as depicted on the image

STEP 12

step 12: how to save data from website to google spreadsheet

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.

STEP 13

step 13: how to save data from website to google spreadsheet

Paste your “client_email” into the desired form input, then click on the suggested email URL.

STEP 14

step 14: how to save data from website to google spreadsheet

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

About the author

encodedBicoding

Software Engineer || Tech Enthusiast.
Ex @andela
Github: https://github.com/encodedBicoding
Geek Name: EncodedBicoding
Real Name: Dominic Isioma Olije

Leave a Comment