DEV Community

Cover image for Automated Airtime Disbursal using Google Forms, Python, Africas Talking Airtime Api and Heroku
Zoo Codes
Zoo Codes

Posted on • Updated on

Automated Airtime Disbursal using Google Forms, Python, Africas Talking Airtime Api and Heroku

My previous article on automating customer rewards using airtime garnered a lot of attention and reads.

This of course lead me to realize a good number of people are interested in the topic. This article will cover similar topics only with a different workflow. Quick life update, I recently joined the prestigious Moringa School to update my software development skills. Now due to Covid-19 pandemic we are mostly learning remotely, this necessitates an internet connection. The school provides airtime via a 3rd party company. However, I got an idea for a DIY cloud-based option hence this project.

This project will make use of:

  • Google Forms to collect student information(name, phone number).
  • Google Sheets to store and verify the data.
  • Python as our language of choice to get the required data, send requests to the airtime endpoint of Africas Talking Api
  • Heroku to host and schedule our script.

Prerequisites

To follow along this post and code the same features. You're going to need a few things:

  • 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.
      • Additionally, you will need to request them to enable airtime access for your account. Email their airtime team for further clarification

    Once you've got the above sorted :
    - Create a new directory and change into it.

              mkdir Automated-Airtime-Disbursal
              cd Automated-Airtime-Disbursal
    
    • 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, gspread libraries.
    • Save the installed libraries in a requirements.txt file
           python -m venv .
           source bin/activate
           pip install africastalking python-dotenv gspread  
           pip freeze > requirements.txt
    

Alternatively, check the finished code on Github

GitHub logo KenMwaura1 / Automated-Airtime-Disbursal

This is a simple python script to automate airtime disbursal using google forms, python and AfricasTalking

Forms For Everyone

To kickoff this process, lets first create a Google form. Visit this link
and create a new form. Example as shown below:
Alt Text

The next step is authenticate the python script, and our local development environment with Google. This enables us to access Google Form data from Google Drive by enabling the Google Drive Api in Google Cloud Platform
console.

To complete setting up our form:

  1. Go to the Google Form
  2. Responses Tab,
  3. Click the spreadsheet icon
  4. And create a new Destination Spreadsheet for our Google form.

Alt Text
The spreadsheet will be created and stored Google Drive. To integrate our Python environment and Google Drive. First we have to authenticate with Google.

Authentication For the Win!

Alt Text

Now to interact with the Google Drive Api we have to setup our account, enable Google Drive and Google Sheets Api and obtain a credentials.json file.

  1. Go to the Google Cloud Platform Console, click on New Project / Projects → New Project, on the GCP Toolbar.
    Alt Text

  2. Enter a name for our project and click Create.
    Alt Text

To access spreadsheets via the Google Sheets Api, authentication is required. There are two ways to go about it:

  • Service Account Mainly used to access spreadsheets on behalf of a bot.
  • OAuth Client ID - If you’d like to access spreadsheets on behalf of end users (including yourself).

Alt Text

Api access for our project

  1. Head to Google Developers Console and create a new project (or select the one you already have).
  2. In the box labeled “Search for APIs and Services”, search for “Google Drive API” and enable it. Alt Text
  3. In the box labeled “Search for APIs and Services”, search for “Google Sheets API” and enable it. Alt Text

Service Account Setup

For this project I used the Service Account, However feel free to go the Oauth route if you prefer.A service account is a special type of Google account intended to represent a non-human user that needs to authenticate and be authorized to access data in Google APIs.

Since it’s a separate account, by default it does not have access to any spreadsheet until you share it with this account. Just like any other Google account.

Here’s how to get one:

  1. Enable API Access for a Project (as shown above )if you haven’t done it yet.
  2. Go to “APIs & Services > Credentials” and choose “Create credentials > Service account key”. Alt Text
  3. Fill out the form. Alt Text
  4. Click “Create” and “Done”.
  5. Press “Manage service accounts” above Service Accounts.
  6. Press on ⋮ near recently created service account and select “Manage keys” and then click on “ADD KEY > Create new key”.
    Alt Text

  7. Select JSON key type and press “Create”.

You will automatically download a JSON file with credentials. It may look like this:

{
  "type": "service_account",
  "project_id": "api-project-XXX",
  "private_key_id": "2cd … ba4",
  "private_key": "-----BEGIN PRIVATE KEY-----\nNrDyLw … jINQh/9\n-----END PRIVATE KEY-----\n",
  "client_email": "473000000000-yoursisdifferent@developer.gserviceaccount.com",
  "client_id": "473 … hd.apps.googleusercontent.com",
  ...
}
Enter fullscreen mode Exit fullscreen mode

Place the downloaded credentials file in our currently working folder. In my case I renamed it to airtime-credentials.json. Go back to the Google Console, in our current project. Click on the credentials tab, copy the
email under service accounts.

Alternatively check the downloaded json file for value client_email

  1. Very important! Go to your spreadsheet and share it with a client_email from the step above. Just like you do with any other Google account. If you don’t do this, you’ll get a gspread.exceptions.SpreadsheetNotFound exception when trying to access this spreadsheet from your application or a script. Alt Text

Note: This secret json file and the Python File created below should be present in the same directory.

Data Extraction from Google Sheets

Alt Text

This is our google sheet that we will be extracting data from:

Alt Text
Take note of the name as well as the columns as they integral to data extraction.

After we've completed the steps above lets now get the data from our spreadsheets. Create a file to hold all of our code, import of our required libraries.

touch airtime_disbursal.py
Enter fullscreen mode Exit fullscreen mode
# airtime_disbursal.py 
import os
import africastalking as at
from dotenv import load_dotenv
import gspread
Enter fullscreen mode Exit fullscreen mode

Now create a variable using the gspread library, service_account method, and our downloaded json file to authenticate our request. Then we will create a function get_spreadsheet_data that accepts spreadsheet name and index of required worksheet as arguments. The function will query for the specified spreadsheet and worksheet then return all the values except the column headers.

def get_spreadsheet_data(sheet_name, worksheet_index):
    #make a request to Google specifying the spreadsheet required
    sheet = gc.open(sheet_name).get_worksheet(worksheet_index)
    # by specifying the index we remove the column headers
    return sheet.get_all_values()[1:]
Enter fullscreen mode Exit fullscreen mode

Inorder for us to call the above defined function we need to define a few variables as well as assign it to a variable
to make the resultant data easier to work with:

sheet_index = 0
airtime_sheet_name = 'Contact Information (Responses)'
sheet_data = get_spreadsheet_data(airtime_sheet_name, sheet_index)
print(sheet_data)
Enter fullscreen mode Exit fullscreen mode

Airtime for Everyone!

Alt Text

Let's create a .env file in our working folder to hold all of our environment variables that we dont want to expose to everyone. Enter the following changing the placeholders with the proper credentials.

touch .env
Enter fullscreen mode Exit fullscreen mode
# .env 
at_username=enter your at_username
at_api_key=enter your api_key
Enter fullscreen mode Exit fullscreen mode

Ensure you get your correct credentials from your Africas talking portal.

# Load our sensitive information using environment variables
load_dotenv()
# get the environment values from the .env file
at_username = os.getenv('at_username')
at_api_key = os.getenv('at_api_key')

# initialize africas talking using username and api key
at.initialize(at_username, at_api_key)
airtime = at.Airtime
account = at.Application
Enter fullscreen mode Exit fullscreen mode

The snippet above uses load_dotenv() function to load our sensitive data from environment variables. We then assign variables to each environment value. We then instantiate Africas Talking client by passing our api_key and at_username variables. Finally, we assign the Airtime and Application classes to variables.

def airtime_disbursal(number, airtime_amount: str, airtime_currency_code: str):
    print(account.fetch_application_data())

    try:
        response = airtime.send(phone_number=number, amount=amount, currency_code=currency_code)
        print(response)
    except Exception as e:
        print(f"Encountered an error while sending airtime. More error details below\n {e}")


Enter fullscreen mode Exit fullscreen mode

The snippet above creates a airtime_disbursal function that accepts 3 arguments: number (phone number of each recipient),
airtime_amount (amount of airtime to send to each recipient) and airtime_currency_code (3 digit currency code required by Africas Talking). We then proceed to print out our account balance using fetch_application_data() method. Finally we write a try-catch block to hold the logic for sending airtime. Here we pass our arguments as needed. Read the docs for more information.

# Set The 3-Letter ISO currency code and the amount
amount = "5"
currency_code = "KES"

# Unpack the list of values
for item in sheet_data:
    print(item[4])
    mobile_number = item[4]
    # for each number in the sheet send airtime top-up as specified.
    airtime_disbursal(mobile_number, amount, currency_code)

print(account.fetch_application_data())
Enter fullscreen mode Exit fullscreen mode

The code block above defines an amount variable and assigns a value of 5. This is ofcourse a demo, in a production environment, you will probably have variable amounts for different recipients. We then define our currency code as KES. This is important as it tells the API what currency to disburse the airtime in. We then add a for loop to unpack our sheet_data list. The last item in the list contains the required phone number. We proceed to assign it to a variable.
We then call our airtime_disbursal function on each value on the list. The function will run passing along a different phone number on each run, until the list is complete. We then query the balance of our account after the completion of the loop.

Deployment

Alt Text

Now for our solution to be automated, we need to deploy it to the cloud and have it run on scheduled intervals. There a lot of good cloud vendors we could use however Heroku takes the cake for its ease of deployment and free tier that will be enough for demo purposes. It is easy enough to choose any other cloud provider e,g. Digital Ocean, AWS, GCP etc.

After creating an account and logging in, I recommend you install the heroku cli for easier time during deploying. Now lets begin deployment: just do a heroku create --app automatic-airtime. If you go on your app dashboard you'll see your new app.
Alternatively use the browser to create the app.
Alt Text
We need to create a runtime.txt file to tell Heroku which version of python we want it run. I set mine to 3.9.2 to replicate my development environment.

  echo "python-3.9.2" > runtime.txt
Enter fullscreen mode Exit fullscreen mode

We also need to specify config vars that heroku will use during runtime. This similar how we've been
storing our credentials in a .env file. You could either set them via the heroku console in the browser or terminal using the Heroku cli. Make sure you change the values to your actual credentials.
Alt Text

  heroku config:set at_api_key=api_key_here
  heroku config:set at_username=Username_here
Enter fullscreen mode Exit fullscreen mode

We now need to initialize a git repo and push the code on Heroku:

  git init
  heroku git:remote -a heroku create --app automatic-airtime
  git add .
  git commit -am 'Deploy airtime disbursal script'
  git push heroku master
Enter fullscreen mode Exit fullscreen mode

Incase you get an error running the above commands, change your app name as its usually required to be unique.

Your app is now on Heroku, but it is not doing anything. Since this little script can't accept HTTP requests, going to automatic-airtime.herokuapp.com won't do anything. But that should not be a problem. To have this script running on as scheduled basis we need to use a Heroku add-on called "Advanced Scheduler.To install this add-on, click on the "Configure Add-ons" button on your app dashboard.

Then, on the search bar, look for Advanced Scheduler:
Alt Text

Click on the result, select the free plan and click on Submit Order Form.
Alt Text

If you go back to your App dashboard, you'll see the add-on:
Alt Text

Click on the "Advanced Scheduler" link to configure a job. Then click on "Create trigger".
Alt Text

Here enter a name for the trigger,set the timezone and for run command select python airtime_disbursal.py.
Alt Text

Set the state to "active", Type to "recurring" and select "Schedule helper" in the schedule section. In this case I set the unit of time to "weekly" for demo purposes. However, feel free to adjust as your needs dictate. For the interval its set for Every "Friday" at "04.00 pm"
Alt Text

While everything we used so far on Heroku is free, the Advanced Scheduler will run the job on the Free trial. Which allows for 3 triggers up to 100 runs which fits OK here from there it goes up to $15/month. Check further on pricing page.

Conclusion

We initially set out to build a DIY solution to scheduled airtime disbursal using set number of tools. For us
the workflow is Google Forms --> Google Sheets --> Python --> Africas Talking --> Heroku --> Recipient. Now this approach works well and as expected, however, it requires understanding the various parts and services needed thus not for everyone. As stated in the beginning of this article, there various 3rd party that plug and play options. Pick
the best option for your use case.

I hope that you liked this post and that you learned things reading it.I truly believe that this kind of project is one of the best ways to learn new tools and concepts.
If you have any question or comments. Let me know in the comments, or on Twitter.

Happy Coding!.

Top comments (4)

Collapse
 
amolo profile image
Amolo

Good stuff.
🇰🇪 to the world....👌🏾

Collapse
 
ken_mwaura1 profile image
Zoo Codes

Appreciate the support 🚀🚀🚀. Just read some of your work on FaunaDB. Awesome content. Definitely on my TODO list.

Collapse
 
hartley94 profile image
Martin Thuo

Great work.

Collapse
 
ken_mwaura1 profile image
Zoo Codes

Thank you for the support 🚀🚀