DEV Community

Cover image for How to Build an API With Nodejs, Expressjs and Google Sheet - Series 1
Osumgba Chiamaka
Osumgba Chiamaka

Posted on • Updated on

How to Build an API With Nodejs, Expressjs and Google Sheet - Series 1

Got a bunch of work done with spreadsheets and you are wondering how to integrate that data into your Nodejs application without moving it to a relational or NoSql database?

That’s possible and you’ll learn how to get it done in this article.

Google sheet remains an important tool for small businesses today. I know a couple of startups that started all from a Google Sheet until they found a need to scale before they created an app and migrated to a real database.

Though there has been a lot of reasons given as to why an actual backend database should be used instead of Google Sheet, reasons ranging from accidentally deleting data or sheet, lack of static identifiers on records, etc.

However, this article is not to deliberate the advantages of sheet over an actual backend database or vice versa, some projects require you to read data from sheet and sync into your application/database or write to sheet for managerial use.

Most small projects might not necessarily require a regular database and can be managed with Google Sheet.

In this article, I will be walking you through the process of reading from Google Sheet using Nodejs.

Prerequisites

For this tutorial we are going to create a rest API that reads and writes to Google Sheet.
Before you begin this tutorial you'll need the following:

  • Nodejs and NPM installed
  • A web browser
  • A google account to enable authentication

Let’s get started…

Enabling Google Sheet API

Google sheet API is google's API that enable developers programatically read, write and format google sheet from their applications.

Step 1). Download your credentials.json
Follow this link and click on the “Enable the Google Sheets API” button to download your credentials.json file
s_9190355683C153BCB18A14D16920F69B43895F255B1A0932B5FF3AAA80959B28_1596526218353_Screenshot+2020-08-04+at+8.29.02+AM.png
You’ll be required to enter the name of your project as shown below:

Select web server **** as the type of application you are integrating with and click on *API Console* to configure your redirect URI

Select credentials from your console and edit your OAuth client
Screenshot 2020-08-02 at 3.14.15 AM.png

Authorise redirect URI and save. (This URI is where you will be redirected to after authenticating with Google. This should be an absolute path)
Screenshot 2020-08-02 at 3.23.14 AM.png
and finally, download your credentials
Screenshot 2020-08-02 at 4.18.29 AM.png
Note: This tutorial will assume the name of your downloaded credential as *credentials.json*, you can rename yours to whatever name you desire to use and make sure you replace credentials.json file to your desired file name as used in the article.

Step2). Install the Required NodeJS Packages

npm install googleapis@39 express http --save
npm install nodemon --save-dev

Creating our API

Folder structure

Our folder structure will look like this
Screenshot 2020-08-02 at 3.42.11 AM.png
The server.js file contains the code to start and run our server
Add the code below to your server.js file.

    const PORT = process.env.PORT || 3000;
    const http = require('http');
    const app = require('./routes/app')
    const server = http.createServer(app);
    server.listen(PORT)

Inside your routes folder, create app.js and add the code below

    const express = require('express');
    const indexRoute = require('../routes/index')
    const app = express();
    app.use((req, res, next) => {
      res.setHeader('Access-Control-Allow-Origin', '*');
      res.setHeader('Access-Control-Allow-Headers', 'Origin, X-Requested-With, Content, Accept, Content-Type, Authorization');
      res.setHeader('Access-Control-Allow-Methods', 'GET, POST, PUT, DELETE, PATCH, OPTIONS');
      next();
    });
    app.use('/', indexRoute);
    module.exports = app;

Start your server by typing nodemon in your terminal, this is same as typing node server.js. The difference is you’ll have to always stop and restart your server whenever you make changes to your code if you use node server.js. But with Nodemon, it automatically restarts your application when a file changes in the directory. Nodemon knows to start server.js because it was specified in my package.json file "main": "server.js". Your server should be running on http://localhost:3000

Copy *credentials.json* into the google folder.
Below is the code that creates a class module to authenticate and authorise our application to use the sheet api.

    const fs = require('fs')
    const readline = require('readline')
    const {google} = require('googleapis')
    // If modifying these scopes, delete token.json.
    // SCOPE gives additional rules to the sheet, you can restrict rule to readonly or give full access
    const SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
    // The file token.json stores the user's access and refresh tokens, and is
    // created automatically when the authorization flow completes for the first
    // time.
    // The path were your token.json file is saved, depends totally on you.
    const TOKEN_PATH = './google/token.json'
    class Authentication {
      authenticated(){
        return new Promise((success, failed) => {
          // Load client secrets from a local file.
            let credentials = this.getClientSecret()
            let authorized = this.authorize(credentials)
            authorized.then(success, failed)
          })

      }
      getClientSecret(){
        return require('./credentials.json')
      }

      /**
       * Create an OAuth2 client with the given credentials, and then execute the
       * given callback function.
       * @param {Object} credentials The authorization client credentials.
       * @param {function} callback The callback to call with the authorized client.
       */
      authorize(credentials) {
        const {client_secret, client_id, redirect_uris} = credentials.web
        const oAuth2Client = new google.auth.OAuth2(
            client_id, client_secret, redirect_uris[0])

            return new Promise((success, failed) => {
              // Check if we have previously stored a token.
              fs.readFile(TOKEN_PATH, (err, token) => {
                if (err) {
                  this.getNewToken(oAuth2Client)
                  .then((oAuth2ClientNew) => {
                    success(oAuth2ClientNew)
                  }, (err) => {
                    failed(err)
                  })
                } else {
                  oAuth2Client.setCredentials(JSON.parse(token))
                  success(oAuth2Client)
                }    
              })
            })
        }

      /**
       * Get and store new token after prompting for user authorization, and then
       * execute the given callback with the authorized OAuth2 client.
       * @param {google.auth.OAuth2} oAuth2Client The OAuth2 client to get token for.
       * @param {getEventsCallback} callback The callback for the authorized client.
       */
      getNewToken(oAuth2Client, callback) {
        return new Promise((success, failed) => {
          const authUrl = oAuth2Client.generateAuthUrl({
            access_type: 'offline',
            scope: SCOPES,
          })
          console.log('Authorize this app by visiting this url:', authUrl)
          const rl = readline.createInterface({
            input: process.stdin,
            output: process.stdout,
          })
          rl.question('Enter the code from that page here: ', (code) => {
            rl.close()
            oAuth2Client.getToken(code, (err, token) => {
              if (err) {
                failed('Error while trying to retrieve access token', err)
              } 
              oAuth2Client.setCredentials(token)
              // Save the token for later program executions
              fs.writeFile(TOKEN_PATH, JSON.stringify(token), (err) => {
                if (err) return console.error(err)
                console.log('Token stored to', TOKEN_PATH)
              })
              success(oAuth2Client)
            })
          })
        })
      }
    }

    module.exports = new Authentication

Create index.js file inside the google folder and paste the code above(its commented to explain the codebase.

Create our routes

Inside our routes folder, create index.js file and add the code below. This should contain all the routes for this application.

    const express = require('express')
    const router = express.Router()
    const { retrieveUsers } = require('../controllers/retrieveFromSheet')
    const { saveUsers } = require('../controllers/saveToSheet')

    router.get('/v1/users', retrieveUsers)
    router.post('/v1/update/users', saveUsers)

    module.exports = router

Create our controllers

Inside our controllers folder, create *retrieveFromSheet.js* and saveToSheet.js

Authenticate application

Let's give our application access to Sheet and afterwards download token.json.

Add this code to retrieveFromSheet.js

    const authentication = require('../google/index')
    const { google } = require('googleapis')

    exports.retrieveUsers = (req, res) => {
    authentication.authenticated()
      .then((auth) => {
        res.status(200)
        .json('Working')
      })
      .catch(err => {
        res.status(401)
        .json(`you know wetin happen, ${err}`)
      })
    }

open *localhost:3000/v1/users* on your browser, then go back to your terminal
Screenshot 2020-08-02 at 1.57.37 PM.png
Open the url in your terminal. Look at the link critically, you'll see that it appends the redirect_uri you configured in your google developer console.

Give all needed access to the application and continue, it should redirect you to your redirect_uri with a code appended to the path.
Screenshot 2020-08-03 at 8.07.15 AM.png
copy and paste the code into your terminal and hit enter. Open the google folder you should see *token.json, navigate back to *http://localhost:3000/v1/users** and if all goes well you should see
Screenshot 2020-08-03 at 7.19.57 AM.png

Let's add a function to retrieve data from our sheet

    const getFromSheet =  (auth) => {
      return new Promise((success, failed) => {
        const sheets = google.sheets({version: 'v4', auth})
        sheets.spreadsheets.values.get({
          spreadsheetId: '1_c4TS8WO0VqX336OauvYaVlxRzuEkaZ50hJf6yQxZok',
          range: 'approved!A:D',
        }, (err, res) => {
          if (err){
            return failed(err) 
          }
          const rows = res.data.values
          success(rows)
        })
      })
    }

Call getFromSheet inside retrieveUsers

    getFromSheet(auth).then((response) => {
      res.status(200).
      json(response)
    })
    .catch(err => {
      console.log(`i no gree fetch data from sheet, ${err}`)
    })

res returns data from the sheet including status code, headers, url, method type, etc but our concern is in res.data.values.

Refresh *localhost:3000/v1/users* and our retrieved users should be an array of arrays.

Screenshot 2020-08-03 at 7.47.30 PM.png

Now let's format our retrieved users to look more pretty.

*retrieveFromSheet.js* should look like this

    const authentication = require('../google/index')
    const { google } = require('googleapis')
    const getFromSheet =  (auth) => {
      return new Promise((success, failed) => {
        const sheets = google.sheets({version: 'v4', auth})
        sheets.spreadsheets.values.get({
          spreadsheetId: '1_c4TS8WO0VqX336OauvYaVlxRzuEkaZ50hJf6yQxZok',
          range: 'approved!A:D',
        }, (err, res) => {
          if (err){
            return failed(err) 
          }
          const rows = res.data.values

          // format retrieved data
          if (rows.length) {
              var rowHead = rows.shift()
              const formatedUsers = rows.map((row) => {
                  return rowHead.reduce( (obj, key, i) => {
                    obj[key] = row[i]
                    return obj
                  }, {})
              })
              success(formatedUsers)   
          } else {
            failed('No data found.')
          }
        })
      })
    }
    exports.retrieveUsers = (req, res) => {
      authentication.authenticated()
      .then((auth) => {
        getFromSheet(auth).then((response) => {
          res.status(200)
          .json({
            message: response
          })
        })
        .catch(err => {
          res.status(404)
          .json({
            error: `i no gree fetch data from sheet, ${err}`
          })
        })
      })
      .catch(err => {
        res.status(401)
        .json({
          error: `you know wetin happen, ${err}`
        })
      })
    }

users should look like this
Screenshot 2020-08-04 at 7.14.26 AM.png

Let’s go over the code base again in bits and pieces, to have a better understanding.

    sheets.spreadsheets.values.get({
          spreadsheetId: '1_c4TS8WO0VqX336OauvYaVlxRzuEkaZ50hJf6yQxZok',
          range: 'approved!A:D',
        }

Range

A range can be a single cell in a sheet or a group of adjacent cells in a sheet. For e.g in our code above we specified a range - approved!A:D. *approved* is pointing to the name of the sheet as shown in the image below.

The entire sheet is called a workbook

Screenshot 2020-08-03 at 8.30.21 PM.png

!A:D means starting from column A to column D.
!B2:D3 means start from column B row 2 to column D row 3.

values.get is used to retrieve values from a single range as shown in the example above.
values.batchGet is used to retrieve from multiple ranges at same time.

To use batchGet, change your range to ranges: ['approved!B2:D3', 'approved!A1:C2'], it takes an array of different ranges. using batchGet, returned data is destructed from res.data.valueRanges.

spreadsheetId: '1_c4TS8WO0VqX336OauvYaVlxRzuEkaZ50hJf6yQxZok', spreadsheetId refers to the id of your workbook
Screenshot 2020-08-04 at 6.10.32 AM.png

Promises

Promises simply refers to the completion (or failure) of an asynchronous operation. A promise can only succeed or fail once.

Here we created a promise to return either a success or failed object

    const getFromSheet =  (auth) => {
      return new Promise((success, failed) => {

Conclusion

The data we just retrieved from sheet can be served to our front end, used for analysis in our application or even saved to a real backend database if any. In my next article we would take a look at how to save to Google Sheet and more.

Follow me on twitter let's connect

Discussion (2)

Collapse
khenriksson profile image
Kasper Henriksson

Superb example of how to get routes working with node and OAuth! Thanks a lot!

Collapse
javierfuentesm profile image
Javier Fuentes Mora

Nice , how would you do an update where , of a select where name = 'something' ? i have been looking how to do this and i cant find anything
Thank youu