After my previous article, I got requested to do a follow-up tutorial using an Excel spreadsheet to send bulk messages.
For this one the background story is a simple one. Assuming we are in university/college, and we needed to send our class members, a message to inform/remind them about class. Naturally there are multiple options including Whatsapp/Telegram, calls, email etc. However, we can't assume they all have smartphones, they will be online in time to
receive the message. Furthermore,calling is not only tedious but highly inefficient and not scalable.
Another option is to text the class manually or create a group chat. Depending on the number of people, the frequency of texts and whether you need to customize the message. It may prove more work that it should be.
In this article we'll take an spreadsheet of names and numbers, craft a custom message for each and send a custom message to each one. we'll add checks in case the message fails to send.
This method is not only highly scalable but efficient and flexible as you could potentially have multiple scripts for different classes or customize one script for the specific need.
Preparation
To effectively follow along with this post and subsequent code, you will need the following prerequisites.
- Python and pip (I am currently using 3.9.2 ) Any version above 3.5 should work.
-
-
Api Key and username from your account. Create an app and take note of the api key.
As per the previous article :- Create a new directory and change into it.
mkdir at-project cd at-project- Create a new virtual environment for the project or activate the previous one.
- Using python package manager(pip) install africastalking python sdk, python-dotenv library and openpyxl library.
- Save the installed libraries in a requirements.txt file
python -m venv . source bin/activate pip install africastalking python-dotenv openpyxl pip freeze > requirements.txt
-
There are few alternative libraries for reading and writing excel files: Pandas,
xlrd , openpyxl among others. In the end I settled for openpyxl as I had the most experience
using it and it had support for .xlsx files.
Got all that? Let's send some texts.
Alternatively jump to the completed code on
Africas-Talking-Multiple-SMS
This is a simple script utilizing python and the Africas Talking API to send bulk texts from a csv and an Excel sheet.spread
Read the corresponding articles:
- Sending Bulk SMS using Africas Talking, Python and CSV
- Sending Bulk SMS using Africas Talking, Python and Excel
Points to note
This project is under current development.
Inorder to make use of it. Edit the names_cell_range and number_cell_range according to the layout of your spreadsheet
Change the name of the workbook referenced in load_workbook() function to your own
This project also uses .env file to store the API key and username Both can be obtained by signing up/logging into Africas Talking
Executing the script
-
Clone the Repo
git clone https://github.com/KenMwaura1/Africas-Talking-Multiple-SMS -
Create a virtual environment (venv)
python venv venv
. Activate the virtual environment
`source ./scripts/activate`
If you are using pyenv
2a. pyenv virtualenv at-project
2b. pyenv activate at-project
3. Ensure the spreadsheet…
The first alert
It all starts with a single message to your first classmate, this is acts an alpha test for your notification service.
I am using the python-dotenv library to retrieve the required credentials for environment variables.
This is adhering to best practice in regard to sensitive information.
- Create a
.envfile - Enter the following replacing the placeholders with the proper credentials.
# Both can be obtained from your account console on Africas Talking
username=Username-here
api_key=apikey-here
Create our main file multiple-sms-excel.py.
Import the required libraries.
# multiple-sms-excel.py
import os
import africastalking as at
from dotenv import load_dotenv
Now lets get our credentials, initialize the africastalking client and send our first test sms.
Make sure your number here, and throughout this post, is in E.164 format
# multiple-sms-excel.py
load_dotenv()
username = os.getenv("username")
api_key = os.getenv("api_key")
at.initialize(username, api_key)
sms = at.SMS
message = f"hey {name} from python using africas talking API"
# add your number below
number = "+2547XXXXXX"
sms.send(response = sms.send(message, [number]))
print(response)
This creates a message and sends it . Test the code by running python multiple-sms-excel.py
You should see the atxid and receive your first message!
Multi Notifications
Now that your test went well, let's update the existing file to send messages to all of them.
Add the following code
# multiple-sms-excel.py
import os
import africastalking as at
from openpyxl import load_workbook
from dotenv import load_dotenv
load_dotenv()
username = os.getenv("username")
api_key = os.getenv("api_key")
at.initialize(username, api_key)
sms = at.SMS
wb = load_workbook('sample.xlsx')
print(wb.sheetnames)
sheet1 = wb['Sheet1']
names_cell_range = sheet1['B2:B4']
number_cell_range = sheet1['C2:C4']
First we import the load_workbook function from the openpyxl module. This allows us
to read an existing workbook. We proceed to assign the variable wb to active workbook.
My spreadsheet is named sample.xlsx pictured below:
However, edit according to your specific use case. We print all the worksheets names in our workbook. After we get the appropriate worksheet, we assign a variable for future reference.
Based on the layout of the worksheet we identify the range of cells which we want.
We assign variables names_cell_range and number_cell_range to the specific range.
99 problems but class ain't one
You've finally got the required details now we send the custom notification/reminder.
Let's create a function send_messages() to hold all of our message logic. This step is optional, but it makes easier in case we want to import the logic to another file or reference it in the code again.
# multiple-sms-excel.py
def send_messages():
for row in sheet1.iter_rows(values_only=True):
name = row[1]
number = f"+254{row[2]}"
lesson = row[3]
lesson_date = "Friday 12 March at 8.00 am "
print(name,number)
message = f"hey {name} Kindly note {lesson} lecture is scheduled on {lesson_date}"
try:
response = sms.send(message, [number])
print(response)
except Exception as e:
print(f"Uh oh we have a problem: {e}")
send_messages()
I'll explain what the above block of code does.
The openpyxl library has an iter_rows() method that we use to iterate over each row in the spreadsheet. We further pass on the values_only argument to ensure we only get the value of each row.
The for loop returns 3 values from out Excel file, we only need name and number. We proceed to assign the values to their aptly named variables. I hard-coded the value of the date for the lecture. However, its just easy enough to calculate using the inbuilt datetime module. We construct a custom message using f strings to interpolate the values we need. We go ahead and add a try-catch block which will come in handy to notify us of in case we run into problem when sending the messages. Lastly we call our function send_messages().
Now we can finally run python multiple-sms-excel.py and watch the terminal output. You should receive a custom message if everything went well. This was just a sample use case for bulk sms. You could just easily adapt it to fit a variety of situations.
If you have any question or comments. Let me know in the comments, or on Twitter
Buy me a coffee
Top comments (4)
KeyError: 'Worksheet Sheet1 does not exist.'
got this error
my bad my sheet had a different name
I am making a Bulk Sms project and I am using Africas talking API in it but I am getting this error
"Encountered an error while sending: Invalid phone number: 1"
can you help me to fix it,
Seems your phone number isnt getting parsed correctly thus causing the error. Try printing out the number/ variable to confirm.