How to Build an API With Nodejs, Expressjs and Google Sheet - Series 1
Tags: googlesheet, node, javascript, express
Published: 2020-08-04 23:11:59

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:
- [N](https://nodejs.org/en/download/)[odejs](https://nodejs.org/en/download/) 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](https://developers.google.com/sheets/api/quickstart/nodejs) 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](https://cdn.hashnode.com/res/hashnode/image/upload/v1596579252707/jL727h-jj.png)
You’ll be required to enter the name of your project as shown below:
![](https://cdn.hashnode.com/res/hashnode/image/upload/v1596336837850/yyPdCFmw7.png)

Select **web server** **** as the type of application you are integrating with and click on ****API Console**** to configure your redirect URI
![](https://cdn.hashnode.com/res/hashnode/image/upload/v1596337146059/SIJua-rUo.png)

Select credentials from your console and edit your `OAuth client`
![Screenshot 2020-08-02 at 3.14.15 AM.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1596337934485/2eqOW4-F1.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](https://cdn.hashnode.com/res/hashnode/image/upload/v1596337956437/kFJSfnN1S.png)
and finally, download your credentials
![Screenshot 2020-08-02 at 4.18.29 AM.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1596338523641/a82CrKU9o.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](https://cdn.hashnode.com/res/hashnode/image/upload/v1596336174142/oBN8C6MXj.png)
The `server.js` file contains the code to start and run our server
Add the code below to your server.js file.
```js
    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
```js
    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.
  ```js
    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.
```js
    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`
```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](https://cdn.hashnode.com/res/hashnode/image/upload/v1596437616001/atqk1RTsN.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](https://cdn.hashnode.com/res/hashnode/image/upload/v1596438734671/UKSXNzwbk.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](https://cdn.hashnode.com/res/hashnode/image/upload/v1596521045934/IUiPj1XYM.png)

Let's add a function to retrieve data from our sheet
```js
    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
```js
    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](https://cdn.hashnode.com/res/hashnode/image/upload/v1596520767648/Mtf36Wp7t.png)

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

****retrieveFromSheet.js**** should look like this
```js
    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](https://cdn.hashnode.com/res/hashnode/image/upload/v1596521778062/mYSBeTMQo.png)

Let’s go over the code base again in bits and pieces, to have a better understanding.
```js
    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](https://cdn.hashnode.com/res/hashnode/image/upload/v1596483087200/gImy3jjKs.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](https://cdn.hashnode.com/res/hashnode/image/upload/v1596520235080/r9SFu-H_N.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
```js
    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](https://twitter.com/KindnessOsumgba) let's connect