Short code which works with Twilio to record
""" | |
ADD THE FOLLOWING LINES TO YOUR REQUIREMENTS.TXT: | |
flask | |
twilio | |
pytz | |
""" | |
# When running in Google Cloud - you must set the function to activate sms() | |
# Code is in cloud function - requires TXT and environment variable referencing sheet ID (part of Google Sheets URL after domain but before parameter) | |
# Requires service account for this project to have ALL sheets shared with it (when creating new sheet, make sure share to same users) | |
# First time using there will be error if we haven't actived Sheets API (should be a one-time thing for the account) | |
# Sheets must stay in same format - looks up column and row numbers, not headings | |
# To avoid accidentally clashing with itself the code will first mark each sheet as "in use" then do something, then mark it as free. If there are persistent errors - check the "in use" tab to see if something's gone wrong and it's marked as in use | |
# Setting testing number so we can keep track in log statements | |
global test_num | |
test_num = "79" | |
print ("Test:{}".format(test_num)) | |
# Flask to handle the inbound request | |
from flask import request, jsonify | |
print ("Imported request") | |
# To parse the inbound data | |
import json | |
print ("Imported json") | |
# To get the sheet environment variable we set | |
import os | |
print ("Imported os") | |
# So we can add data to lists without overwriting old lists | |
import copy | |
print ("Imported copy") | |
# To check for menu item matches | |
import re | |
print ("Imported regex") | |
# To log orders at the correct time and check time windows | |
from datetime import datetime, timedelta | |
print ("Imported datetime") | |
import pytz | |
print ("Imported pytz") | |
# To wait to use the GSheet if it's already in use | |
import time | |
print ("Imported time") | |
# So we can read and write to GSheets | |
# This relies on the Service Account we are using having been | |
# invited to edit the appropriate sheets | |
print ("Trying to authorise with GSheets") | |
from apiclient import discovery | |
sheets = discovery.build('sheets', 'v4') | |
print ("Authenticated") | |
def check_if_in_use(step_off, business_url): | |
script_using_sheet_range = 'Script using sheet!A2:B2' | |
# Opening up the tab which records whether the script is in use | |
script_using_sheet = sheets.spreadsheets().values().get( | |
spreadsheetId = business_url, | |
range = script_using_sheet_range).execute() | |
# Getting the notes we've made about whether the script is in use | |
in_use_details = script_using_sheet.get('values', []) | |
print ("In use_details: ",in_use_details) | |
if len(in_use_details)< 1: | |
step_off = False | |
else: | |
# The first row of column B is True if the script is using the sheet | |
# false if it isn't using the sheet | |
is_script_in_use = in_use_details[0][0] | |
# Based on Sheets formatting it could be a few different things so | |
# we'll standardise that first | |
if is_script_in_use == True or is_script_in_use == "True" or is_script_in_use == "TRUE": | |
# There is the possibility that something broke in the past | |
# so let's also check if the sheet has been in-use for a reasonable time period | |
when_was_marked_in_use = in_use_details[0][1] | |
last_marked_timestamp = datetime.strptime(when_was_marked_in_use, "%Y %b %d - %H:%M") | |
# If the last user started using the sheet more recently than five minutes ago | |
actual_time_new_york = pytz.timezone('US/Eastern') | |
if last_marked_timestamp > datetime.now(actual_time_new_york) - timedelta(minutes = 10): | |
print ("Last marked timestamp {} is within the last ten minutes".format(last_marked_timestamp)) | |
# We assume it's still in use | |
step_off = True | |
else: | |
print ("Last marked timestamp {} is not within the last ten minutes".format(last_marked_timestamp)) | |
# We assume something went wrong and we should go ahead | |
step_off = False | |
else: | |
# If script_in_use isn't true we don't need to step off | |
step_off = False | |
return step_off | |
def its_mine(business_url, | |
in_use = True): | |
# Marking the sheet as in-use or not in-use to minimise the chances of | |
# different instances of this script accidentally overwriting each other | |
# Get current time and put into appropriate format | |
actual_time_new_york = pytz.timezone('US/Eastern') | |
now = datetime.now(actual_time_new_york) | |
current_time = str(now.strftime("%Y %b %d - %H:%M")) | |
print("Current Time =", current_time) | |
# Setting range to update | |
script_using_sheet_range = 'Script using sheet!A2:B2' | |
print ("Setting in-use to {}".format(in_use)) | |
# Set the values to put into those cells | |
body = { | |
"majorDimension": "ROWS", | |
"values": [ | |
[in_use, current_time] | |
] | |
} | |
# Update the cells using the API (same one we authenticated in the import phase) | |
result = sheets.spreadsheets().values().update( | |
spreadsheetId = business_url, | |
range = script_using_sheet_range, | |
body = body, | |
valueInputOption = 'USER_ENTERED' | |
).execute() | |
print(result) | |
return current_time | |
def you_can_have_it_now(business_url, owned_time): | |
# First check the values in the sheet | |
# We assume something has gone wrong until we're proven otherwise | |
all_good = False | |
script_using_sheet_range = 'Script using sheet!A2:B2' | |
# Opening up the tab which records whether the script is in use | |
script_using_sheet = sheets.spreadsheets().values().get( | |
spreadsheetId = business_url, | |
range = script_using_sheet_range).execute() | |
# Getting the notes we've made about whether the script is in use | |
in_use_details = script_using_sheet.get('values', []) | |
print ("In use_details: ",in_use_details) | |
# false if it isn't using the sheet | |
is_script_in_use = in_use_details[0][0] | |
# Based on Sheets formatting it could be a few different things so | |
# we'll standardise that first | |
if is_script_in_use == True or is_script_in_use == "True" or is_script_in_use == "TRUE": | |
print ("Correct - script in use") | |
# This is what we expect, so all good | |
# Check the marked_in_use timestamp matches the one we expect | |
when_was_marked_in_use = in_use_details[0][1] | |
print ("checking if {} matches {}".format(str(when_was_marked_in_use), str(owned_time))) | |
if str(when_was_marked_in_use) == str(owned_time): | |
print ("Matches owned time") | |
all_good = True | |
else: | |
print ("Trying backup window") | |
marked_in_use_timestamp = datetime.strptime(when_was_marked_in_use, "%Y %b %d - %H:%M") | |
owned_time_timestamp = datetime.strptime(owned_time, "%Y %b %d - %H:%M:%S") | |
if marked_in_use_timestamp > owned_time_timestamp-timedelta(minutes = 1) and marked_in_use_timestamp < owned_time_timestamp+timedelta(minutes = 1): | |
print ("Within window") | |
all_good = True | |
else: | |
print ("Not within window") | |
all_good = False | |
if all_good == False: | |
# Something has gone wrong - we shouldn't go any further | |
print ("Issue - not all good") | |
return 500 | |
else: | |
# We can go ahead on our merry way | |
its_mine(business_url, | |
in_use = False) | |
return | |
# Marking the sheet as in-use so the script doesn't accidentally confuse itself | |
def im_using_it(business_url): | |
# We start by assuming we can use the sheet | |
step_off = False | |
# Run function to see if we should step_off | |
step_off = check_if_in_use( | |
step_off = step_off, | |
business_url = business_url) | |
number_of_checks = 0 | |
# Attempting a few times to wait and come back | |
while step_off == True and number_of_checks < 120: | |
time.sleep(2) | |
number_of_checks += 1 | |
step_off = check_if_in_use(step_off, business_url) | |
# If we try every two seconds for two minutes and never | |
# get access to the sheet - just return 500 because something | |
# has probably gone wrong and we don't want to keep trying forever | |
if step_off == True: | |
return 500 | |
else: | |
# We can mark the sheet as step off | |
current_time = its_mine(business_url, | |
in_use = True) | |
return current_time | |
# If the user has just started a new interaction | |
def starting(sent_number): | |
print ("Getting available menu") | |
# Get the lookup spreadsheet to check from our env. variables | |
SPREADSHEETID = os.getenv('spreadsheet_id') | |
# Extract all the relevant rows, this is a json block | |
result = sheets.spreadsheets().values().get(spreadsheetId=SPREADSHEETID, | |
range='number_lookup!A:E').execute() | |
# Extract the values from the block (this will be a list of lists) | |
values = result.get('values', []) | |
print ("Contacted number: {}".format(sent_number)) | |
# Get the relevant data for just the Twilio number that has been | |
# contacted. This allows us to have multiple Twilio numbers using the | |
# same flow but with business-specific details for each | |
number_val = [val for val in values if val[0] == sent_number][0] | |
business_name = number_val[1] | |
business_url = number_val[3] | |
business_phone_number = number_val[4] | |
print (""" | |
Twilio number: {} | |
Business name: {} | |
Business url: {} | |
Business number: {} | |
""".format(sent_number, business_name, business_url, business_phone_number | |
)) | |
# Using the looked up values to get the dishes the restaurant has | |
# said are available - same process as when we got the values from | |
# the lookup table | |
available_dishes = sheets.spreadsheets().values().get(spreadsheetId=business_url, | |
range='Available dishes!A:A').execute() | |
available_dishes_packed = available_dishes.get('values', []) | |
print ("Dishes list: ",available_dishes) | |
available_dishes = [dish[0].strip(' ') for dish in available_dishes_packed] | |
# Creating a list of dishes to send to the user | |
dishes_joined = "\n".join(available_dishes) | |
# Getting example dishes so we can tell users what to order | |
dish_example_1 = available_dishes[0] | |
dish_example_2 = available_dishes[1] | |
# Getting all the ways the business has said users might modify | |
# a dish. For example, it could be small, medium, large, no-salt etc. | |
additions = sheets.spreadsheets().values().get(spreadsheetId=business_url, | |
range='Additions!A:A').execute() | |
additions_packed = additions.get('values', []) | |
# Extracting each of the listed additions | |
additions = [addition[0].strip(' ') for addition in additions_packed] | |
print ("Additions: ",additions) | |
# Creating a list of additions which can be sensibly used in a message | |
all_bus_last_addition = additions[:-1] | |
additions_joined = ", ".join(all_bus_last_addition) | |
additions_joined = additions_joined+" or "+additions[-1] | |
# Getting addition examples to send to user | |
addition_example_1 = additions[0] | |
addition_example_2 = additions[1] | |
# Creating a regex of menu items/additions so we can try to identify | |
# when users are ordering something from the menu (this is VERY rough | |
# but works as a slightly more flexible check) | |
menu_plus_additions = available_dishes + additions | |
menu_regex = ".*("+"|".join(menu_plus_additions)+").*" | |
# Retrieving wait time from the sheet | |
wait_times = sheets.spreadsheets().values().get(spreadsheetId=business_url, | |
range='Wait time!A1').execute() | |
wait_times_packed = wait_times.get('values', []) | |
wait_time = str(wait_times_packed[0][0]) | |
# Constructing the response to return - this will be used by the Twilio flow | |
string_to_return = """Thanks for contacting {business_name}! | |
The current wait time is {wait_time} | |
You can choose any of the below options and you can have your meal {additions_joined}. | |
{available_dishes}""".format( | |
business_name = business_name, | |
wait_time = wait_time, | |
additions_joined = additions_joined, | |
available_dishes = dishes_joined) | |
# Constructing the JSON response which Twilio will grab values from | |
json_to_return = {"response":string_to_return, | |
"wait_time":wait_time, | |
"url":business_url, | |
"name":business_name, | |
"number":business_phone_number, | |
"dish_1":dish_example_1, | |
"dish_2":dish_example_2, | |
"addition_1":addition_example_1, | |
"addition_2":addition_example_2, | |
"dishes_string":dishes_joined, | |
"additions_string": additions_joined, | |
"menu_regex": menu_regex, | |
"menu":available_dishes, | |
"additions":additions} | |
return json_to_return | |
# If the user has given us a choice and we want to wake up the API | |
def waking(business_url): | |
# Set the range of cells to update | |
range_name = "Script using sheet!A1:B1" | |
# Set the values to put into those cells | |
body = { | |
"majorDimension": "ROWS", | |
"values": [ | |
["Script using sheet","Time set"] | |
] | |
} | |
# Update the cells using the API (same one we authenticated in the import phase) | |
result = sheets.spreadsheets().values().update( | |
spreadsheetId=business_url, | |
range=range_name, | |
body=body, | |
valueInputOption='USER_ENTERED' | |
).execute() | |
print(result) | |
# Return 200, we're not really doing anything here | |
return 200 | |
# If the user has responded to our message offering the menu | |
def chosen(sent_message, | |
menu_regex, | |
business_url, | |
from_number, | |
from_name): | |
# We're going to return data in json - this is just | |
# creating the empty object that we'll fill | |
json_to_return = {} | |
# Printing the received message and regex we'll check it | |
# against as a debugging step | |
print ("Received message is: ", sent_message) | |
print ("Menu regex is: ", menu_regex) | |
# Check if message matches anything on our menu | |
print ("checking for menu match") | |
menu_match = re.search(r""+menu_regex.lower(), sent_message.lower()) | |
if not menu_match: | |
print ("""order: {} didn't match menu: {}""".format(sent_message.lower(), menu_regex.lower())) | |
# If our regex check finds a match (in other words - if we | |
# think the user has requested something on the menu) | |
if (menu_match): | |
# Save a variable saying we think the user ordered something | |
json_to_return["order_found"] = True | |
# Check if the sheet is already in use. We don't need this | |
# For functions where we're only reading or updating existing | |
# Rows - we just need it for this one because we could | |
# accidentally write to the same row twice | |
current_time = im_using_it(business_url) | |
if current_time == 500: | |
return 500 | |
# Get all of the existing orders | |
orders = sheets.spreadsheets().values().get(spreadsheetId=business_url, range='Orders!A:E').execute() | |
orders_packed = orders.get('values', []) | |
# Find the number of existing orders so we don't accidentally overwrite one | |
# It appears that, if there is an empty row half way along our list, the API | |
# returns it as an empty list item, rather than just missing that row out of the | |
# response - that means that even if there are empty rows in the sheet we hopefully | |
# still don't overwrite the end of our existing list | |
num_orders = len(orders_packed) | |
row_num = num_orders+1 | |
print ("Row number is: {}".format(str(row_num))) | |
# Get current time so we can include that with our saved order | |
actual_time_new_york = pytz.timezone('US/Eastern') | |
now = datetime.now(actual_time_new_york) | |
current_time = str(now.strftime("%Y %b %d - %H:%M:%S")) | |
print("Current Time =", current_time) | |
# Constructing an order id based on a non-identifiable chunk of the phone number | |
# the row number we're updating and the current time (very unlikely those chunks | |
# will ever match) | |
order_id = "_"+str(from_number[2:5])+"_"+str(row_num)+"_"+str(current_time) | |
# Set the range of cells to update | |
range_name = "Orders!A"+str(row_num)+":F"+str(row_num) | |
# Set the values to put into those cells | |
body = { | |
"majorDimension": "ROWS", | |
"values": [ | |
[order_id, current_time, from_name, from_number, sent_message, "False"] | |
] | |
} | |
you_can_have_it_now(business_url, | |
owned_time = current_time) | |
# Update the cells using the API (same one we authenticated in the import phase) | |
result = sheets.spreadsheets().values().update( | |
spreadsheetId=business_url, | |
range=range_name, | |
body=body, | |
valueInputOption='USER_ENTERED' | |
).execute() | |
print(result) | |
# Write the message to send back | |
json_to_return["response"] = """Alright, your curbside takeout order was received and will be ready in 15 to 20min. | |
We will call you to process the payment. | |
Once you get here type HERE. | |
Thanks & see you soon!""" | |
# Include the row number and order id so Twilio can reference them in future | |
json_to_return["row_number"] = row_num | |
json_to_return["order_id"] = order_id | |
# If it doesn't look like the user has ordered anything, we mark | |
# "order_found" as False - we have logic Twilio-side which will split the flow | |
# based on whether this returned "order_found" value. | |
# As a fall back - also including a potential response so "response" is always | |
# a returned field | |
else: | |
json_to_return["order_found"] = False | |
json_to_return["response"] = "How about we have the restaurant call you?" | |
return json_to_return | |
# If the user is marked as having arrived | |
def arrived(business_url, | |
order_row, | |
order_id, | |
sent_message): | |
# Creating empty JSON object which we'll fill | |
json_to_return = {} | |
# The cells where the order SHOULD be listed based on the row number we recorded | |
# before. | |
order_range = "Orders!A"+order_row+":E"+order_row | |
print ("Order range: ",order_range) | |
# Check the orders sheet for the specific business, the specific row where this | |
# order should reside | |
order = sheets.spreadsheets().values().get(spreadsheetId=business_url, range=order_range).execute() | |
order_list = order.get('values', []) | |
print ("Order list: ",order_list) | |
# Extract the order ID to check against our saved order ID | |
retreived_order_id = str(order_list[0][0]) | |
print ("Retreived order id: ", retreived_order_id) | |
# We start by assuming we haven't found the appropriate row | |
found_appropriate_row = False | |
# When we DO find the appropriate row we're going to want | |
# to update it the received message (which will include car | |
# make and model), and the True under the "customer is here" | |
# column | |
body = { | |
"majorDimension": "ROWS", | |
"values": [ | |
[True, sent_message] | |
] | |
} | |
# If the ID in this row matches the order ID we expect, we can | |
# continue | |
if retreived_order_id == order_id: | |
# Log that we've been successful and update our variable | |
# so we can check we were successful | |
print ("ID matches") | |
found_appropriate_row = True | |
else: | |
# If we didn't find it, start looping through the whole range | |
print ("Looks like the sheet may have been reordered - looking for row") | |
all_range = "Orders!A:E" | |
# Get all existing orders | |
orders = sheets.spreadsheets().values().get(spreadsheetId=business_url, range=all_range).execute() | |
orders_list = orders.get('values', []) | |
for retrieved_row_num, order_details in enumerate(orders_list): | |
# For each order line, check the first cell (where the ID should be) | |
if order_details[0] == order_id: | |
# If we find the id - update our planned order_row | |
print ("Found order_id - was list_item: ",str(retrieved_row_num)) | |
# Adjusting for Python zero indexing | |
order_row = str(retrieved_row_num+1) | |
found_appropriate_row = True | |
# When we've found our order ID we don't need to keep going | |
# so stop there | |
break | |
if found_appropriate_row == True: | |
# If at any point we found our appropriate row - set a successful response | |
# message (we'll likely write a more specific one in Twilio) | |
json_to_return["response"] = """Great, thank you! We have maked you as here""" | |
# Set the row to update as the row we defined | |
print ("Updating appropriate row") | |
range_to_update = "Orders!G"+order_row+":H"+order_row | |
result = sheets.spreadsheets().values().update( | |
spreadsheetId=business_url, | |
range=range_to_update, | |
body=body, | |
valueInputOption='USER_ENTERED' | |
).execute() | |
# Print our result so we know we're done | |
print (result) | |
else: | |
# If we haven't found the appropriate row then setting unsuccessful message | |
json_to_return["response"] = """Something has gone wrong, please call the restaurant""" | |
# Make sure we return whether we were successful in our response | |
json_to_return["found_appropriate_row"] = found_appropriate_row | |
return json_to_return | |
# Main process | |
def sms(msg): | |
# Print test number and message for logging debugging | |
print ("Test:{}".format(test_num)) | |
print (msg) | |
# Trying to interpret message with Flask (formats are inconsisten | |
# so we try a couple ways) | |
try: | |
# Initially just try to get JSON | |
print ("Trying to get Flask to interpret JSON") | |
mydata = request.get_json(silent=True) | |
print ("JSON is: {}".format(mydata)) | |
from_number = mydata.get('from') | |
except: | |
print ("Failed to get json") | |
try: | |
# If that fails it's probably in bytes so we get that and | |
# convert to JSON. This will fail if the object can't be | |
# converted to JSON but we're expecting JSON so it makes | |
# sense to fail. | |
print ("Trying Flask to get data") | |
bytes_data = request.data | |
print ("Unmodified bytes data: {}".format(bytes_data)) | |
modified_bytes_data = bytes_data.decode('utf8').replace("'", '"').replace("\n"," ") | |
print ("Modified bytes data: {}".format(modified_bytes_data)) | |
print ("Converting data to json") | |
my_json = modified_bytes_data | |
print ("my_json is: {}".format(my_json)) | |
# Load the JSON to a Python list & dump it back out as formatted JSON | |
mydata = json.loads(my_json) | |
except: | |
print ("Failed to get data") | |
# The number the user messaged with | |
from_number = mydata.get('from') | |
# The message the user sent | |
sent_message = mydata.get('body') | |
print ("Sent message is: ",sent_message) | |
print ("Sent message is: {}".format(sent_message)) | |
# This is the manual variable we set in Twilio to keep track of where users are | |
stage = mydata.get('stage') | |
print ("Stage is: {}".format(stage)) | |
# The number they messaged (this lets us identify different businesses by their | |
# Twilio number) | |
sent_number = mydata.get('to') | |
try: | |
sent_number = str(sent_number) | |
except: | |
"" | |
print ("Contacted number is: {}".format(sent_number)) | |
# User is just starting - hasn't chosen anything | |
if stage == "start": | |
print ("Running def starting() with sent number: {}".format(sent_number)) | |
json_to_return = starting(sent_number) | |
return json_to_return | |
# User has sent their order - we aren't writing anything important yet, | |
# just putting something in the sheet to wake up the connection | |
elif stage == "waking": | |
business_url = mydata["url"] | |
waking(business_url = business_url) | |
return 200 | |
# User has been sent the menu, we're assuming they've chosen something | |
elif stage == "chosen": | |
# Extract the menu regex we constructed in our starting() function before | |
menu_regex = mydata["menu_regex"] | |
print ("Menu: ",menu_regex) | |
# Extract the business_url we also got in starting | |
business_url = mydata["url"] | |
print ("URL: ", business_url) | |
from_name = mydata["name"] | |
print ("Running def chosen()") | |
json_to_return = chosen( | |
sent_message = sent_message, | |
menu_regex = menu_regex, | |
business_url = business_url, | |
from_number = from_number, | |
from_name = from_name) | |
return json_to_return | |
# User has made their order and has sent the message "here" | |
elif stage == "arrived": | |
# Extract the business url we got before | |
business_url = mydata["url"] | |
print ("URL: ", business_url) | |
# Extract the order_row and order_id we defined in chosen() | |
order_row = mydata["order_row"] | |
order_id = mydata["order_id"] | |
print ("Running def arrived()") | |
json_to_return = arrived( | |
business_url = business_url, | |
order_row = order_row, | |
order_id = order_id, | |
sent_message = sent_message | |
) | |
return json_to_return | |
else: | |
print (""""[ERR] - No defined stage detected. | |
Number messaged: {} | |
Message sent: {}""".format(sent_number, sent_message)) | |
print ("+====================================+") | |
return_msg = "You messaged {} at the stage: {}. The text you sent was {}".format(sent_number,stage,sent_message) | |
print (str(return_msg)) | |
return str(return_msg) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment