How to Integrate Google Sheet in Nodejs Application
Tags: node, webdev, showdev, javascript
Published: 2020-08-06 14:54:27


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.

<!-- ![https://www.youtube.com/watch?v=qUKz81cococ](https://www.youtube.com/watch?v=qUKz81cococ) -->

## Demo

{% youtube qUKz81cococ %}

## Recent Articles

[Kubernetes for Nodejs Developers](https://cloudnweb.dev/2020/07/kubernetes-nodejs-developers)

[Building a Production-grade Nodejs,GraphQL and TypeScript Server - Testing and Docker](https://cloudnweb.dev/2020/05/nodejs-graphql-typescript-starter-part-3)

Consider that you are building a coach booking application where you need to send the total bookings for a specific month to the sales team periodically. you can achieve this using,

1. There can be a button on the CRM dashboard where Salespeople can go and export the booking data from the database to Google Sheet or as PDF.

Even though it works, it still involves some manual process of going to the dashboard and export it to the google sheet. I would say, we can still automate this process and make it easier.

Here's the updated workflow,

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/jdgurnkm3ysw7pprsj93.png)

### Setup and Install

Let's install the dependencies for our project here,

```bash
npm install express body-parser ejs express-ejs-layouts node-fetch
```

- [express](https://www.npmjs.com/package/express) - library for http webserver to handle the request and response.
- [body-parser](https://www.npmjs.com/package/body-parser) - it is used to parse the POST request body.
- [ejs](https://www.npmjs.com/package/ejs) - it is a template engine used to handle the views in our express application.
- [express-ejs-layouts](https://www.npmjs.com/package/express-ejs-layouts) - mapper library between expressjs and ejs template engine.
- [node-fetch](https://www.npmjs.com/package/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.

```jsx
const express = require("express")

const app = express()

// Express body parser
app.use(express.urlencoded({ extended: true }))

app.get("/", (req, res) => {
  res.send("dashboard")
})

app.listen(3005, () => {
  console.log("Server is running on PORT 3005")
})
```

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

```jsx
const expressLayouts = require("express-ejs-layouts")

app.use(expressLayouts)
app.set("view engine", "ejs")
```

our views folder will look like,

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/1qg7wcncloswlpc6nm84.png)

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

```jsx
app.post("/save", (req, res) => {
  const username = req.body.username
  const email = req.body.email
  const phone = req.body.phone
  const fromdate = req.body.fromdate
  const todate = req.body.todate

  // GOOGLE SHEET LOGIC COMES HERE

  res.render("success")
})
```

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

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/7fv1wi0am8wqvx4buhif.png)

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

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/xzmmx3sy23ptfbxgekcn.png)

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

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/648wl9k5uypdaxg1dmxd.png)

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

Check out the script from the source [here](https://gist.github.com/willpatera/ee41ae374d3c9839c2d6)

```jsx
// original from: http://mashe.hawksey.info/2014/07/google-sheets-as-a-database-insert-with-apps-script-using-postget-methods-with-ajax-example/

function doGet(e) {
  return handleResponse(e)
}

// Usage
//  1. Enter sheet name where data is to be written below
var SHEET_NAME = "Sheet1"

//  2. Run > setup
//
//  3. Publish > Deploy as web app
//    - enter Project Version name and click 'Save New Version'
//    - set security level and enable service (most likely execute as 'me' and access 'anyone, even anonymously)
//
//  4. Copy the 'Current web app URL' and post this in your form/script action
//
//  5. Insert column names on your destination sheet matching the parameter names of the data you are passing in (exactly matching case)

var SCRIPT_PROP = PropertiesService.getScriptProperties() // new property service

// If you don't want to expose either GET or POST methods you can comment out the appropriate function

function doPost(e) {
  return handleResponse(e)
}

function handleResponse(e) {
  // shortly after my original solution Google announced the LockService[1]
  // this prevents concurrent access overwritting data
  // [1] http://googleappsdeveloper.blogspot.co.uk/2011/10/concurrency-and-google-apps-script.html
  // we want a public lock, one that locks for all invocations
  var lock = LockService.getPublicLock()
  lock.waitLock(30000) // wait 30 seconds before conceding defeat.

  try {
    // next set where we write the data - you could write to multiple/alternate destinations
    var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"))
    var sheet = doc.getSheetByName(SHEET_NAME)

    // we'll assume header is in row 1 but you can override with header_row in GET/POST data
    var headRow = e.parameter.header_row || 1
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
    var nextRow = sheet.getLastRow() + 1 // get next row
    var row = []
    // loop through the header columns
    for (i in headers) {
      if (headers[i] == "Timestamp") {
        // special case if you include a 'Timestamp' column
        row.push(new Date())
      } else {
        // else use header name to get data
        row.push(e.parameter[headers[i]])
      }
    }
    // more efficient to set values as [][] array than individually
    sheet.getRange(nextRow, 1, 1, row.length).setValues([row])
    // return json success results
    return ContentService.createTextOutput(
      JSON.stringify({ result: "success", row: nextRow })
    ).setMimeType(ContentService.MimeType.JSON)
  } catch (e) {
    // if error return this
    return ContentService.createTextOutput(
      JSON.stringify({ result: "error", error: e })
    ).setMimeType(ContentService.MimeType.JSON)
  } finally {
    //release lock
    lock.releaseLock()
  }
}

function setup() {
  var doc = SpreadsheetApp.getActiveSpreadsheet()
  SCRIPT_PROP.setProperty("key", doc.getId())
}
```

Note: Don't forget 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 a web app by clicking the `Publish` tab.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/uek2t7lu7xi6w58r3ftl.png)

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/ivqvj7nqk88tkumic8og.png)

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

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/qh8xb5vgu52eggek1eib.png)

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

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/t8ena9wdne6becu85zo2.png)

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

```jsx
app.post("/save", (req, res) => {
  const username = req.body.username
  const email = req.body.email
  const phone = req.body.phone
  const fromdate = req.body.fromdate
  const todate = req.body.todate

  const url = `${GOOGLE_SHEET_URL}?Name=${encodeURIComponent(
    username
  )}&Email=${encodeURIComponent(email)}&Phone=${encodeURIComponent(
    phone
  )}&FromDate=${encodeURIComponent(fromdate)}&ToDate=${encodeURIComponent(
    todate
  )}`

  fetch(url)
    .then(res => {
      return res.json()
    })
    .then(res => console.log("google sheet res", { res }))
    .catch(error => console.error(error))

  res.render("success")
})
```

Here, we pass all the data as a query parameter. make sure the name matches with a column name that we have given in the google sheet.

[Source code](https://github.com/ganeshmani/googlesheet-nodejs-tutorial)