DEV Community

Cover image for Sending Bulk SMS using Africas Talking, Python and Excel
Zoo Codes
Zoo Codes

Posted on • Updated on

Sending Bulk SMS using Africas Talking, Python and Excel

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.

Alt Text

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.
  • An Africas Talking account.

    • 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

forthebadge made-with-python GPL license Open Source Love svg1

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:

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

  1. Clone the Repo

    git clone https://github.com/KenMwaura1/Africas-Talking-Multiple-SMS

  2. 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

Alt Text
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 .env file
  • 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 
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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!

Alt Text

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']
Enter fullscreen mode Exit fullscreen mode

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:

Alt Text

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

Alt Text
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()
Enter fullscreen mode Exit fullscreen mode

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)

Collapse
 
ronnkarani profile image
ronnkarani
raise KeyError("Worksheet {0} does not exist.".format(key))
Enter fullscreen mode Exit fullscreen mode

KeyError: 'Worksheet Sheet1 does not exist.'

got this error

Collapse
 
ronnkarani profile image
ronnkarani

my bad my sheet had a different name

Collapse
 
diksha0104 profile image
Diksha0104

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,

Collapse
 
ken_mwaura1 profile image
Zoo Codes

Seems your phone number isnt getting parsed correctly thus causing the error. Try printing out the number/ variable to confirm.