Building an advanced restaurant delivery chatbot with Google Sheets integration
Highly customizable chatbot in less than 2 hours
By ANDREW GANIN
This complete walk-through will teach you how to use Google Sheets integration in your chatbots. See how easy it is to build highly customizable chatbots for complex use cases with the power of Activechat.
Table of content
Setting the goals
Let’s start with complete list of features for this simple chatbot. Since it’s not a production-ready solution, but rather an illustration of Google Sheets integration mechanics, there will definitely be some shortcuts. Feel free to use the template for your own design!
We’ll use a simple Google Sheets spreadsheet to store chatbot data. One sheet will contain complete list of products that are available for delivery, and another will hold orders from chatbot users.
Click to see actual data here 👉 Restaurant delivery chatbot Google Sheets template
You can test the sample bot here 👉 Activechat Pizza Bot v.2
It’s supposed that there will be a bot admin, receiving new delivery orders, sending couriers and marking orders as delivered.
Here is the complete list of features for this simple chatbot:
- Display a gallery of products from specific category based on certain search criteria
- Display detailed information about specific products
- Take delivery orders, send order notifications to admin’s email and store orders in the Google Sheets document
- Allow users to check the status of their order and leave comments for couriers
Designing chatbot skills
This chatbot is very simple, so it will have just a few simple skills. The complete development process should take around an hour only.
First, we’ll have the “start” skill to greet the user and offer him/her some options.
Next, we’ll have a “menu” skill, displaying the gallery of products that fit user’s search criteria.
To process delivery orders, we’ll need the “order” skill.
Another skill will be used to check the status of user’s order and add comments to orders that are not yet delivered. Let’s call it “status”.
These skills will be inter-connected with events, and “default” skill will trigger other skills based on the keywords detected in user’s message.
Preparing the data
Our chatbot will be extremely flexible in terms of product catalogue. We’ll store everything in a Google spreadsheet, one product per row, with columns for:
- Product ID
- Product category (used for filtering)
- Product name
- Product description
- Product price
- Product weight
- Link to product image
- Product availability (a simple Yes/No)
Chatbot admin will be able to add more products and product categories easily, and disable product availability with a simple change of “Yes” to “No” in a spreadsheet cell.
Delivery orders will be stored in another sheet of the same spreadsheet. For each order (one per line) we’ll put there:
- Messenger ID of chatbot user
- His (her) name, e-mail and phone number
- Order date and time
- Order cost
- Name of the product ordered
- Delivery address
- Order status (“waiting”, “preparing”, “cooking”, “packing”, “delivering”, “done”, edited by admin)
- Estimated delivery time (edited by admin)
- Link to product image (we’ll use this in the next chapter to display the gallery of previous orders for specific user)
- User’s comments for this order (like, address details, extra add-ons etc).
Starting the conversation
Our “start” skill will be very simple. It will display “Choose something from our menu” message and then it will listen to user’s choice, displaying two quick replies – “pizza” and “salad”. Once user clicks one of these replies (or types anything else), “start” skill will continue to the SEND block, triggering “menu2” skill to display the menu.
Instead of clicking “pizza” or “salad” quick replies, user can type something else – for example “dessert”. If there’s anything in “desert” category in your Google Sheet, then these products will be displayed. LISTEN block is saving user’s input to the $search bot attribute, which is used in the next skill.
Displaying dynamic Google Sheets gallery
Now comes the fun part. We have product category in the $search attribute, and we want to display a gallery of products from our Google Sheet that relate to that category. Something that would require advanced coding in any other chatbot platform can be done in Activechat with just a couple of blocks.
ℹ️ When working with dynamic galleries you should keep in mind Facebook’s limitations on gallery size – it’s limited to 10 scrollable cards. To make chatbot conversation flexible and able to display unlimited number of products we’ll use a simple navigation trick.
GS-GALLERY block will do a search on your Google Sheet and display a gallery of cards that fit the search criteria. Let’s look into this in more detail.
Once you connect your Activechat to your Google Account (go to Settings – Integrations – Google to do this), you can use GS-GALLERY block to build dynamic galleries from any spreadsheet. The block in the example above contains three self-explanatory parts:
- Set search criteria. In this example we’ll build the gallery from rows where column B (product category) is equal to $search attribute value (remember – we got this value from the user in our “start” skill?) and column H (product availability) is equal to “Yes” (means, the product is available for delivery).
- Build gallery content. We’ll use column C (product title) as Title for each gallery card, column D (product description) as card’s Subtitle and column G (product image) as image URL.
- Add extra attributes to each card. We’ll add value from column E (product price) to “price” gallery attribute and column F (product weight) as “weight” gallery attribute. Later, when user clicks any of the gallery buttons, these values will be available as $_selected_gallery_price and $_selected_gallery_weight bot attributes (note the naming convention – we just add $_selected_gallery_ to the attribute name).
To each of these cards we’ll add two buttons – one to order the displayed product (it will trigger the “order” skill) and another to display detailed information about the product.
These buttons will be added to each card in the gallery. Once the user clicks any of these buttons, chatbot will continue to the connected block, while setting a number of attributes to values defined by the card content:
- $_selected_gallery_title will contain the “Title” field of the card
- $_selected_gallery_subtitle will contain the “Subtitle” field
- $_selected_gallery_image will contain the image link for that card
- $_selected_gallery_<attribute> will contain any extra card attributes that you added in the “Attributes” part of GS-SEARCH
This gives your chatbot the ability to know which card was clicked, and you can use that data in the conversation that follows.
For example, when user clicks “Details” button, we want to display detailed info about the selected product. Note the use of dynamic gallery attributes in TEXT and IMAGE blocks that are connected to this button:
TEXT block that displays detailed product description from $_selected_gallery_subtitle contains two buttons. “Show more” will go back to the GS-GALLERY block, displaying the same gallery again, and “New search” will ask user for new search criteria.
Note the use of card attributes to display extra data (not available as card Title or Subtitle) in the detailed description. This allows you to store extra parameters with each gallery card, and these card-specific parameters can be used in the conversation when user clicks the card in the gallery:
Navigating multiple gallery pages
Let’s see what happens if there’s more than 10 rows in your spreadsheet that fit GS-SEARCH block search criteria. Once this block is executed (and gallery built) you will have two system attributes to check the number of search results:
- $_gs_total_results will contain total number of spreadsheet rows that fit your search criteria
- $_gs_total_pages will contain the number of results divided by 10 (efficiently, the number of 10-card galleries required to display all search results)
We can check the value of $_gs_total_pages to see if there’s more than one gallery page and build a simple page navigation mechanics with quick replies:
What’s going on here?
Immediately after we display the dynamic gallery with products, we check the value of $_gs_total_pages with SWITCH block. If it’s less than 2 (i.e. we have only one page of search results) we do nothing (notice that there’s no block connected to that exit in SWITCH). But if it’s 2 or more, we proceed to another SWITCH with three different options:
- if current page number (we store it in $page attribute and set to 1 before displaying the gallery) is 1, we show “Use buttons to navigate” message and display a single quick reply for “Next page”
- if current page is greater than 1 and less than $_gs_total_pages (i.e. the maximum page number possible for that search), we display two quick replies for navigation – “Previous page” and “Next page”
- and, finally, if current page is equal to the maximum page number we display just a single quick reply – “Previous page”
Once the user clicks “Previous page” or “Next page” quick reply, we decrease or increase the current value of $page attribute by 1 and go back to GS-GALLERY block to display search results again, starting from the specified page:
Receiving delivery orders
What happens when chatbot users click the “Order” button in our dynamic gallery or in detailed product view? Let’s see how the bot is sending these orders to the Google spreadsheet.
This flow is very simple. The bot is asking user a couple of questions like email, phone number and delivery address, and then sends this data to bot admin with the LEAD block and stores it to Google Sheets with GS-UPDATE block. When this is done, the bot displays “You’re all set” confirmation message and two quick replies to check pizzas or salads again.
LEAD block is combining user’s details and order data into a neat email message that is sent automatically to admin’s address.
After this, we proceed to GS-UPDATE block to store the order into Google Sheets.
Notice – I’m setting “Insert” parameter to “true” and use “2” as the row number. It will instruct Activechat to add new rows above row 2, so that the latest orders are always on top of our spreadsheet (row 1 is reserved for column headers).
Cell values are obtained from attributes that the chatbot already has:
- “User ID” (column A) – from $_id system attribute
- “User name” (column B) – from $_first_name and $_last_name system attributes
- “E-mail” and “Phone” (columns C and D) – from lead data that we collected with LISTEN blocks ($user_email and $user_phone)
- “Order date” (column E) – from $_year, $_month, $_date system attributes (current date)
- “Order time” (column F) – from $_hour, $_minute, $_second system attributes (current time)
- “Order price”, “Order content” (columns G, H) – from $_selected_gallery_price and $_selected_gallery_title attributes that were set automatically when user clicked a button in the dynamic gallery
- “Delivery address” (column I) – from $user_address attribute collected by LISTEN block
- “Order status” (column J) – set by default to “received”. This can be changed later in the Google Sheets document by bot admin and updated statuses will be displayed to the user.
- “Delivery time” (column K) – set to “estimating..” by the bot. Once admin reviews the order, he/she can change the value in that cell to be displayed to user on every status check.
- “Image” (column L) – from $_selected_gallery_image attribute set when user clicked a button in the dynamic gallery. This column is not used in the current version of the bot, but we’ll be using it later to display the gallery of previous orders
- “Comment” – empty, the bot will be updating that column later, in the “status” skill.
Checking order status
Let’s make it possible for our chatbot users to check the current status of their order (remember we have “Status” column in our Google Sheet which is supposed to be updated by bot administrator?).
The logic will be quite simple: once the user requests the status check, we’ll find a row in the Google Sheet with his (her) Messenger ID in column A and anything except “done” in column J (“Order status”) and display the status. It is supposed that at every moment there can be only one (or none at all) order in any other status – this can cause problems if user is placing multiple orders before admin changes their status to “done”. You can fix it with a simple check before accepting new order – just use a similar GS-SEARCH to check for open orders placed by the same user. Adding this check will be your homework assignment! 😁
When this skill is triggered, the bot sends “Hold on, checking my records” message first and then we use GS-SEARCH to find a spreadsheet row where column A (user ID) is equal to the current user’s Messenger ID (it’s available in the $_id system attribute).
After GS-SEARCH is executed, we’ll check the value of $_gs_result system attribute. If it contains “This value was not found” (actually, I’m checking only for “not found”), it means that there are no active orders for that customer, and the bot is sending “It looks like there are no active orders from you currently”.
But if the value of $_gs_result is “Ok” – it means that the row was found, and we can update user with information from that row. So, the bot proceeds to the TEXT block:
This block is displaying data from Google Sheets row that the bot have found on the previous step. Notice the use of $_gs_<column> attributes – they are populated automatically after every successful search and contain values from every column in that row. That means that column A value will be available as $_gs_a, column B – as $_gs_b etc.
Adding comments to orders
You may have noticed “Add comment” button in the TEXT block that displays order status. I’ve added it to allow chatbot users to leave comments for their orders (for example, how to get to the building, adding extras to the order or anything else).
When user clicks that button the bot says ” Sure, just type and send” and LISTENs to user’s input, saving it to the $order_comment attribute. Once the user send his/her comment, the bot proceeds to GS-UPDATE block, adding that comment to column M in our spreadsheet (and overwriting previous value in that column, if any).
We’re using the value of $_gs_row attribute to specify which row in the spreadsheet should be updated. This attribute always contains row number of the last successful execution of GS-SEARCH block.
Notice that “Insert” option in the GS-UPDATE block editor is set to “false”. This instructs Activechat to update the existing spreadsheet row instead of adding new one. on top When we were accepting orders above, we set this parameter to “true” so that new orders would be piled on top of your spreadsheet.
Triggering skills with keywords
How does the user triggers these skills and how does the bot know which skill to use?
To make it possible we’ve introduced a very simple keyword detection in the “default” skill. For a quick recap – this skill will be triggered every time when user is sending message to your chatbot and you do not have any LISTEN block actively listening to user’s response (i.e. the bot is in “idle” mode).
We’re using SWITCH block to check the value of $_last_user_input system attribute (it always contains the last message that user sent to the chatbot).
If this attribute contains “pizza” or “salad” or “deliver” (the user asked something like “how to order delivery?” or “I want pizza” etc), the bot will trigger “start” skill again (since the ordering starts in that skill, with “Choose something from our menu” message).
If $_last_user_input contains words like “order” or “where” or “status” (for example, user is asking “Where is my order?” or “What’s my order status?”), the bot will trigger “status” skill (see above).
Please keep in mind that SWITCH block is evaluating conditions top to bottom, in the order they appear in the block editor. So, if you’re checking for “deliver” keyword first (to answer questions like “How do you deliver?”) and then for “delivery” keyword (to answer “Where is my delivery?”) you should be aware that both phrases will trigger the first condition because “delivery” contains “deliver” as sub-string.
Conclusion
It looks like we’ve managed to build quite complex chatbot which is almost ready to be used in real-life application. Bot admin will be able to add new products and categories easily, adding new rows to Google spreadsheet. Admin will receive orders, update statuses and estimated delivery times and mark orders as “done” on delivery, and chatbot users will be able to browse products, make orders, check order statuses and leave comments.
It took us less than an hour to build that chatbot, and it’s just 5 skills with 58 blocks in total. Imagine doing something similar with Manychat or Chatfuel!
You can test the sample bot here 👉 Activechat Pizza Bot v.2
You can experiment, customize and improve that bot from the template – look for “Activechat Pizza Bot v.2” in our chatbot templates!
There are many improvements to make, of course. Throwing in a couple of extra blocks will add the ability to send instant in-Messenger notifications to bot admins or arrange direct communication between customers and delivery couriers right in the chatbot – we’ll be covering these and many other cool features of Activechat in the next walk-through sessions like this!
Did you like it? Just let me know in the comments on our Facebook community!
Did you find this useful? Please share with other bot builders!
CONTACT US
- 888-370-4802
- ask@activechat.ai
- 1013 Centre Road
Wilmington, DE 19805
© 2018-2020 Activechat, Inc.