Node.js — Get data from your Google Spreadsheet for your application

Hi there! welcome to my blog, this is Alan.

Today, I’m going to share with you how to get data from your google spreadsheet for your Backend application.

With this approach, we can control permission of which google spreadsheet that our Backend can read

Alright, let’s get started!

I assume you already have the google cloud account and created a project.

Google Service Account

The first step is enabling Google Sheet API, go to the link below, click ENABLE APIS AND SERVICES, and search for spreadsheet to enable it.

https://console.cloud.google.com/apis/dashboard

Press enter or click to view image in full size

Next, we need to create a Service Account which is a kind of account that is used in our application to access google api / google cloud stuff.

Imagine, you person need an account to access google sheet, google docs, etc… So your App, like you but it’s an app, needs an account to access those things. That is Service Account for.

Go to this link and create a Service Account:

https://console.cloud.google.com/iam-admin/serviceaccounts?referrer=search&project=mybudget-429120

Press enter or click to view image in full size

Then generate a Key for it, select .json file:

That’s all for google service account setup. We now go to the coding:

Coding

Let’s initialize a project first. I use nestjs which is a great one, you can use any other as your need.

Press enter or click to view image in full size

Rename the service account json file we had in previous step to service-account.json and put it at the root dir of the project:

Press enter or click to view image in full size

Install the googleapis and google-auth-library

1yarn add google-auth-library googleapis

Establish connection to google apis by using service-account.json for authentication.

Press enter or click to view image in full size

Define a getData function to fetch data

Press enter or click to view image in full size

Let’s create a test sheet:

Press enter or click to view image in full size

And call getData in controller:

Press enter or click to view image in full size

Open localhost:3000/sheet to check the result:

Press enter or click to view image in full size

Oh, error! Because we didn’t share the Test Sheet with the Service Account yet (it’s like your friend doesn’t share SpreadSheet with you, but you try to open it and get error)

Let’s share it with the Service Account. Open the service-account.json file and you will find the “client_email”.

Press enter or click to view image in full size

Open the Test Sheet and add that email to Share list:

Press enter or click to view image in full size

And here we are:

Press enter or click to view image in full size

That is just a simple read api and there are more apis available for google spreadsheet here that you can explore for your need.

Here is the sample source code for your reference:

https://github.com/alanng2050/blog-read-write-google-spreadsheet

Enjoy the coding!