Automated Airtime Disbursal using Google Forms, Python, Africas Talking Airtime Api and Heroku
Tags: python, tutorial, googlecloud, africastalking
Published: 2021-07-03 14:16:22


My previous [article](https://dev.to/ken_mwaura1/automate-customer-rewards-using-python-postgresql-and-africas-talking-9dj) on automating customer rewards using airtime garnered a lot of attention and reads.

{% post https://dev.to/ken_mwaura1/automate-customer-rewards-using-python-postgresql-and-africas-talking-9dj %}

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](https://moringaschool.com/) 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](https://account.africastalking.com/auth/register/).
    - 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.

      ```shell
              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

      ```shell
           python -m venv .
           source bin/activate
           pip install africastalking python-dotenv gspread  
           pip freeze > requirements.txt
      ```

Alternatively, check the finished code on [Github](https://github.com/KenMwaura1/customer-rewards)

{% github KenMwaura1/Automated-Airtime-Disbursal no-readme %}

## Forms For Everyone
To kickoff this process, lets first create a Google form. Visit [this link](https://docs.google.com/forms/)
and create a new form. Example as shown below:
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/n18a0qh1tk6lyrt8d3wd.png)

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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/85jwr37ufg4d2i5fdmht.png)
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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ngrieg0krb8up2c083hd.jpg)
    
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](https://console.cloud.google.com/), click on New Project / Projects → New Project, on the GCP Toolbar.
   ![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bqiyc3q2q6qmj2ngeojq.png)

2. Enter a name for our project and click Create.
   ![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/l9tr5gcl8junjtnnrw6s.png)

To access spreadsheets via the Google Sheets Api, authentication is required. There are two ways to go about it:
- [Service Account](https://docs.gspread.org/en/latest/oauth2.html#service-account) Mainly used to access spreadsheets on behalf of a bot.
- [OAuth Client ID](https://docs.gspread.org/en/latest/oauth2.html#oauth-client-id) - If you’d like to access spreadsheets on behalf of end users (including yourself).

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y4z2zc0qvchhf0piwazv.png)

### Api access for our project
1. Head to [Google Developers Console](https://console.cloud.google.com/) 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8mja2wizj11s4kglp66p.png)
3. In the box labeled “Search for APIs and Services”, search for “Google Sheets API” and enable it.
   ![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/a7pmnqyrrtg42tvt1oc7.png)

### 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/b5yfn1dct672uotue7ab.png)
3. Fill out the form.
   ![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pex23ztkrfr5xh9ot8nc.png)
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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kpnfspzeco2erggv57gq.png)

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

You will automatically download a JSON file with credentials. It may look like this:
```json
{
  "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",
  ...
}
```
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`

8. 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5o3ez7anjr9sbas8b35f.png)

**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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tuyfmewszhwdkehhpa72.jpg)

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

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7zsx4mtaeud6b5aez1at.png)
_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.
```shell
touch airtime_disbursal.py
```

```python
# airtime_disbursal.py 
import os
import africastalking as at
from dotenv import load_dotenv
import gspread
```
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.

```python
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:]
```
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:

```python
sheet_index = 0
airtime_sheet_name = 'Contact Information (Responses)'
sheet_data = get_spreadsheet_data(airtime_sheet_name, sheet_index)
print(sheet_data)
```
## Airtime for Everyone!
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8r2p2jwf8zjgu1tz6juj.jpg)

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.

```shell
touch .env
```

```
# .env 
at_username=enter your at_username
at_api_key=enter your api_key
```
Ensure you get your correct credentials from your Africas talking portal.
```python
# 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
```
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.
```python
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}")


```
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](https://developers.africastalking.com/docs/airtime/overview) for more information.
```python
# 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())
```
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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/w7cp4vsaawl9quarvxzi.jpg)

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](https://devcenter.heroku.com/articles/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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yxaq0wrdfum6c6ax8utd.png)
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.
```shell
  echo "python-3.9.2" > runtime.txt
```
We also need to specify [config vars](https://devcenter.heroku.com/articles/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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/equbszltrsdq2n77hy75.png)

```shell
  heroku config:set at_api_key=api_key_here
  heroku config:set at_username=Username_here
```
We now need to initialize a git repo and push the code on Heroku:
```shell
  git init
  heroku git:remote -a heroku create --app automatic-airtime
  git add .
  git commit -am 'Deploy airtime disbursal script'
  git push heroku master
```
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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hygsw7m9c79gajiu2jrj.png)

Click on the result, select the free plan and click on Submit Order Form.
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/k4studsvsvpu6zjj4zbc.png)

If you go back to your App dashboard, you'll see the add-on:
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ylhs2km83a6k4zv6wggb.png)

Click on the "Advanced Scheduler" link to configure a job. Then click on "Create trigger".
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1rswwlwjm10fvytchjlw.png)

Here enter a name for the trigger,set the timezone and for run command select python airtime_disbursal.py.
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5jhk636vwo4ufkvwuqx6.png)


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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uylfgvmm4i1k3jiz41zc.png)

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](https://elements.heroku.com/addons/advanced-scheduler#pricing).

## 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](https://twitter.com/Ken_Mwaura1).

Happy Coding!.