Integrating Node.js and Google Sheets - How to do it

Integrating Node.js and Google Sheets - How to do it

In today's data-driven world, managing and automating data processes is essential for businesses and developers. Google Sheets is a powerful tool for data storage and manipulation, and Node.js is a versatile runtime environment for building server-side applications. In this tutorial of Ohion, we'll explore integrating Node.js with Google Sheets to automate data tasks and streamline your workflow.

Prerequisites
Before we dive into the integration process, make sure you have the following prerequisites in place:
Node.js: Ensure that you have Node.js installed on your system. You can download it from nodejs.org.
Google Sheets API Credentials: You'll need to set up a project in the Google Cloud Console, enable the Google Sheets API, and obtain API credentials. Follow Google's official guide for this step.

Setting Up Your Project
Once you have Node.js and Google Sheets API credentials in place, you can start setting up your project. Follow these steps:
1 Create a New Node.js Project: Create a new directory for your project and initialize it with npm init to generate a package.json file.
2 Install Dependencies: Install the necessary Node.js packages for working with Google Sheets by running the following command:
 

npm install google-spreadsheet google-auth-library

3 Authentication: Implement the authentication process using the obtained API credentials. You can use the google-auth-library to authenticate your requests to Google Sheets.
4 Access Google Sheets: Use the Google Sheets API to access your spreadsheets. You can read, write, and manipulate data directly from your Node.js application.

Example Code
Here's a simple example of how to read data from a Google Sheet using Node.js:
 

const {
    GoogleSpreadsheet
} = require('google-spreadsheet');
const {
    JWT
} = require('google-auth-library');

class GoogleSheets{
    // [POST]
    async index(req, res) {
        const data = req.body;
        const dataInsert = {
            'name': data.name,
            'age': data.age,
            'phone': data.phone,
            'message': data.message,
        }
        const serviceAccountAuth = new JWT({
            email: process.env.GOOGLE_SERVICE_ACCOUNT_EMAIL,
            key: process.env.GOOGLE_PRIVATE_KEY.replace(/\\n/gm, '\n'),
            scopes: [
                'https://www.googleapis.com/auth/spreadsheets',
            ],
        });
        let status = 200
        let message = 'Insert Successfully !!!'

        try {
            const doc = new GoogleSpreadsheet(process.env.GOOGLE_SHEET_ID, serviceAccountAuth);
            await doc.loadInfo();
            const sheet = doc.sheetsByIndex[0];
            await sheet.addRow(dataInsert);
        } catch (error) {
            console.log('Error Send Information', error);
            status = 400
            message = 'Insert Failured!!!'
        }

        const result = {
            status,
            message
        }
        req.flash('result', result);
        res.redirect('/');
    }
}

module.exports = new GoogleSheets()

To get the key :

1 You have to go to IAM in console.cloud.google.com
2 Select service accounts  -> choose + CREATE SERVICE ACCOUNT  -> fill information and Done (for create IAM)

3 When creating IAM accounts, in the actions account choose Manage detail -> choose tab Key -> choose Add Key -> choose Create New Key -> Download file JSON

Finally, in file JSON, you have a GOOGLE_SERVICE_ACCOUNT_EMAIL and GOOGLE_PRIVATE_KEY.
Note: You have to share a role owner link Google Sheets for GOOGLE_SERVICE_ACCOUNT_EMAIL

Good luck!!! Hion Coding