Send WhatsApp messages from Google Sheets using Python

Want to make your business communication on WhatsApp more effective? This tutorial will show you how to send messages to phone numbers stored in a Google Sheets CSV file using Python, the requests library, and the Wassenger API. You’ll learn to download the CSV file from Google Sheets, read the phone numbers and messages, and send them automatically with Wassenger.

🫣 Don’t want to use programming? No problem! Explore our new no-code WhatsApp Campaigns feature. Import your contacts, define a message, set a delivery date and relax! 🥳 🥳

This article is just a small taste of all the API features. Explore dozens of use cases and ready-to-use code examples here.

Prerequisites

• Basic understanding of Python
• Python 3 installed on your computer
• A text editor for writing code (e.g: Visual Studio Code, Atom, Vim)
• A Wassenger API token (you can obtain this by signing up here)

🤩 🤖 Wassenger is a complete communication platform and API solution for WhatsApp. Explore more than 100+ API use cases and automate anything on WhatsApp by signing up for a free trial and get started in minutes!

Install required packages

First, create a new directory for your project and navigate to it in your terminal. Then, run the following commands to install the necessary libraries:

pip install requests pandas

Prepare the Google Sheets CSV file

Create a new Google Sheets document and fill it with two columns:

• First column: phone number in E164 format with the country prefix.
• Second column: text message to send to the target phone number.

The Google Sheets document should have at least two columns and look like:

The equivalent Sheets document exported as CSV should look like this:

+447362053576,"👋 Welcome to {{your-business-name}}! Thanks for signing up. We are just a message away!"
+447362053576,"💐 Your order has been shipped. Tracking number is {{tracking-number}}. Don't hesitate to reach out to if you need help! 🤗"

Get the download URL of your Google Sheets document

1. Click on “File” in the top left corner.
2. Go to “Share” > “Publish to the web”.
3. In the “Link” tab, select “Comma-separated values (.csv)” from the dropdown menu.
4. Select the desired sheet page with the relevant data: by default the first one.
5. Click on “Publish” and copy the URL.

🤩 🤖 Wassenger is a complete API solution for WhatsApp. Sign up for a 7-day free trial and get started in minutes!

Send text messages

Create a new file named send_messages.py in your project directory and add the following code:

import requests
import pandas as pd

# Replace this with the URL of your published Google Sheets CSV file
# See the indications above to obtain the Google Sheets download URL to enter here
google_sheets_csv_url = 'ENTER_GOOGLE_SHEETS_CSV_URL_HERE'

# Replace this with your Wassenger API token
# Get your API token here: https://app.wassenger.com/apikeys
token = 'API_TOKEN_GOES_HERE'

# Optionally specify the target WhatsApp device ID connected to Wassenger
# you want to use for messages delivery (24 characters hexadecimal value)
device = 'DEVICE_ID'

# Define the headers for the API request
headers = {
  'Content-Type': 'application/json',
  'Authorization': f"{token}"
}

# Define the URL for the Wassenger API
api_url = 'https://api.wassenger.com/v1/messages'

# Read the Google Sheets CSV file and parse it as a CSV
data = pd.read_csv(google_sheets_csv_url)

# Iterate through the DataFrame rows
for index, row in data.iterrows():
    phone_number = row[0]
    message = row[1]

    # Create the payload for the API request
    payload = {
        'phone': phone_number,
        'body': message,
        'device': device
    }

    # Send the message using the Wassenger API
    response = requests.post(api_url, headers=headers, json=payload)

    # Check if the API request was successful
    if response.status_code == 200:
        print(f"Message sent to {phone_number}: {message}")
    else:
        print(f"Failed to send message to {phone_number}: {response.text}")

print("Finished sending messages")

Play and run code in the cloud without installing any software in your computer. Create a free account in Replit and get started in minutes.

Send media messages

In this example, we will create a different program called send_media.py to send multiple image media messages to different phone numbers loaded from a Google Sheets document.

To send a media message, the easiest way is to provide a file download URL. If your file is not already uploaded somewhere, you can upload the file to Google Drive and make the file publicly available to be downloaded by the API to send it later.

Example download URL from a public file in Google Drive:

https://drive.google.com/uc?id=1RG3CAPiwiFlFATUlIIwhk0RrbEU4PgVP&export=download

Important: the given download URL must return the file binary content, otherwise it will fail.

Create a new file named send_media.py in your project directory and add the following code:

import requests
import csv
from io import StringIO

# Replace this with the URL of your published Google Sheets CSV file
# See the indications above to obtain the Google Sheets download URL to enter here
google_sheets_csv_url = 'ENTER_GOOGLE_SHEETS_CSV_URL_HERE'

# Set the download URL of the file to be sent.
# The file must be publicly accessible from the Internet and return the file binary content
# You can also upload a local file in Google Drive and make the file publicly available for download
# Accepted file formats are: images (JPEG, WEBP, PNG), video (MP4), audio (MP3, OGG) and documents (PDF, XLSX, DOCX, ZIP...)
file_url = 'https://picsum.photos/seed/picsum/600/500'

# Replace this with your API token
# Get your API token here: YOUR_CONSOLE_URL/apikeys
token = 'API_TOKEN_GOES_HERE'

# Optionally specify the target device ID connected to the API
# you want to use for messages delivery (24 characters hexadecimal value)
device = 'DEVICE_ID'

# Define headers for the API request
headers = {
    'Content-Type': 'application/json',
    'Authorization': f'{token}'
}

# Define URLs for the API
base_url = 'https://api.wassenger.com/v1'
url = f'{base_url}/messages'
files_url = f'{base_url}/files'

def upload_file(url):
    body = {'url': url}
    try:
        response = requests.post(files_url, json=body, headers=headers)
        response.raise_for_status()
        return response.json()['id']
    except requests.exceptions.HTTPError as error:
        if error.response.status_code == 409 and error.response.json():
            return error.response.json()['meta']['file']
        print(f'Failed to upload file: {error.response.text}')
        return None

def normalize_phone(phone):
    return f"+{phone.replace('\\D', '')}"

def send_message(phone, message, file):
    body = {
        'phone': phone,
        'message': message.strip(),
        'device': device,
        'media': {'file': file}
    }
    try:
        response = requests.post(url, json=body, headers=headers)
        response.raise_for_status()
        print(f"==> Message created: {phone}")
    except requests.exceptions.HTTPError as error:
        print(f'Failed to create message to {phone}: {error.response.text}')

def main():
    try:
        if not file_url:
            raise ValueError('Missing required fileUrl')

        print('=> Downloading Google Sheets CSV file...')
        response = requests.get(google_sheets_csv_url)
        response.raise_for_status()
        data = response.text
        records = csv.reader(StringIO(data))

        print('=> Uploading file...')
        file_id = upload_file(file_url)
        if not file_id:
            raise ValueError('Failed to upload file: check the error')

        print('=> Processing messages...')
        for phone, message in records:
            if not phone or not message:
                continue

            number = normalize_phone(phone)
            if number and len(number) >= 8 and message:
                send_message(number, message, file_id)

    except ValueError as error:
        print(f'Error: {error}')
    except requests.exceptions.HTTPError as error:
        print(f'Error: {error.response.text}')

if __name__ == "__main__":
    main()

Play and run code in the cloud without installing any software in your computer. Create a free account in Replit and get started in minutes.

Replace the Google Sheets URL to export as CSV

In the send_messages.py and send_media.py files, make sure you have replaced the Google Sheets CSV URL and your actual Wassenger API token:

# Replace this with the URL of your published Google Sheets CSV file
google_sheets_csv_url = 'ENTER_GOOGLE_SHEETS_CSV_URL_HERE'

See the indications above to obtain the Google Sheets download URL to enter here.

🤩 🤖 Wassenger is a complete communication platform and API solution for WhatsApp. Explore more than 100+ API use cases and automate anything on WhatsApp by signing up for a free trial and get started in minutes!

Replace the API token

In the send_messages.py file, make sure you have defined the API token of your actual Wassenger account:

# Replace this with your Wassenger API token
token = 'API_TOKEN_GOES_HERE'

Optionally, if you have multiple WhatsApp numbers connected in your Wassenger account, you can specify which WhatsApp number you want to use for the message delivery by specifying the Wassenger unique device ID (24 characters hexadecimal value) in the following line:

# Optionally specify the target WhatsApp device ID connected to Wassenger
# you want to use for messages delivery (24 characters hexadecimal value)
device = 'DEVICE_ID'

Run the program

Before running the program, if you plan to send hundreds of messages in a row, we recommend to define a lower messages delivery speed per minute no more than 2–3 messages per minute to prevent ban issues due to anti-spam policies by WhatsApp. Learn more about best practices and how to reduce risk here.

Run the program in the cloud

You can run the program in the cloud for free on Replit.com without installing any software on your computer.

Simply create a new project and copy & paste the provided code, then click on “Run” to send the messages. It is that simple 😀

Run the program on your computer

Open a terminal in your project directory and run the following command to execute the send_messages.py or send_media.py script:

python send_messages.py

Similarly, you can run the send_media.py script to send media messages:

python send_media.py

If everything is set up correctly, you should see output indicating the messages have been created successfully:

=> Message created: +123456789009
=> Message created: +123456789009
=> Message created: +123456789009

Note messages will be added to your number’s message delivery queue and delivered asynchronously in the background over time based on your number’s subscription message delivery speed per minute limit or the manually configured delivery speed you have defined in your number’s settings.

Messages may take several minutes or hours, depending on how much you have created, to be effectively delivered to the target phone numbers via WhatsApp. You can monitor the progress fo the messages delivery in the web panel or automatically by using webhook events.

Conclusion

In this tutorial, you learned how to send messages to phone numbers stored in a Google Sheets CSV file using Python and the Wassenger API.

You can update the Google Sheets document and run the program again anytime you want to send new messages through your Wassenger-connected WhatsApp number.

You can further customize the script to handle additional columns, create different types of messages, or integrate it with your software as needed.

🤩 🤖 Wassenger is a complete communication platform and API solution for WhatsApp. Explore more than 100+ API use cases and automate anything on WhatsApp by signing up for a free trial and get started in minutes!