Google sheets API and Node.js
Tags: node, googlecloud, webdev, javascript
Published: 2021-06-14 04:42:09

Hey Everyone! 
Today we are going to store and retrieve data from google sheets using Node.js. So it's fun to do. I will explain all the steps in an easy way. Let's start coding.

##STEP1:
First create new folder node-googlesheets and open terminal in that directory and write a command

>npm init

Hit the enter to given questions. Now install the packages that we need.

> npm i googleapis google-auth-library express

Now create a new file named index.js and require the packages

```javascript
const express = require('express');
const app = express();
const {GoogleAuth} = require('google-auth-library');
const {google} = require('googleapis');

app.get("/",(req,res) =>{
    res.send("hello world")
})

app.listen(3000,(req, res) =>{
    console.log("running on server");
})
```
##STEP2:
Now its time to enable google sheets api. click on this [Link](https://console.cloud.google.com/getting-started?pli=1). Sign in using your google account. you will redirect to this home page.
![google console](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/b2valpygt73q2jbwrnju.png)

Now click on select project on top left corner.
![google console](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/isl01mlk1oqkhs8vl7dm.png)
Create new project and named it whatever you want.After creating project select the given project.


![google console](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sxh2k7wxptsxj9ievhxa.gif)

Now you need to enable google sheets API. For that click on left side bar APIs & Services. Then Enable APIs and Services , search for google sheets API and enable it. all this are shown in above gif.


After that we need to create one service account. For that follow the below steps shown in figure.
![google console](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0ss9acbsazaxfwjc2yra.png)
Click on credential on left side bar. on top you will see the option create credentials click on that and click on Service account. you will redirect to below given page 

![google console](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cuqxa6tzw6h8bxv2uo86.png)
only fill the service account name and click on create and continue and skip option 2 and 3 and done.

![google sheets](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/73akd9vf2ezx0xx4964x.png)
Here we will get information of service account. Copy the service account Email. Follow the below steps to download a credentials file.
![google sheets](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/u9gmaul3o92bi7h2wjlw.gif)
Move this file to your working directory and rename it as credentials.json .
NOTE : This is important to move file in project directory and rename it credentials.js.

##STEP3:
Create google sheets file.
![google sheets](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/w2lykes3o80rg7lhkhbt.png)
Copy your service email from credentials in your API and services and share with google sheets in top right corner and give permission as an Editor and save changes. Copy your google sheet id from url and paste it in code. 

##STEP4:
Now it's time to code. open index.js and create first route
```javascript
const express = require('express');
const app = express();
const {GoogleAuth} = require('google-auth-library');
const {google} = require('googleapis');

app.get("/", async(req,res) =>{ 
    const auth = new GoogleAuth({
        keyFile: "credentials.json",
        scopes: "https://www.googleapis.com/auth/spreadsheets"
    })//this is authentication where we are providing our credential file to authenticate


    const client = await auth.getClient();
    
    const spreadsheetId = "1WbsIrcHLLeVVN6K_1RFP5n0qCh****Ub63kg3XMKIGfQ";//this is unique id of google sheet
    const range = "Sheet1"//name of sheet 
    const sheet = google.sheets({version:"v4", auth: client})
    await sheet.spreadsheets.values.get({
        spreadsheetId,
        range
    },(err, data) =>{
        res.send(data.data)
    })
})

app.listen(3000,(req, res) =>{
    console.log("running on server");
})

```

Using this code we can fetch all the rows from the sheets. Now if you want to add data in sheet then refer below code.
```javascript
const express = require('express');
const app = express();
const {GoogleAuth} = require('google-auth-library');
const {google} = require('googleapis');

app.get("/", async(req,res) =>{
    const auth = new GoogleAuth({
        keyFile: "credentials.json",
        scopes: "https://www.googleapis.com/auth/spreadsheets"
    })

    const client = await auth.getClient();
    // res.send(client)
    const spreadsheetId = "1WbsIrcHLLeVVN6K_1RFP5n0qChRliUb63kg3XMKIGfQ";
    const range = "Sheet1"
    const sheet = google.sheets({version:"v4", auth: client})
    await sheet.spreadsheets.values.get({
        spreadsheetId,
        range
    },(err, data) =>{
        console.log(data.data)
    })
//adding data in sheet
    await sheet.spreadsheets.values.append({
        spreadsheetId,
        range: "Sheet1",
        valueInputOption: "USER_ENTERED",
        resource :{
            values:[
                ['newname','newname'],
            ]
        }
    })
    res.send("Sheet updated")
})

app.listen(3000,(req, res) =>{
    console.log("running on server");
})

``` 
Yeah, all done.
This will give you idea about how to store and retrieve data from sheets.
I hope you liked the article!
Thank You!