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:
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 and click on the “Enable the Google Sheets API” button to download your credentials.json file
You’ll be required to enter the name of your project as shown below:
Select web server as the type of application you are integrating with and click on API Console** to configure your redirect URI
Select credentials from your console and edit your OAuth client
Authorise redirect URI and save. (This URI is where you will be redirected to after authenticating with Google. This should be an absolute path)
and finally, download your credentials
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
The server.js file contains the code to start and run our server
Add the code below to your server.js file.
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
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.
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.
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
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
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.
copy and paste the code into your terminal and hit enter. Open the google folder you should see token.json, navigate back to localhost:3000/v1/users and if all goes well you should see
Let's add a function to retrieve data from our sheet
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
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.
Now let's format our retrieved users to look more pretty.
retrieveFromSheet.js should look like this
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
Let’s go over the code base again in bits and pieces, to have a better understanding.
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
!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
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
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.