Implementing Google Sheet APIs using Node backend with basic CRUD operations.

Authentication, Basic Implementation in Nodejs

Google APIs are application programming interfaces (APIs) developed by Google which allow communication with Google Services and their integration to other services.

Prerequisites:

Following are the prerequisites you will need.

• Google Account and create one project in the developer account.
• Nodejs and Google Library.

Firstly, we will see the Google Account settings and then Nodejs authentication-authorization & with basic implementations.

Step 1: Sign-in Google Developer Account and Create a new project and click to Enable APIS and services

Search Google Sheets API and enable it.

Step 2: Goto OAuth consent screen

Click on the “CONFIGURE CONSENT SCREEN” and go ahead for updating your application’s & user’s details.

Step 3: Fill in the Apps. details for verification, 4 steps needed.

Fill the mandatory fields in the tabs.

OAuth consent screen:
App name, User support email and Developer contact information
SAVE AND CONTINUE

Scopes: SAVE AND CONTINUE

Test users:
While publishing status is set to ‘Testing,’ only test users are able to access the app. The allowed user cap prior to app verification is 100, and is counted over the entire lifetime of the app
Add Users
SAVE AND CONTINUE

Summary: Here you will get all your details till now you saved.

Step 4: Goto Credentials and click on Create Credentials and select OAuth client ID.

A client ID is used to identify a single app to Google’s OAuth servers. If your app runs on multiple platforms, each will need its own client ID

After selecting OAuth client ID, there will be application type drop-down, select Web Application, give Name.
Add your Authorised javascript origins and Authorised redirect URIs as shown below.

Your development and production domains.

After saving/creating credentials, you will be provided with a JSON file. Click the Download icon next to your newly created OAuth2 Client Id.
The client ID, client secret keys. This plays an important role in your Nodejs application to authenticate.

Credential JSON file, client ID, client secret keys.

So, this was the overall manual work in the Google Developer Account to create a project and its credentials.

The next step starts in the Nodejs implementation to authenticate with created credentials/project in the Google Account.

Prerequisites in Nodejs:

• Package:- npm install googleapis.
• Store safely credentials JSON file from Google Account.

Let's start:

Authentication

Authentication

Authorize

Get token file after verification from Google

In the above code of authentication and authorization,
you can see, credentials.json file is stored(at any path you can), after successful verification from the Google, token.json file is generated in your Nodejs application(at any path you can set).

To link Nodejs to your Google Developer Account, one step is needed todo i.e. to generate token.json file that will be after verification of credential.json.

Token.json is always in the way to authenticate during Google spreadsheets modification. So generating token.json will require only once to authenticate.

So let's practically go for the above explanation,
When firstly you will run your APP. you will receive the URL with the help of credential.json file, so you will need to open this URL in your browser. This URL needs the Google developer account’s username-password to verify it.

After successful sign-in, your Authorised redirect URI will be called which you have set during the creation of OAuth client creation, which is shown in the above image in step 4, so this redirect API should be created in your application and should print the request body i.e. req.originalUrl, here you will get the code from the Google and after entering the code, token.json file will be generated.

So whenever you will call the API for the modification of the sheet, it will pass through this token.json file.

Workflow of generating token.json

So this was the authentication & authorization process to link Nodejs App. and Google Account.

Now, let's start with basic modification, CRUD operations of Google Sheet APIS.

• Get google sheet readings (records)
• Insert google sheet records
• Update google sheet records
• Delete google sheet records

For all the operations, you will need to save spreadsheetId, range in the configuration of your APP.

spreadsheetId :
From the URL of your spreadsheet google drive
https://docs.google.com/spreadsheets/d/<…spreadsheetId..>/gid=<…>
range:
range: ‘<sheet-name>!A2:Z’,
A- column, 2 is your sheet line from where your records start and Z is the last column or it can be A2:K, any column can be last.

Get google sheet readings (records)

You will receive data as per your requirements

Insert google sheet records

To insert single or multiple records

Update google Sheet records

To update records

Delete google sheet records

To delete records

Param <auth> is authentication of your application.

References

• Official Google Sheets API reference

I would definitely recommend checking out these references (especially the Official Google Sheets API reference) to get a more in-depth understanding and how to use it more advance with Node.js.

Hope you found this information helpful.
Thanks and happy coding.