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

Welcome back to the second series of our article on building an API with Nodejs, Expressjs and Google Sheet. In the previous article we initialized our project, authorised our application to use the Google Sheet API, created our route and controllers, and read data directly from Google Sheet into our application.

If you’ve not read the previous article, do well to check it out here - How to Build an API With Nodejs, Expressjs and Google Sheet - Series 1

In this article, I’ll be working you through the process of writing to Google Sheet from a Nodejs application.

Prerequisites

• Postman

While continuing this article because we want everyone to catchup with what we have already done, we would consider 2 types of applications

• I want to write to and read from Google Sheet

• I have no business retrieving from Google Sheet, I only want to write to Google Sheet

  I want to write to and read from Google Sheet

  Follow all the steps in Series 1 and the follow along as we create our post route/controller

  I have no business retrieving from Google Sheet, I only want to write to Google Sheet

  If your application only writes to Google Sheet, follow the steps below

  Step 1)

  In Series 1 follow all the steps under Enabling Google Sheet API to authorise your application to use the Google Sheet API only change your redirect URI to http://localhost:3000/v1/update/users

  Step 2)

  Our folder structure will look like this

  Step 3)

  Follow all steps in Series 1 to create and start your server. Server should be running on localhost:3000

  Step 4) Follow the steps in Series 1 to create routes.

  Here we will add a get route for authentication and after delete it as we will no longer need it.

    const express = require('express')
    const router = express.Router()
    const { saveUsers } = require('../controllers/saveToSheet')
  
    router.get('/v1/update/users', saveUsers)
    router.post('/v1/update/users', saveUsers)
  
    module.exports = router
  

  Step 4) Create your controller

  Authenticate application

  Add the code below to your saveToSheet.js file

    const authentication = require('../google/index')
      const { google } = require('googleapis')
  
      exports.saveUsers = (req, res) => {
      authentication.authenticated()
        .then((auth) => {
          res.status(200)
          .json('Working')
        })
        .catch(err => {
          res.status(401)
          .json(`you know wetin happen, ${err}`)
        })
    }
  

  Go back to Series 1 and complete the steps needed to authenticate and download your token.json file. If your browser shows Working, hurray we are have successfully authorised our application to use Google Sheet API.

Delete router.get('/v1/update/users', saveUsers) we do not need it again.

Create our POST controller

Let’s create a function to save our data to Google Sheet in saveToSheet.js. saveToSheet.js should look like this

    const authentication = require('../google/index')
    const { google } = require('googleapis')
    const sheets = google.sheets('v4')
    const saveToSheet = async (auth, data) => {
        const request = {
          spreadsheetId: '1_c4TS8WO0VqX336OauvYaVlxRzuEkaZ50hJf6yQxZok',
          range: 'new users!A1:B',
          valueInputOption: 'USER_ENTERED',
          insertDataOption: 'INSERT_ROWS',
          resource: {
            values: data // data to save to sheet
          },
          auth: auth
        }
        try {
          const response = (await sheets.spreadsheets.values.append(request)).data
          // response return an object with the spreadsheetId, tableRange and all columns updated
          // you can use this response in your application however you so desire
          return 'Success - Google Sheet Updated'
        } catch (err) {
          return `${err}` //returns only the error object
        }
    }

    exports.saveUsers = (req, res) => {
      const data = [['amaka', 'obinna'], ['uche']] // data to save to Google Sheet
        authentication.authenticated()
        .then((auth) => {
            saveToSheet(auth, data)
            .then(resp => {
                res.status(200).
                json({
                message: resp
                })
            })
            .catch(err=> {
                res.status(404).
                json({
                error: `i no gree save data to sheet, ${err}`
                })
            })
        })
        .catch(err => {
            res.status(401)
            .json(`you know wetin happen, ${err}`)
        })
    }

In the code snippet above, notice we didn’t create a new promise as did in Series 1 but we used async and await to enable asynchronous, promise-based behaviour.

Google Sheet API accepts data in form of arrays.

    const data = [['amaka', '2 lokoja'], ['uche']]

This data we are saving to Google Sheet can be retrieved from the UI, but here we created it ourselves. Open Postman, let test our post route The image above shows what the response from Google Sheet API looks like after successfully updating the Sheet. You can use this data however you so desire in your application.

You can also enter the data to save to Sheet directly here in Postman, as shown in the image below. In your application, use req to retrieve data entered in Postman and then you’ll destructure to retrieve the data to save to Google Sheet. Each time you save to Google Sheet, a new row is added below.

Congratulations

We have completed our REST API built with Nodejs, Expressjs and Google Sheet API, to read from Sheet and write to Sheet. Thanks and see you next time.

Follow me on twitter let's connect

View full application on Github. Feel free to fork and star for later consumption

Further Reading

Google Sheet API Reference