Short code which works with Twilio to record

restaurant_bot.py

"""
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)