Build a Birthday Bot with Twilio and Spreadsheet

0xkoji - Mar 10 '21 -

- Dev Community

What we will create is the following.

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.

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

Create poetry project

$ 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/

$ poetry add gspread oauth2client twilio

setup env for Google Spreadsheet

gspread

burnash / gspread

Google Sheets Python API

Google Spreadsheets Python API v4

Name: Build a Birthday Bot with Twilio and Spreadsheet
Rating: 1.3 (3065 reviews)
Author: 0xkoji

Simple interface for working with Google Sheets.

Features:

Open a spreadsheet by title, key or URL.
Read, write, and format cell ranges.
Sharing and access control.
Batching updates.

Installation

pip install gspread

Requirements: Python 3.8+.

Basic Usage

Create credentials in Google API Console
Start using gspread

import gspread
gc = gspread.service_account()

# Open a sheet from a spreadsheet in one go
wks = gc.open("Where is the money Lebowski?").sheet1

# Update a range of cells using the top left corner address
wks.update([[1, 2], [3, 4]], "A1")

# Or update a single cell
wks.update_acell("B42", "it's down there somewhere, let me take another look.")

# Format the header
wks.format('A1:B1', {'textFormat': {'bold': True

…

View on GitHub

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

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

$ poetry run python app.py
today is 03/10
sent a msg

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .