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
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.
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
Top comments (4)
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?
Thanks for reading.
Please confirm that in your sheet permission, you activated read and write.
Looks interesting to use spreadsheets as a way to store than db, what use cases did u use spreadsheet over db ?
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.