DEV Community

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

Posted on

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

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

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
Folder Structure

Step 3)

Follow all steps in Series 1 to create and start your server. Server should be running on http://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.
Working
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
Postman
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.
Postman
Each time you save to Google Sheet, a new row is added below.
Sheet

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

Discussion (4)

Collapse
kshitiz20 profile image
Kshitiz Sharma

Hey. Great article.
I could follow properly the process of getting the users.
but while posting, it says "The caller does not have permission" Can you help me with that?

Collapse
osumgbachiamaka profile image
Osumgba Chiamaka Author

Thanks for reading.

Please confirm that in your sheet permission, you activated read and write.

Collapse
arung86 profile image
Arun Kumar G

Looks interesting to use spreadsheets as a way to store than db, what use cases did u use spreadsheet over db ?

Collapse
osumgbachiamaka profile image
Osumgba Chiamaka Author

As mentioned in the first series, this article is not to deliberate the usefulness of sheet over an actual db or vice versa. But i'll highlight a few use cases.

use case 0) a small startup using sheet because that is what works for them because of the market they are serving, etc.
use case 1) you have a google form where people register for an event and you need to pull this data into your application or probably save to an actual db.
use case 2) your non-technical manager demands you update the sheet with data when a user registers for an event. why not automate this process.