DEV Community

0xkoji
0xkoji

Posted on

Build a Birthday Bot with Twilio and Spreadsheet

#python #twilio #spreadsheet

What we will create is the following.

Alt Text

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

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

Create poetry project 

$ poetry new birthday_bot $ cd birthday_bot
Enter fullscreen mode Exit fullscreen mode

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

setup env for Google Spreadsheet

gspread

GitHub logo burnash / gspread

Google Sheets Python API

Google Spreadsheets Python API v4

main workflow GitHub licence GitHub downloads documentation PyPi download PyPi version python version

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

Requirements: Python 3.8+.

Basic Usage

  1. Create credentials in Google API Console

  2. Start using gspread

import gspread # First you need access to the Google API. Based on the route you # chose in Step 1, call either service_account(), oauth() or api_key(). 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
…
Enter fullscreen mode Exit fullscreen mode
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')
Enter fullscreen mode Exit fullscreen mode

run a script 

$ poetry run python app.py today is 03/10 sent a msg
Enter fullscreen mode Exit fullscreen mode

Top comments (0)