How to build an email listings API with Googledrive + Googlesheets + Nodejs

Subscribe to my newsletter and never miss my upcoming articles

lukas-blazek-mcSDtbWXUZU-unsplash.jpg

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

VUanNnxH5.png

  • Search for Googledrive API and enable it

ihSRSK07z.png

  • Click on manage API , create create credentials and setup

FnQENfJiH.png

  • Head back to APIs and services tab and enable the google sheets API

2ArTBh5Qc.png

Thats done , let's head to the next challenge

Second challenge

  • install the google-sheets npm package here
    http://npmjs.com/package/google-spreadsheet
    
    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.

// 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:

developers.google.com/sheets/api

console.cloud.google.com

developers.google.com/drive

npmjs.com/package/google-spreadsheet

No Comments Yet