WhatsApp API Google Sheets

Bot source code with basic functionality

Source Code

In this source code, the bot sends text from Google Sheets cells to the numbers specified in the cells. Records the status of the sent message, and if the client responds, records his first response in an additional cell. You can use this source code in your work or as a convenient platform to start your project.

GitHub - Whapi-Cloud/googlesheets-sender: How to connect a simple WhatsApp bot to Google Sheets using WhatsApp API. In this source code, the bot sends text from Google Sheets cells to the numbers specified in the cells. It records the status of the sent message, and if the client responds, it records their first response in an additional cell.GitHub

What is the functionality of this bot?

• Writing information to a table;

• Reading information from the table;

• Sending messages to numbers from the table;

Using this source code will allow you to customize the bot to your needs or take it as a basis for creating any other integration.

If you need help, just message us in the support chat on any page of the site. We can help you with bot installation or customization.

Script Demo: WhatsApp Bot for Google Sheets mailings

---

Usage instructions

Article Chapters:

• Setting up Google API services for bot operation

• Creating an account in Whapi.cloud for WhatsApp operation

• Configuring the Webhook link for WhatsApp API

• Checklist for setting up a WhatsApp bot with Google Sheets API on NodeJS

• Risks of being blocked for unwanted broadcasts or mass activity on WhatsApp

Setting up Google API services for bot operation

To get started, we need to refer to the official "Quick Start" article on NodeJS by Google, which includes an example of working with the API. We will use it as a basis.

Setting up an account for API operation

The first step requires us to visit the developer's site. To do this, enter your login and password for your Google account or create a new account.

After authorization, the developer window will open. Next, we will need to create a new project, which will allow integrating the operation of our bot with your service account in Google.

Creating a new project in Google APIs

Next, enter the information and click the "Create" button.

Creating a new project in Google APIs

After creating the project, you need to select the "Credentials" tab. There are two ways to access this section: either select the corresponding topic in the left menu or enter "Credentials" into the search bar.

Open the "Credentials" API

Next, click on the "CREATE CREDENTIALS" button and select "Service account".

Open the "Service account" API

In the first section, choose a service account name, it can be any name. The ID is filled in automatically. Pay attention to the Email address, as it represents the service email address, which we will need later. Then click CREATE AND CONTINUE.

Fill in "Service account details"

In the second section, choose a role. To do this, type "Editor" in the search bar of the dropdown list.

Select a role

The list offers many options, you need to select "Editor," as indicated below in the screenshot. Then click the CONTINUE button.

Editor

The third section does not require filling in. Click the DONE button.

The "DONE" button

After completing the setup, you will be redirected to the "Credentials" section, where in the list of service accounts, you will need to select the previously created email address. Save the service account's email address as it will be needed in the future.

Select the service account

In the appeared settings window for the service email, select the KEYS section. Then create a new key by clicking the ADD KEY button and choosing "Create new key".

Configure the service account key

The key type is JSON. Click CREATE. Automatically, you will be prompted to download a JSON file with the data. Save and rename it to keys.json for easier access from the project.

Creating a service account key

The project or bot mentioned in the step above can be found at the link.

GitHub - Whapi-Cloud/googlesheets-sender: How to connect a simple WhatsApp bot to Google Sheets using WhatsApp API. In this source code, the bot sends text from Google Sheets cells to the numbers specified in the cells. It records the status of the sent message, and if the client responds, it records their first response in an additional cell.GitHub

To work with it, download the project or clone it. Then, move our previously created key into it.

Then, in the terminal, type the command npm i. It will install the necessary dependencies and libraries for the bot to function. At this stage, work with the module is completed, and we move on to the next section.

You need to copy this file to the project root

At this point, the setup of services can be considered complete. Let's move on to the project itself.

Setting up the bot to work with Google Sheets

Before starting work with our bot, you will need to create a table in Google Sheets and grant access to the service account that we created earlier.

To do this, click the Share button and enter the Email address of the service account in the address bar.

In the new window, you will be asked to specify the role - Editor and click the Send button.

Now the service account has access to this table, allowing changes to be made to the bot.

Also, we attach a link to an example of filling out the table for a quick start.

Attention: to ensure the bot works correctly, you need to fill in the table as indicated in the example.

• Column A - phone numbers (without a plus sign with the country code in international format),

• Column B - message text (can be composed of other cells),

• Column C - delivery status (do not fill),

• Column D - recipient's response (do not fill).

You can fill in other columns as needed.

Example of a Google Sheet table

Next, open the config.js file where we will store the configuration data for the bot.

config.js file

And write the ID of our Google Sheet into it. You can find it in the address bar.

ID Google Sheets

config.js

We write the Google Sheets ID in config.js

---

Settings from Whapi.Cloud

You'll need your channel token

Let's write it to the configuration file config.js:

Move on to setting up the webhook.

---

Setting up the Webhook link for WhatsApp API

To track and enter message statuses and customer responses into the table, we need to use a webhook.

Our script currently tracks two events - message status and the recipient's first response.

Example of incoming information from the Webhook

Is it possible to do this without a webhook? It is possible, but only if we send requests in GET and POST. Consequently, the timeliness of changes may be missed. The webhook does this without a request, automatically, with any changes, which greatly facilitates working with feedback. For more information on what a webhook is and how to test it, you can read this article.

Webhooks | Help Desksupport.whapi.cloud

The easiest way to test it locally right here and now is to use tools like ngrok, which creates a secure tunnel to your local host, allowing you to provide access to your local server from the Internet.

In the video below, we will show you how to start ngrok and demonstrate the key principles of locally testing your WhatsApp bot.

When you run the ngrok program, enter the command: ngrok http 8080

This is the port on which our source script runs.

Let's take a link from Ngrok for our test. Launch it, get the link, and copy it to the config.js file.

We launch and obtain a unique link from ngrok

Link in config.js

Our script will automatically set up the webhook for your channel. No additional changes from you will be required. We have already provided the necessary webhook events and modes.

However, if you need to delve a little deeper into this and modify the source code to meet individual requirements, you will find the API method useful for managing channel settings:

Update channel settingsWhapi Cloud - Whatsapp API for developers

You can also manage channel settings in the interface

After checking on the local address, you will need to run the bot on your server. You will need to specify your domain as the webhook.

Checklist for setting up a WhatsApp bot with Google Sheets API on NodeJS

We are approaching the final stage of our guide. To ensure that we have done everything correctly, check the checklist.

Checklist for setting up a WhatsApp bot with Google Sheets API on NodeJS:

• Prepared Google API services for bot operation, obtained key.js and a service account;

• We have a table created according to the template in Google Sheets with Editor access to the service account;

• We created an account on Whapi.cloud to work with the WhatsApp channel;

• Connected the phone number from which the message distribution will be done;

• Filled in the necessary fields in the bot;

• Checked the webhook settings on the channel.

If you have filled in the previous steps correctly, you can start using the bot. Launch our script and check how the message distribution works.

Launch the Script

Make sure you are in the project directory in the terminal. Run the command: npm run start

Risks of being blocked for unwanted broadcasts or mass activity on WhatsApp

Permanent blocking of a WhatsApp account is the most unpleasant event for a user, as it leads to the impossibility of further use of the phone number in the messenger.

However, you have a chance to recover the number and we will tell you how to do it in the article.

Unlocking Your WhatsApp Number | Help Desksupport.whapi.cloud

Attention: you can request unblocking only once! Therefore, we recommend carefully reviewing our recommendations for reducing blocking risks.

Below, we will discuss the most common user mistake.

Mass mailings

Mass mailings is a very convenient marketing tool that allows you to notify a large number of people about anything. However, excessive use of this tool can result in a ban.

First, user complaints. If you include non-key consumers of your content in mass mailings, you may receive a complaint/spam tag/block from them. Next, the WhatsApp service accesses the last five messages received by the user from this company, group, or another user, as well as information about when this message was sent and the type of message sent (image, video, text, etc.). Based on this information, you may receive either temporary (from 24 hours to 48 hours) or permanent blocking.

Attention: when you first message a customer, they may mark your messages as spam. Therefore, it is important to establish communication in such a way that the customer writes to you first or is interested in communicating.

Secondly, spam protection. Excessive activity of your WhatsApp account by WhatsApp services may seem suspicious, namely:

• Sending hundreds of messages per minute is the most common reason for blocking;

• Sending messages to users who are not on your contact list, including those with links;

• Sending the same impersonal text to many people, including uninterested users;

• Sending messages at equal intervals, for example, every 5 or 10 seconds;

• Creating multiple groups or channels and adding users who are not in your contact list;

• Mass mailings to users in a country different from the country where the broadcast is taking place.

All of the above automates your communication with clients, therefore, for WhatsApp services, you are considered a spammer, posing a threat to user security.

Thirdly, violating the rules and requirements of WhatsApp policy. For example, it is prohibited to distribute goods or services prohibited by policy (alcohol, weapons, medical consultations, financial pyramids, etc.) via the messenger.

So, how do you conduct mailings without risking getting banned?

Recommendations to minimize the risk of your WhatsApp account being blocked:

1. Always warm up your number

You should always warm up your number. This significantly reduces the risk of blocking.

Attention: the longer the number is registered with WhatsApp, the more resistant it is to blocking.

2. Breaks between broadcasts

Sending messages around the clock and without weekends is a very bad idea. Take breaks between batch mailings.

Attention: do not send more than 2 messages per minute, more than 6 hours per day, or more than 3 days in a row.

3. Engage with your audience

• Whenever possible, send messages to those for whom your messages will not be a surprise;

• Give the client the opportunity to unsubscribe from your mailings. Explicitly include the keyword STOP at the end of the message;

• Ask your clients to save your number in their contacts. Send them your business contact card by pressing the simple "Add to Contacts" button;

• Leave open-ended questions so that the user can reply to you.

Attention: strive to get at least 30 responses from customers (30%) out of 100 messages sent.

4. Group the audience by geographic location

Always try to group contacts by area code. WhatsApp expects the average person to mainly communicate with contacts located in the same area as their phone number.

Remember, if you have any questions or issues along the way, our support team is always here to help. We value each user and strive to ensure that your experience with our API is as smooth and efficient as possible.