Many businesses rely on Google Sheets for secure data storage and easy accessibility. It can also function as a centralized hub to streamline operations. However, manually reaching out to leads or sending information stored in Google Sheets can quickly become tedious. That’s where integrations with Google Sheets can help, allowing you to automate the process of sending WhatsApp messages directly using your sheet’s data.
In this guide, I’ll show you how to automatically send WhatsApp messages using Google Sheets. This process not only sends text messages but also extracts data from specific columns in the sheet to personalize the message. Once you set up the campaign in our app, messages will be sent automatically whenever new entries are added or specific events occur in the sheet.
Follow these steps to successfully create your automation campaign.
Set up the Google Sheet
The first thing you will need is a Google sheet filled with data that you can use to personalize your WhatsApp message. It is also essential that this sheet contains the WhatsApp numbers that will receive it.
In this example, we have a list of new hires for a marketing agency. Our sheet contains four columns: Phone number, Name, ID Number and Designation. We will map these fields so each new member of the team can receive a personalized WhatsApp message with their ID Number and Designation.
Organize your data in a similar fashion. Note that you can add as many columns as you need.
Create the variables
We can think of variables as empty fields that are shared between different applications, and can be filled automatically with data sent between them. Right now, we will create three variables, one corresponding to each of the columns that we have in our Google Sheet (Name, ID Number and Designation). Note that we are omitting the Phone Number, we will insert it somewhere else.
To create a variable, open the app and go to WhatsApp Bot Manager > Message Template. Scroll down to the Template Variables section and click on the Create button.
Give your variable a single-worded name to identify it and save it. Create one variable for each of the fields you wish to personalize.
The variables will automatically appear on the app.
Create a Message Template
WhatsApp only lets you use Message Templates for business-initiated messages, so you’ll need to create one. Although you could do this directly on Meta’s Template Manager, for this tutorial we will use the integration within our app for an easier access to the variables we created before. Let’s see how.
Go back to WhatsApp Bot Manager > Message Template. Click on the Create button at the top right corner.
On the menu, select General Template.
Name the template, select its language and select the template category (we recommend using marketing). Optionally, add a Header; for our example, we will leave it empty.
Write the message body including the variables you created before. Click on the Variables button to insert them.
Finally, save the message template and close the window. Click on the Sync Template button and see if It’s approved or not. If the template is approved, then it’s good to use it for the automation campaign.
Create a Webhook Workflow
From the left side menu, select Webhook Workflow and then click on the Create button. Give the workflow a name.
Select the appropriate WhatsApp number and the Message Template that you created before. Click on Create Workflow.
A webhook URL will be created and you will see a Google Sheet trigger Instructions button. Click on it.
You will see a window with several instructions and a block of code. Copy that code and go to the Google Sheet.
On the Google Sheet’s top menu, go to Extensions > Apps Script.
Paste the code that you copied before, replacing the existing function. Then, press CTRL + S to save.
After saving the code, select Triggers from the left side menu and click on Add Trigger. As event type, set On Change and save.
While saving, you may be asked to select a Google account and verify the app is safe. Just select your account and click on Advanced options > Go to project. Finally, press the Allow button.
Mapping the Webhook Workflow
Go back to the app and click on the Capture Webhook Response button.
Go back to the Google Sheet and add new sample data to the columns. This data will appear in the app as raw data, helping you map it to the variables in your Message Template.
In the phone number field, select the corresponding data from the Phone Number column. Similarly, map the row data to the variables used in your message template. Finally, save the workflow.
That’s it! Now, whenever a row is added to the Google Sheet, our app will send a message to the indicated phone number.
This is how we can integrate Google Sheets with our app to automatically send WhatsApp messages. If you have additional doubts, you can always contact us. We will be glad to help.
