This will be a simple tutorial of how i integrated google-sheets in NodeJs for an email listings API.
This is also a unique use case, peculiar to my needs while working on a small Nodejs project. But this methodology can be applied for broader use cases and of-course modified.
First challenge
Google sheets package has great documentation; but there wasn't enough resources to learn how to generate your unique API key for Authorization, to access the google drive resources programmatically.
Let's start off:
- Login to google console platform create a new project
- Navigate to the dashboard and click on Enable APIs and services
- Search for Googledrive API and enable it
- Click on manage API , create create credentials and setup
- Head back to APIs and services tab and enable the google sheets API
Thats done , let's head to the next challenge
Second challenge
- install the google-sheets npm package here
http:
i want to make my code modular and employ the separation of concerns(SOC) concept,
so my EmailListings class has three methods; namely
- accessGoogleSheet
- addToSheet(data)
- retreiveList()
class EmailListings {
}
module.exports = EmailListings
this can can instantiated and the methods called from node server.js file
Let's load up all the configurations
This method loads up the spreadsheet unique identifier and the secret credential.json file we generated from google console.
const { GoogleSpreadsheet } = require('google-spreadsheet');
class EmailListings {
async accessGoogleSheet(){
const doc = new GoogleSpreadsheet('7s_9aHRSBNYTGrUYvjTETH8rskNciVRdFsZb2tgC89kw');
await doc.useServiceAccountAuth(require('./client_secret.json'));
await doc.loadInfo();
return doc
}
Select and update data to the spreadsheet
For this tutorial we will statically define our sheet ID for the spreadsheet. This methods takes in the data sent from the api and adds it to the sheet.
async addToSheet(data){
try {
const email = data.Emails
const doc = await this.accessGoogleSheet()
await doc.updateProperties({ title: 'Feedback email list' });
const sheet = doc.sheetsByIndex[0];
sheet.updateProperties({title: 'email listings'})
await sheet.setHeaderRow(['Emails'])
await sheet.addRow({Emails: email})
return 'success!'
} catch (error) {
console.log(error)
return error
}
}
Select and retrieve data from the spreadsheet
This method essentially retrieves an update list of emails added to the spreadsheet.
the spreadsheet can also be viewed across the integrated google drive account.
async retreiveList(){
const doc = await this.accessGoogleSheet()
const sheet = doc.sheetsByIndex[0];
try {
const getRow = await sheet.getRows({offset:1})
const emailList = getRow.map((row) => {
return row.Emails
})
return emailList;
} catch (error) {
return error
}
}
}
that's all for that email listings methods :).
Third challenge
We need a simple node server to make network requests to google-drive in this context,
the goal here is not to spin up a major Nodes production server but one that proves a concept.
So let's write one.
const express = require('express');
const app = express();
const cors = require('cors')
const Emailmodels = require('./email_listings');
app.use(express.json());
app.use(express.urlencoded({ extended: false }));
const whitelist = ['http://localhost:3000', 'liamgadgets.com']
const corsOptionsDelegate = function (req, callback) {
let corsOptions;
if (whitelist.indexOf(req.header('Origin')) !== -1) {
corsOptions = { origin: true }
} else {
corsOptions = { origin: false }
}
callback(null, corsOptions)
}
app.use(cors(corsOptionsDelegate))
app.get('/', (req, res) => {
res.send('welcome!')
})
app.post('/api/v1/update', (req, res) => {
let emailmodel = new Emailmodels()
emailmodel.addToSheet(req.body).then(()=>{
res.status(201).json({
status: true,
message: 'email listing updated'
})
}).catch(
(err) =>res.status(400).json({
status:false
}) )
})
app.get('/api/v1/retrieve', (req, res) => {
let emailmodel = new Emailmodels()
emailmodel.retreiveList().then((emails)=>{
res.status(200).json({
status: true,
message:emails
})
}).catch(
(err) =>res.status(400).json({
status:false
}) )
})
app.use((req, res, next) => {
next(res.status(400).json({
status: false,
data: 'Bad request'
}));
});
app.use((err, req, res) => {
res.status(err.status || 500).json({
status: false,
data: 'internal server error'
});
});
const port = process.env.PORT || 3000
app.listen( port, () =>
console.log(` listening at http://localhost:${port}`)
)
We learnt a great deal about using one of google drive resources, i.e google spreadsheets.
The google drive API provides alot of resources at our disposal, we could automate a ton of stuff and improve our productivity . Check them out here!
Reach out and connect with me on Twitter, LinkedIn and Github .
Resources:
https://developers.google.com/sheets/api
https://console.cloud.google.com/
https://developers.google.com/drive
https://www.npmjs.com/package/google-spreadsheet
