Send messages from Google Sheets using PHP

Table of contents

Introduction
Prerequisites
Install required packages
Prepare the Google Sheets CSV file
Get the download URL of your Google Sheets document
Send text messages
Send media messages
Replace the Google Sheets URL to export as CSV
Replace the API token
Run the program
Conclusion

In this tutorial, you'll learn how to send messages to phone numbers stored in a Google Sheets CSV file using PHP and the Agent Connect API. We will download the CSV file from Google Sheets, read the phone numbers and messages, and then send them using the Agent Connect API.

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 PHP
PHP installed on your computer
A text editor for writing code (e.g: Visual Studio Code, Atom, Vim)
A Agent Connect API token (you can obtain this by signing up here)
pecl_http extension installed for HTTP networking

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:

pecl install pecl_http

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:

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

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

+1234567890,"👋 Welcome to {{your-business-name}}! Thanks for signing up. We are just a message away!"
+1234567890,"💐 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

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

Send text messages

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

<?php

require_once 'vendor/autoload.php';

use http\Client;
use http\Client\Request;
use http\Client\Response;
use League\Csv\Reader;

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

// Replace this with your Agent Connect API token
$token = 'ENTER API KEY HERE';

// Optionally specify the target WhatsApp device ID connected to Agent Connect
// you want to use for messages delivery (24 characters hexadecimal value)
$device = 'DEVICE ID GOES HERE';

$client = new Client();

// Read the Google Sheets CSV file and parse it as a CSV
$csv = Reader::createFromString(file_get_contents($google_sheets_csv_url));
$csv->setHeaderOffset(0);

// Iterate through the CSV rows
foreach ($csv->getRecords() as $record) {
    $phone = $record['phone'];
    $message = $record['message'];

    // Prepare the API request
    $request = new Request('POST', 'Agent Connect_API_URL');
    $request->setHeaders([
        'Authorization' => 'Bearer ' . $token,
        'Content-Type' => 'application/json',
    ]);
    $request->setBody(json_encode([
        'phone' => $phone,
        'body' => $message,
        'device' => $device,
    ]));

    // Send the API request and get the response
    $client->enqueue($request)->send();
    $response = $client->getResponse();

    // Check if the message was sent successfully
    if ($response->getResponseCode() == 200) {
        $responseBody = json_decode($response->getBody(), true);
        echo "Message sent to {$phone}. Message ID: {$responseBody['message_id']}\n";
    } else {
        echo "Failed to send message to {$phone}. Error: {$response->getBody()}\n";
    }
}

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.php to send multiple image media messages to different phone numbers loaded from a Google Sheets document.

In order 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 in order 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.php in your project directory and add the following code:

<?php

require_once 'vendor/autoload.php';

use http\Client;
use http\Client\Request;
use http\Client\Response;
use League\Csv\Reader;

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

// Set the download URL of the file to be sent
$file_url = 'https://picsum.photos/seed/picsum/600/500';

// Replace this with your API token
$token = 'ENTER API KEY 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 GOES HERE';

$base_url = 'https://api.agentconnect.chat/v1';
$url = $base_url . '/messages';
$files_url = $base_url . '/files';

function upload_file($url, $files_url, $token)
{
    $client = new Client();
    $request = new Request('POST', $files_url);
    $request->setHeaders([
        'Content-Type' => 'application/json',
        'Authorization' => $token
    ]);
    $request->setBody(json_encode(['url' => $url]));

    $client->enqueue($request)->send();
    $response = $client->getResponse();

    if ($response->getResponseCode() == 200) {
        $responseBody = json_decode($response->getBody(), true);
        return $responseBody['id'];
    } else {
        echo "Failed to upload file: " . $response->getBody() . "\n";
        return null;
    }
}

function normalize_phone($phone)
{
    return "+" . preg_replace('/\D/', '', $phone);
}

function send_message($phone, $message, $file, $url, $device, $token)
{
    $client = new Client();
    $request = new Request('POST', $url);
    $request->setHeaders([
        'Content-Type' => 'application/json',
        'Authorization' => $token
    ]);
    $request->setBody(json_encode([
        'phone' => $phone,
        'message' => trim($message),
        'device' => $device,
        'media' => ['file' => $file]
    ]));

    $client->enqueue($request)->send();
    $response = $client->getResponse();

    if ($response->getResponseCode() == 200) {
        echo "Message created: " . $phone . "\n";
    } else {
        echo "Failed to create message to {$phone}: " . $response->getBody() . "\n";
    }
}

function main($google_sheets_csv_url, $file_url, $url, $files_url, $token, $device)
{
    // Download Google Sheets CSV file
    $csv_data = file_get_contents($google_sheets_csv_url);

    // Parse the CSV data
    $csv = Reader::createFromString($csv_data);
    $csv->setHeaderOffset(0);

    // Upload file
    $file_id = upload_file($file_url, $files_url, $token);
    if (!$file_id) {
        echo "Failed to upload file: check the error\n";
        return;
    }

    // Process messages
    foreach ($csv->getRecords() as $record) {
        $phone = $record['phone'];
        $message = $record['message'];

        if (!$phone || !$message) {
            continue;
        }

        $number = normalize_phone($phone);
        if ($number && strlen($number) >= 8 && $message) {
            send_message($number, $message, $file_id, $url, $device, $token);
        }
    }
}

main($google_sheets_csv_url, $file_url, $url, $files_url, $token, $device);

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.php and send_media.php files, make sure you have replaced the Google Sheets CSV URL and your actual Agent Connect 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.

Replace the API token

In the send_messages.php file, make sure you have defined the API token of your actual Agent Connect account:

# Replace this with your Agent Connect API token
$token = 'ENTER API KEY HERE';

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

# Optionally specify the target WhatsApp device ID connected to Agent Connect
# you want to use for messages delivery (24 characters hexadecimal value)
$device = 'DEVICE ID GOES HERE';

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 in 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.php or send_media.php script:

php send_messages.php

Similarly, you can run the send_media.php script in order to send media messages:

php send_media.php

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

=> Message created: +1234567890
=> Message created: +1234567890
=> Message created: +1234567890

Note messages will be added to your number's message delivery queue and delivered asynchronously in 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 PHP and the Agent Connect API.

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

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