How to Build an API With Nodejs, Expressjs and Google Sheet - Series 2
Tags: node, javascript, googlesheet, express
Published: 2020-08-08 10:33:59

# 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](https://dev.to/osumgbachiamaka/how-to-build-an-api-with-nodejs-expressjs-and-google-sheet-1-45gf) 

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

## Prerequisites
- [Postman](https://www.postman.com/downloads/)

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](https://dev.to/osumgbachiamaka/how-to-build-an-api-with-nodejs-expressjs-and-google-sheet-1-45gf) 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](https://dev.to/osumgbachiamaka/how-to-build-an-api-with-nodejs-expressjs-and-google-sheet-1-45gf) 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](https://dev-to-uploads.s3.amazonaws.com/i/oeyga6edalz1r5wbst5t.png)
#### Step 3)
Follow all steps in [Series 1](https://dev.to/osumgbachiamaka/how-to-build-an-api-with-nodejs-expressjs-and-google-sheet-1-45gf) to create and start your server. Server should be running on http://localhost:3000
#### Step 4) Follow the steps in [Series 1](https://dev.to/osumgbachiamaka/how-to-build-an-api-with-nodejs-expressjs-and-google-sheet-1-45gf) **to create routes.** 
Here we will add a get route for authentication and after delete it as we will no longer need it.
```js
    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
```js
    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](https://dev.to/osumgbachiamaka/how-to-build-an-api-with-nodejs-expressjs-and-google-sheet-1-45gf) 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](https://dev-to-uploads.s3.amazonaws.com/i/zc08o0bwtv1jmntgwjc6.png)
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 
```js
    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](https://dev.to/osumgbachiamaka/how-to-build-an-api-with-nodejs-expressjs-and-google-sheet-1-45gf) but we used `async` and `await` to enable asynchronous, promise-based behaviour. 

Google Sheet API accepts data in form of arrays. 
```js
    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](https://dev-to-uploads.s3.amazonaws.com/i/3982mjc15hr0gn6te0sh.png)
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](https://dev-to-uploads.s3.amazonaws.com/i/8z26dvbgs7nlnq0rijov.png)
Each time you save to Google Sheet, a new row is added below.
![Sheet](https://dev-to-uploads.s3.amazonaws.com/i/5ilkknn39ztt2nl43xxl.png)
## 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](https://twitter.com/KindnessOsumgba) let's connect
View full [application](https://github.com/osumgbachiamaka/googlesheetapi-nodejs) on Github. Feel free to fork and star for later consumption
### Further Reading
 [Google Sheet API Reference](https://developers.google.com/sheets/api/reference/rest)