messengers bot with Google Sheets API in NodeJS

Preparation and Setting Up Google Services

The example of working with API we offer below is based on the official article Node.js Quickstart from Google

Setting Up an Account to Work with API

First of all, go to the developer’s website and create a new project.

Creating a project in Google APIs

This done, open the Library tab

Open the API Library

You will see a list from which you need to select Google Sheets API. Next, click on the Enable button.

Click on the Enable button

At this point, you will be redirected to the page with settings for the API. This is where you will need to create credentials. Click on the Create credentials button.

In the resulting window, choose the settings for the service account and press on the Select the credentials type button. We recommend you use the credentials shown in the screenshot:

We recommend using these settings for the service account

Then you will need to choose the name and role for the account. Select the Editor role.

Adding credentials. Selecting a role - Editor

Select the type of the key - JSON. After that, download a JSON file with data. Save the file to the folder with the project and rename it to keys.json so that it would be handy to use it in the project.

Your account is almost set up. All that is left to do now is enable the account as Editor in Google Sheets. To do this, open the sheet and press the Access Settings button.

We need to add the account that we created as the editor of this table. Now look up the email address of the account in the developers console in the Credentials tab and copy it.

Credentials from where you want to copy the email

Assigning an editor to a created account

Your services are now ready to go! Let’s move on to the project itself.

Creating a Module for Google API

To begin with, create a config.js file where you will keep configuration data for your bot. Add the ID of your Google sheet to it. You can find the ID in the address bar.

How to find a Google Sheet ID

config.js

module.exports = {
    spreadid:"1M6fyEhv64ug7zILRz86H1PBKEKHUgKV9pWSW2m_r4SI",  // Google sheet ID
}

Next, create a Googleapi.js file to keep all the functions and data related to your Google API. First of all, you will need to install a Google API module for NodeJS.
To install the module, enter the command npm install Googleapis@39 --save in the terminal. You will also need to import dependencies in the file itself.

const config = require("./config.js");
const {Google} = require('Googleapis');
const keys = require('./keys.json');

Next, create a client object for authentication in Google.

const client = new Google.auth.JWT(
    keys.client_email,
    null,
    keys.private_key,
    ['https://www.Googleapis.com/auth/spreadsheets']
) //Json Web Token

The parameters that the JWT function takes include:

• The email address from the JSON file with credentials;
• The path to the file with a private key (which is null since we don’t send it);
• The private key from the JSON file with credentials;
• A list of access permissions. In our case, it is only Google Sheets. If necessary, you can add other Google APIs to the list.

The next step is to call the function that allows you to be authenticated in the system.

client.authorize(function(err, tokens) {
    if (err){
        console.log(err);
        return;
    }

    console.log('Connected Google Sheets Api!');
    gsrun(client);
});

let gsapi;

async function gsrun(cl){
    gsapi = Google.sheets({version:'v4', auth:cl})
}

If everything goes well, output Connected Google Sheets API! to the console and add the Sheets class into a gsapi object. The Sheets class takes as parameters the version of the API used and the Client object that we have created before.

The Method of Receiving Data

async function getValues(range)
{
    const opt = {
        spreadsheetId: config.spreadid,
        range : range
    }

    let data = await gsapi.spreadsheets.values.get(opt);
    let dataArray = data.data.values;

    return dataArray;
}

In order to receive data from the sheet, you will need to write a function. (As a parameter, it takes a cell range with the following format: List1!A1:B2) where List1 is the name of your list in the sheet. Be careful when you specify the parameter.

opt - a dictionary of parameters that we send as a request to the Google API.

• spreadsheetId - the sheet ID;
• range - the range of values from where to extract data;

To extract data from the spreadsheet, you need to send opt to the gsapi.spreadsheets.values.get(opt) method;

The method returns all the information about the request. As for the data itself, it is stored in data.values.

Now it’s time to write a method allowing us to insert data into the spreadsheet. To add data to the end of the sheet, you need to first find out the number of the last line. Since API does not let us do so directly, let us first define the method that will return the number of the last line, and then add the data.

async function getLastRow() // Get the number of the last row in the table
{
    const opt = {
        spreadsheetId: config.spreadid,
        range: 'Data!A1:A'
    }
    let response = await gsapi.spreadsheets.values.get(opt);
    return response.data.values.length;
}

The method consists in receiving all data from the A1:1 range, that is, to the end of the table, and then returning the length of the resulting array.

The Method of Writing Data

async function updateSheet(name, phone) // Write in the last row of the table the data.
{
    let lastRow = await getLastRow() + 1;
    const opt = {
            spreadsheetId : config.spreadid,
            range: 'Data!A' + lastRow,
            valueInputOption:'USER_ENTERED',
            resource: {values: [[name, phone]]}
    }
    await gsapi.spreadsheets.values.update(opt);
}

The method accepts the name and the phone number as parameters (you will store them in the sheet). Also, the opt dictionary now includes additional parameters, that is, your data. Mind that values is an array of arrays. Thus, we can send a range of data, not just one line. To record data, use the update method.

module.exports.updateSheet = updateSheet;
module.exports.getValues = getValues;
module.exports.getLastRow = getLastRow;

Working with the messengers API

The first thing you should do is to connect messengers to our script so that you could check the bot’s work while you are writing the code. To do this, go to your user account and get the QR code. Then open messengers on your mobile phone and go to Settings -> messengers Web -> Scan the QR code.

Get access to messengers API

To work with the messengers API, you will need a token and Uri from your user account. You can find them in the header of your instance.

Add them to the configuration file:

module.exports = {
    apiUrl: "https://eu115.chat-api.com/instance12345/",
    token: "1hi0xwfzaxsews12345", // Token for working with API from personal cabinet
    spreadid:"1M6fyEhv64ug7zILRz86H1PBKEKHUgKV9pWSW2m_r4SI",  // Google table ID
}

Next, create an index.jsjs file. It will contain all the bot logic as well as a server processing Webhook requests. Import all dependencies.

const config = require("./config.js");
const Googleapi = require("./Googleapi.js");
const token = config.token, apiUrl = config.apiUrl;
const menu_text = config.menuText;
const app = require('express')();
const bodyParser = require('body-parser');
const fetch = require('node-fetch');

• node-fetch makes it possible to make requests to the API, while config will load your data from another file;
• token and apiUrl are your data (from the configuration file) that allow you to address the WA API;
• The Express module is necessary for deploying a web server that will process requests;
• body-parser allows for easy extraction of incoming request;
• Googleapi is our Google API module;

app.use(bodyParser.json());

process.on('unhandledRejection', err => {
    console.log(err)
});

Then describe the function that will work with the messengers API.

async function apiChatApi(method, params){
    const options = {};
    options['method'] = "POST";
    options['body'] = JSON.stringify(params);
    options['headers'] = { 'Content-Type': 'application/json' };

    const url = `${apiUrl}/${method}?token=${token}`;

    const apiResponse = await fetch(url, options);
    const jsonResponse = await apiResponse.json();
    return jsonResponse;
}

This function takes as a parameter the method that needs to be executed and the object with parameters that we will send with the request. Inside the function, create the options object with two values: json and method. In the first one, you will send parameters necessary for the API, and, in the second, — specify the method which you call and from which you want to get a response. Next, define the constant — our URL for addressing the API. It will contain the URL itself (from the config), the method, and the token. After that, send a request to the Chat API.

Now that your function is ready, you can describe the main bot logic. Describe the handler where the webhook will send data.

app.post('/', async function (req, res) {
    const data = req.body;
}

The function and the handler are one and the same thing; they process POST requests at the server’s main address ('/' path is responsible for that). data is the received JSON file.

To figure out what particular JSON will come to the server, use the following testing tools.

Testing queries - Webhook simulation

app.post('/', async function (req, res) {
    const data = req.body;
    for (var i in data.messages) {
        const body = String(data.messages[i].body.toLowerCase());
        const chatId = data.messages[i].chatId;
        splitBody = body.split(' ');
        command = splitBody[0];

        if(data.messages[i].fromMe)
            return;

        if(command == 'help')
        {
            await apiChatApi('sendMessage', {chatId:chatId, body: menu_text});
        }
        else if (command == 'insert')
        {
            name = splitBody[1];
            phone = splitBody[2];
            await Googleapi.updateSheet(name, phone)
            await apiChatApi('sendMessage', {chatId:chatId, body: 'Successfully recorded'})
        }

        else if (command == 'info')
        {
            let result;
            if (splitBody.length == 1){
                result = await getInfoDataFromSheet('A2:D2');
            }
            else{
                result = await getInfoDataFromSheet(splitBody[1]);
            }
            x = await apiChatApi('sendMessage', {chatId:chatId, body: result})
            console.log(x);
        }

        else if (command == 'file')
        {
            linkFile = (await Googleapi.getValues('Data!D2'))[0][0];
            x = await apiChatApi('sendFile', {chatId:chatId, body: linkFile, 'filename':'testfile'})
        }

        else if (command == 'bulk'){
            lastRow = await Googleapi.getLastRow() + 1;
            dataAll = await Googleapi.getValues('Data!A2:D' + lastRow);
            dataAll.forEach(async function(entry){
                await apiChatApi('sendMessage', {phone:entry[1], body: `Hi, ${entry[0]}, its a test mailing.`});
            });
        }

        else
        {
            await apiChatApi('sendMessage', {chatId:chatId, body: menu_text})
        }
    }
    res.send('Ok');
});

In the handler, perform the actions corresponding to the received commands. Now, let us look at each of the actions separately and see the results of their testing.

Filling the sheet cells

To insert data to the spreadsheet, we need to split the message by using the split method and pass the name and the phone number to the function that we wrote for the Google API.

 else if (command == 'insert'){
    name = splitBody[1];
    phone = splitBody[2];
    await Googleapi.updateSheet(name, phone)
    await apiChatApi('sendMessage', {chatid:chatId, body: 'Successfully recorded'})
}

Reading data from the cell, receiving data

To receive data, we must either send the incoming cell range from the message, or, if the used has not sent the range, send the standard A2:D2.

 else if (command == 'info'){
    let result;
    if (splitBody.length == 1){
        result = await getInfoDataFromSheet('A2:D2');
    }
    else{
        result = await getInfoDataFromSheet(splitBody[1]);
    }
    await apiChatApi('sendMessage', {chatId:chatId, body: result})
}

The GetInfoDataFromSheet function simply forms the line out of the data arrays that GoogleApi returned to us.

async function getInfoDataFromSheet(range){
    data = await Googleapi.getValues('Data!' + range);
    result = "";
    data.forEach(function(entry) {
        result += entry.join(' ') + "\n"
    });
    return result;
}

Sending files to messengers

To send a file, (get) the direct link to the file from the sheet cell and send it using the sendFile method.

 else if (command == 'file'){
    linkFile = (await Googleapi.getValues('Data!D2'))[0][0];
    x = await apiChatApi('sendFile', {chatId:chatId, body: linkFile, 'filename':'testfile'})
}

else if (command == 'bulk'){
    lastRow = await Googleapi.getLastRow() + 1;
    dataAll = await Googleapi.getValues('Data!A2:D' + lastRow);
    dataAll.forEach(async function(entry){
        await apiChatApi('sendMessage', {phone:entry[1], body: `Hi, ${entry[0]}, its a test mailing.`});
    });
}

All the tests were successful. Now we can upload our bot to the server and install the Webhook.

Webhook

A webhook helps prevent pauses in answering incoming messages. Without it, our bot would have to constantly, at regular intervals, send requests for incoming data to the server. That would slow the response time and increase the server load.

But if you specify the webhook’s address, you are spared this problem. The servers will send notifications about incoming changes as soon as they appear. The webhook, in its turn, will receive and process them correctly, thus implementing the bot’s logic. You can specify either the domain name or the IP address.

Now you need to upload the bot to a hosting (dedicated server) and launch it. This done, specify the domain or IP address as a webhook in the user account and test the bot.

Set up webhook in your personal profile

Once you have launched the bot, you will see a notification about a successful connection:

Message about successful connection

messengers bot in conjunction with Google Sheets is ready

So, we described the work of a simple messengers chatbot and posted the source code with ready-to-use functionality on github.

You only need to substitute in the code your token from your personal account and instance number.

Now you need to upload server together with the bot to the hosting and specify your domain as the webhook. With each incoming message the server will receive and process the data. If you have any questions, you can always contact our technical support!

ChatAPI