Build a Birthday Bot with Twilio and Spreadsheet
Tags: python, twilio, spreadsheet
Published: 2021-03-10 07:20:20

What we will create is the following.

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


Sent from your Twilio trial account <-- because I use the trial account(free)

`Hi Koji, Happy Birthday! Have a wonderful day.` is the message that is sent by a python script with Twilio API.


## How it works
Host a python script somewhere(like Heroku) and kick the script every 24 hours.
As the first step, the script checks Google Spreadsheet that has recipients(clients) list with their phone number and birthday. Of course, the spreadsheet can be a text file, a CSV file, an Excel file, or DB. This depends on how many clients are on the list and what kind of hosting service you will use for this. Additionally, how often a person/people will need to update the list and those people are familiar with the CLI tool or not.

In this case, I'm using Google Spreadsheet because this will be maintained by a non-tech person.

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

One more thing, I'm using Poetry (https://python-poetry.org/)

### Create poetry project
```zsh
$ poetry new birthday_bot
$ cd birthday_bot
```

### install packages
In this article, we use gspread, oauth2client, and twilio
The first 2 packages are for accessing Google Spreadsheet and the last one is for using Twilio API.

`gspread`
https://gspread.readthedocs.io/en/latest/index.html

`twilio`
https://pypi.org/project/twilio/

```zsh
$ poetry add gspread oauth2client twilio
```

### setup env for Google Spreadsheet

gspread
{% github burnash/gspread %}

Here is the guide to setup
https://gspread.readthedocs.io/en/latest/oauth2.html

If you don't want to use Google Spreadsheet to hold recipients' information. You can skip this step and add a function to read a text file/csv file.


### obtain Twilio API
We need to `account_id`, `auth_token`, and phone_number to send a message to a smartphone.

Twilio's help center page is very useful to get them.
https://support.twilio.com/hc/en-us/articles/223136027-Auth-Tokens-and-How-to-Change-Them

## code
`app.py`
```python
import gspread
import json
from oauth2client.service_account import ServiceAccountCredentials
import datetime
from twilio.rest import Client

# create client
account_id = 'Twilio_account_id'
auth_token = 'Twilio_auth_token'
phone_number = 'Twilio_phone_number'
client = Client(account_id, auth_token)

# connect google spreadsheet and return worksheet info
def connect_gspread(jsonf: str, key:str) -> gspread.models.Worksheet:
    #print('connect_gspread')
    scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
    credentials = ServiceAccountCredentials.from_json_keyfile_name(jsonf, scope)
    gc = gspread.authorize(credentials)
    SPREADSHEET_KEY = key
    worksheet = gc.open_by_key(SPREADSHEET_KEY).sheet1
    # print(type(worksheet))
    return worksheet

# send a message to a recipient_number
def send_msg(name: str, recipient_number: str):
    # add recipient name to the message
    message = client.messages.create(
        body = 'Hi {}, Happy Birthday! Have a wonderful day.'.format(name),
        from_ = phone_number,
        # from_ = 'recipient_number',
        to = recipient_number
    )


jsonf = './integral.json'
spread_sheet_key = 'spreadsheet_key'
ws = connect_gspread(jsonf,spread_sheet_key)

# get cell value from worksheet(ws)
names = ws.col_values(1)
birthdays = ws.col_values(2)
numbers = ws.col_values(3)

today = datetime.datetime.now()
today = today.strftime("%m/%d")
print('today is {}'.format(today))

if birthdays[1] == today:
    send_msg(names[1], numbers[1])
    print('sent a msg')
else:
    print('no target')
```

### run a script
```zsh
$ poetry run python app.py
today is 03/10
sent a msg
```