Skip to content

Instantly share code, notes, and snippets.

@Robin-Lord
Created April 28, 2020 13:07
Show Gist options
  • Save Robin-Lord/ceee0d5c16a7b4d082244a01bb4d1f40 to your computer and use it in GitHub Desktop.
Save Robin-Lord/ceee0d5c16a7b4d082244a01bb4d1f40 to your computer and use it in GitHub Desktop.
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