Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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