DEV Community

loading...
Cover image for Google sheets API and Node.js

Google sheets API and Node.js

siddharth
I'm a react native,web and MERN stack developer,i like to make random things using javascript and yes i'm a final year IT engineering student
・3 min read

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

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");
})
Enter fullscreen mode Exit fullscreen mode

STEP2:

Now its time to enable google sheets api. click on this Link. Sign in using your google account. you will redirect to this home page.
google console

Now click on select project on top left corner.
google console
Create new project and named it whatever you want.After creating project select the given project.

google console

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
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
only fill the service account name and click on create and continue and skip option 2 and 3 and done.

google sheets
Here we will get information of service account. Copy the service account Email. Follow the below steps to download a credentials file.
google sheets
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
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

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");
})

Enter fullscreen mode Exit fullscreen mode

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.

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");
})

Enter fullscreen mode Exit fullscreen mode

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!

Discussion (0)