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://npmjs.com/package/google-spreadsheet- 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.
// import the google spread sheets package
const { GoogleSpreadsheet } = require('google-spreadsheet');
class EmailListings {
async accessGoogleSheet(){
// this doc object loads up an instance of the spreadsheet by ID
// the ID is defined as a url parameter on the google spreadsheet
const doc = new GoogleSpreadsheet('7s_9aHRSBNYTGrUYvjTETH8rskNciVRdFsZb2tgC89kw');
// load directly from json file if not in secure environment
await doc.useServiceAccountAuth(require('./client_secret.json'));
await doc.loadInfo(); // loads document properties and worksheets
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 {
// select the inputed email
const email = data.Emails
// invoke the method to access the particular googlesheet
const doc = await this.accessGoogleSheet() //sheet
// update the google sheet with a title
await doc.updateProperties({ title: 'Feedback email list' });
// the spreadsheet can have many sheets, select a particular sheet by id or index
const sheet = doc.sheetsByIndex[0]; // or use doc.sheetsById[id]
// rename the sheet with a title
sheet.updateProperties({title: 'email listings'})
await sheet.setHeaderRow(['Emails']) //Set first row of the sheet
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(){
// invoke the method to access the particular googlesheet
const doc = await this.accessGoogleSheet() //sheet
// select a particular sheet by id or index
const sheet = doc.sheetsByIndex[0];
try {
// Returns a range reference from a starting row reference
const getRow = await sheet.getRows({offset:1})
// Return an array of emails
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 }));
// whitelist origins
const whitelist = ['http://localhost:3000', 'liamgadgets.com']
const corsOptionsDelegate = function (req, callback) {
let corsOptions;
if (whitelist.indexOf(req.header('Origin')) !== -1) {
corsOptions = { origin: true } // reflect (enable) the requested origin in the CORS response
} else {
corsOptions = { origin: false } // disable CORS for this request
}
callback(null, corsOptions) // callback expects two parameters: error and options
}
app.use(cors(corsOptionsDelegate))
// api routes
app.get('/', (req, res) => {
res.send('welcome!')
})
// this api route updates the spreadsheet on the drive , with new data
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
}) )
})
// this api route retreives a list of upated emaill listings from spreadsheet on the drive
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
}) )
})
// catch 404 and forward to error handler
app.use((req, res, next) => {
next(res.status(400).json({
status: false,
data: 'Bad request'
}));
});
// error handler
app.use((err, req, res) => {
// render the error page
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: