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");
})
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.
Now click on select project on top left corner.
Create new project and named it whatever you want.After creating project select the given project.
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.
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
only fill the service account name and click on create and continue and skip option 2 and 3 and done.
Here we will get information of service account. Copy the service account Email. Follow the below steps to download a credentials file.
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.
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");
})
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");
})
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!
Top comments (2)
Thank you so much how do I implement this in a react nodejs app already running nodemailer to send inputs to email
Hey, sorry for late reply. Follow the above steps and add "await sheet.spreadsheets.values.append({
spreadsheetId,
range: "Sheet1",
valueInputOption: "USER_ENTERED",
resource :{
values:[
[your values],
]
}
})
"
in your nodemailer route and pass the values you want to pass