How to Send WhatsApp Messages from Google Sheets using the WhatsApp API

WhatsApp is a popular messaging platform used by millions of people worldwide. Whether you want to send bulk messages to your clients, notify your team, or automate messages for your business, integrating WhatsApp with Google Sheets can save time and streamline the process. This guide will show you how to send WhatsApp messages from Google Sheets using the WhatsApp API, and how to set up the integration with the Twilio API.

Why Use Google Sheets with WhatsApp API?

Google Sheets is an excellent tool for managing data, and with the power of WhatsApp API, you can automate the process of sending messages directly from your spreadsheet. Using Google Sheets for this task allows you to easily manage contact details, write custom messages, and send them in bulk—all from one centralized location.

What You Need:

WhatsApp API Access: In this guide, we will use the Twilio API to send WhatsApp messages. You’ll need a Twilio account to proceed.
Google Sheets Account: You must have a Google account and access to Google Sheets.
Google Apps Script: This script will allow you to automate the process of sending WhatsApp messages from the sheet.

Steps to Send WhatsApp Messages from Google Sheets Using the WhatsApp API

Step 1: Set Up a Twilio Account

First, sign up for a Twilio account and set up the WhatsApp sandbox:

Sign up on Twilio: Visit the Twilio website and create a free account.
Get Your Twilio Credentials: Once you sign in, you will be provided with your Twilio Account SID and Auth Token. These credentials are required for the API.
Set Up WhatsApp Sandbox: Follow Twilio’s setup guide to enable WhatsApp messaging via their sandbox. You'll receive a WhatsApp-enabled number.

Step 2: Set Up Your Google Sheet

1. Create a new Google Sheets document or use an existing one.

2. Add the columns for Phone Number and Message that will hold the data of recipients and the message content.

Phone Number	Message
+11234567890	Hello, your order is ready!
+10987654321	Reminder: Your meeting is at 3 PM.

Step 3: Create a Google Apps Script to Send WhatsApp Messages

1. Go to Extensions > Apps Script in Google Sheets.

2. Replace the placeholder code with the following:


                function sendWhatsAppMessage() {

                  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

                  var range = sheet.getDataRange();

                  var values = range.getValues();


                  // Twilio credentials

                  var sid = 'YOUR_TWILIO_SID';

                  var authToken = 'YOUR_TWILIO_AUTH_TOKEN';

                  var twilioNumber = 'YOUR_TWILIO_WHATSAPP_NUMBER';


                  // Loop through the rows and send messages

                  for (var i = 1; i < values.length; i++) {

                    var phoneNumber = values[i][0];

                    var message = values[i][1];

                    var url = 'https://api.twilio.com/2010-04-01/Accounts/' + sid + '/Messages.json';


                    var payload = {

                      'To': 'whatsapp:' + phoneNumber,

                      'From': 'whatsapp:' + twilioNumber,

                      'Body': message

                    };


                    var options = {

                      'method': 'post',

                      'contentType': 'application/x-www-form-urlencoded',

                      'payload': payload,

                      'headers': {

                        'Authorization': 'Basic ' + Utilities.base64Encode(sid + ':' + authToken)

                      }

                    };


                    UrlFetchApp.fetch(url, options);

                }

                }

Important: Replace 'YOUR_TWILIO_SID', 'YOUR_TWILIO_AUTH_TOKEN', and 'YOUR_TWILIO_WHATSAPP_NUMBER' with your actual Twilio credentials and WhatsApp sandbox number.

Step 4: Run the Script

1. Save the script in the Apps Script editor.

2. Run the script by selecting Run > sendWhatsAppMessage from the Apps Script editor.

3. Authorize the permissions that the script requires to access Google Sheets and interact with external APIs.

Step 5: Automate the Sending of WhatsApp Messages

You can also automate the process by scheduling the script to run at specific intervals:

In the Apps Script editor, go to Triggers > Add Trigger.
Set the function to sendWhatsAppMessage and choose your preferred schedule (e.g., daily, hourly).

Troubleshooting Tips

Check your Twilio Sandbox: Ensure that your Twilio WhatsApp number is active and correctly set up.
Validate Phone Numbers: Ensure that phone numbers are in the correct international format (e.g., +1234567890).
Debugging the Script: Use the Logger.log() method in Google Apps Script to track issues during script execution.

Conclusion

By following this guide, you can easily send WhatsApp messages directly from Google Sheets using the WhatsApp API. Whether you need to send bulk notifications, reminders, or updates to your clients or team members, this integration can help automate the process and save time.