WhatsApp API Google Sheets
Bot source code with basic functionality
Last updated
Bot source code with basic functionality
Last updated
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.
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.
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
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.
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.
Next, enter the information and click the "Create" button.
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.
Next, click on the "CREATE CREDENTIALS" button and select "Service account".
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.
In the second section, choose a role. To do this, type "Editor" in the search bar of the dropdown list.
The list offers many options, you need to select "Editor," as indicated below in the screenshot. Then click the CONTINUE button.
The third section does not require filling in. Click 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.
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".
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.
The project or bot mentioned in the step above can be found at the link.
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.
At this point, the setup of services can be considered complete. Let's move on to the project itself.
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.
Next, open the config.js file where we will store the configuration data for the bot.
And write the ID of our Google Sheet into it. You can find it in the address bar.
config.js
Let's write it to the configuration file config.js:
Move on to setting up the webhook.
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.
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.
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.
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:
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.
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:
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.
Make sure you are in the project directory in the terminal. Run the command: npm run start
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.
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 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?
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.
