Introduction
In this tutorial, you'll learn how to send messages to phone numbers stored in a Google Sheets CSV file using Node.js, the axios library, and the Wassenger API. We will download the CSV file from Google Sheets, read the phone numbers and messages, and then send them using the Wassenger API.
Prerequisites
- Basic understanding of JavaScript and Node.js
- Node.js 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)
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:
npm init -y
npm install --save axios csv-parse
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.js in your project directory and add the following code:
const axios = require('axios')
const { parse } = require('csv-parse/sync')
// 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
const googleSheetsCsvUrl = 'ENTER_GOOGLE_SHEETS_CSV_URL_HERE'
// Replace this with your Wassenger API token
// Get your API token here: https://app.wassenger.com/apikeys
const token = 'ENTER API KEY HERE'
// Optionally specify the target WhatsApp device ID connected to Wassenger
// you want to use for messages delivery (24 characters hexadecimal value)
const device = 'DEVICE ID GOES HERE'
// Define the headers for the API request
const headers = {
'Content-Type': 'application/json',
'Authorization': `${token}`
}
// Define the URL for the Wassenger API
const url = 'https://api.wassenger.com/v1/messages'
const normalizePhone = phone => {
return `+${phone.replace(/\D/g, '')}`
}
const sendMessage = async (phone, message) => {
const body = {
phone,
message: message.trim(),
device
}
try {
await axios.post(url, body, { headers })
console.log(`==> Message created: ${phone}`)
} catch (error) {
console.error('Failed to create message to ${phone}:', error.response ? error.response.data : error)
}
}
const main = async () => {
try {
console.log('=> Downloading Google Sheets CSV file...')
const { data } = await axios.get(googleSheetsCsvUrl);
const records = parse(data, { columns: false, skip_empty_lines: true })
console.log('=> Processing messages...')
for (const [phone, message] of records) {
if (!phone || !message) {
continue
}
const number = normalizePhone(phone)
if (number && number.length >= 8 && message) {
await sendMessage(number, message)
}
}
} catch (err) {
console.error('Error:', err)
}
}
main()
Send media messages
In this example, we will create a different program called send_media.js in order 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 Wassenger 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
Create a new file named send_media.js in your project directory and add the following code:
const axios = require('axios')
const { parse } = require('csv-parse/sync')
// 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
const googleSheetsCsvUrl = '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...)
const fileUrl = 'https://picsum.photos/seed/picsum/600/500'
// Replace this with your Wassenger API token
// Get your API token here: https://app.wassenger.com/apikeys
const token = 'ENTER API KEY HERE'
// Optionally specify the target WhatsApp device ID connected to Wassenger
// you want to use for messages delivery (24 characters hexadecimal value)
const device = 'DEVICE ID GOES HERE'
// Define the headers for the API request
const headers = {
'Content-Type': 'application/json',
'Authorization': `${token}`
}
// Define URLs for the Wassenger API
const baseUrl = 'https://api.wassenger.com/v1'
const url = `${baseUrl}/messages`
const filesUrl = `${baseUrl}/files`
const uploadFile = async (url) => {
const body = { url }
try {
const response = await axios.post(filesUrl, body, { headers })
return response.data.id
} catch (error) {
// If the file already exists, obtain the file ID from the response
if (error.response && error.response.status === 409 && error.response.data) {
return error.response.data.meta.file
}
console.error('Failed to upload file:', error.response ? error.response.data : error)
}
}
const normalizePhone = phone => {
return `+${phone.replace(/\D/g, '')}`
}
const sendMessage = async (phone, message, file) => {
const body = {
phone,
message: message.trim(),
device,
media: { file }
}
try {
await axios.post(url, body, { headers })
console.log(`==> Message created: ${phone}`)
} catch (error) {
console.error('Failed to create message to ${phone}:', error.response ? error.response.data : error)
}
}
const main = async () => {
try {
if (!fileUrl) {
throw new Error('Missing required fileUrl')
}
console.log('=> Downloading Google Sheets CSV file...')
const { data } = await axios.get(googleSheetsCsvUrl);
const records = parse(data, { columns: false, skip_empty_lines: true })
console.log('=> Uploading file...')
const file = await uploadFile(fileUrl)
if (!file) {
throw new Error('Failed to upload file: check the error')
}
console.log('=> Processing messages...')
for (const [phone, message] of records) {
if (!phone || !message) {
continue
}
const number = normalizePhone(phone)
if (number && number.length >= 8 && message) {
await sendMessage(number, message, file)
}
}
} catch (err) {
console.error('Error:', err)
}
}
main()
Replace the Google Sheets URL to export as CSV
In the send_messages.js and send_media.js 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
const googleSheetsCsvUrl = '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.js file, make sure you have defined the API token of your actual Wassenger account:
// Replace this with your Wassenger API token
const token = 'ENTER API KEY 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 messages 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)
const device = 'DEVICE ID GOES HERE'
Run the program
Run the program in the cloud
Run the program on your computer
Open a terminal in your project directory and run the following command to execute the send_messages.js script:
node send_messages.js
Similarly, you can run the send_media.js script in order to send media messages:
node send_media.js
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.
Conclusion
In this tutorial, you learned how to send messages to phone numbers stored in a Google Sheets CSV file using Node.js, the axios library, 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 own software as needed.