How to Integrate Google Sheet in Nodejs Application

This article explains how to Integrated Google sheet with your Nodejs Application. There are several use-cases where integrating google sheet with your application is crucial.

Demo

Setup and Install

Let's install the dependancies for our project here,

1npm install express body-parser ejs express-ejs-layouts node-fetch

express - library for http web server to handle the request and response.
body-parser - it is used to parse the POST request body.
ejs - it is a template engine used to handle the views in our express application.
express-ejs-layouts - mapper library between expressjs and ejs template engine.
node-fetch - fetch library used to make htttp request from nodejs to external url.

After that, create a file app.js and add the basic express setup inside that file.

1const express = require("express")
2
3const app = express()
4
5// Express body parser
6app.use(express.urlencoded({ extended: true }))
7
8app.get("/", (req, res) => {
9  res.send("dashboard")
10})
11
12app.listen(3005, () => {
13  console.log("Server is running on PORT 3005")
14})

Now, let's add the template engine into our express application.

1const expressLayouts = require("express-ejs-layouts")
2
3app.use(expressLayouts)
4app.set("view engine", "ejs")

our views folder will look like,

Inside the dashboard, we will have a form which sends us the form data to our express application. let's create a POST route to handle it inside our express application.

1app.post("/save", (req, res) => {
2  const username = req.body.username
3  const email = req.body.email
4  const phone = req.body.phone
5  const fromdate = req.body.fromdate
6  const todate = req.body.todate
7
8  // GOOGLE SHEET LOGIC COMES HERE
9
10  res.render("success")
11})

Now, it is time to integrate google sheet into our application. There are several ways to do it. Here, we are going to use a simple and quick solution that solves our problem.

Let's create a google sheet first

Here, you have a tab called Tools . click on that , you can see Script editor where we can write our script for this sheet.

Here, we are going to write a script which adds the data based on the Column Name inside our sheet. let's add field name from our application as column name in the sheet.

Once we add that, we can write a script to add the value respective the column name that comes from the nodejs application.

Checkout the script from the source here

1// original from: http://mashe.hawksey.info/2014/07/google-sheets-as-a-database-insert-with-apps-script-using-postget-methods-with-ajax-example/
2
3function doGet(e) {
4  return handleResponse(e)
5}
6
7// Usage
8//  1. Enter sheet name where data is to be written below
9var SHEET_NAME = "Sheet1"
10
11//  2. Run > setup
12//
13//  3. Publish > Deploy as web app
14//    - enter Project Version name and click 'Save New Version'
15//    - set security level and enable service (most likely execute as 'me' and access 'anyone, even anonymously)
16//
17//  4. Copy the 'Current web app URL' and post this in your form/script action
18//
19//  5. Insert column names on your destination sheet matching the parameter names of the data you are passing in (exactly matching case)
20
21var SCRIPT_PROP = PropertiesService.getScriptProperties() // new property service
22
23// If you don't want to expose either GET or POST methods you can comment out the appropriate function
24
25function doPost(e) {
26  return handleResponse(e)
27}
28
29function handleResponse(e) {
30  // shortly after my original solution Google announced the LockService[1]
31  // this prevents concurrent access overwritting data
32  // [1] http://googleappsdeveloper.blogspot.co.uk/2011/10/concurrency-and-google-apps-script.html
33  // we want a public lock, one that locks for all invocations
34  var lock = LockService.getPublicLock()
35  lock.waitLock(30000) // wait 30 seconds before conceding defeat.
36
37  try {
38    // next set where we write the data - you could write to multiple/alternate destinations
39    var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"))
40    var sheet = doc.getSheetByName(SHEET_NAME)
41
42    // we'll assume header is in row 1 but you can override with header_row in GET/POST data
43    var headRow = e.parameter.header_row || 1
44    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
45    var nextRow = sheet.getLastRow() + 1 // get next row
46    var row = []
47    // loop through the header columns
48    for (i in headers) {
49      if (headers[i] == "Timestamp") {
50        // special case if you include a 'Timestamp' column
51        row.push(new Date())
52      } else {
53        // else use header name to get data
54        row.push(e.parameter[headers[i]])
55      }
56    }
57    // more efficient to set values as [][] array than individually
58    sheet.getRange(nextRow, 1, 1, row.length).setValues([row])
59    // return json success results
60    return ContentService.createTextOutput(
61      JSON.stringify({ result: "success", row: nextRow })
62    ).setMimeType(ContentService.MimeType.JSON)
63  } catch (e) {
64    // if error return this
65    return ContentService.createTextOutput(
66      JSON.stringify({ result: "error", error: e })
67    ).setMimeType(ContentService.MimeType.JSON)
68  } finally {
69    //release lock
70    lock.releaseLock()
71  }
72}
73
74function setup() {
75  var doc = SpreadsheetApp.getActiveSpreadsheet()
76  SCRIPT_PROP.setProperty("key", doc.getId())
77}

Note: Don't forgot to change the sheet name in the script before deploy.

After that, save the script, run the function setup from the script and deploy it as web app by clicking the Publish tab.

Once you click the Deploy, you need make sure that you configure it correctly. select the Execute the app as → Me and Access Level as Anyone

Now, it will give you an web app URL. copy and store it inside your nodejs application.

Now, coming back to our application, we just need to run GET request on the URL to save the data to google sheet.

1app.post("/save", (req, res) => {
2  const username = req.body.username
3  const email = req.body.email
4  const phone = req.body.phone
5  const fromdate = req.body.fromdate
6  const todate = req.body.todate
7
8  const url = `${GOOGLE_SHEET_URL}?Name=${encodeURIComponent(
9    username
10  )}&Email=${encodeURIComponent(email)}&Phone=${encodeURIComponent(
11    phone
12  )}&FromDate=${encodeURIComponent(fromdate)}&ToDate=${encodeURIComponent(
13    todate
14  )}`
15
16  fetch(url)
17    .then(res => {
18      return res.json()
19    })
20    .then(res => console.log("google sheet res", { res }))
21    .catch(error => console.error(error))
22
23  res.render("success")
24})