Many of us help companies that are in difficult times, or we ourselves are in difficult times. If you work for a company (or customer) that is in trouble, SMS chatbots can help you look beyond your normal list of solutions and help them succeed in a completely different way. If you're a marketer looking for work, adding to your list of skills can mean keeping things going while many of the usual doors are closed – or opening new doors.

What you get

In this post, I'll give you instructions and code to create not just one, but a series of text-based chatbots that can be managed by Google Sheets.

The example here is set up to work with restaurants, but can be customized to work with any company that needs to receive orders, review them using inventory / menus, and note them down to meet them.

Once the system is set up, coding is no longer required to create a new SMS-based chatbot for a new company. In addition, this company can manage important details (like incoming orders and a menu) by simply updating a Google Sheet. This makes all of this much more accessible than most other options.

But first a connection.

A context

In September 2017, as one of my first major passion projects at Distilled, I wrote a Moz blog post explaining to people how to create a chatbot and issue sample code.

This April I received an email from a man named Alexandre Silvestre. Alex had "launched a nonprofit to help local entrepreneurs overcome these challenging times, save as many jobs as possible, and continue to serve our community while smoothing the curve."

These efforts started by focusing on restaurants. Alex found my post for 2017 (Holy Moly, content marketing works!) And asked if I could help his team build a chatbot. We have agreed on a few basic requirements for the bot:

  • It had to work entirely within a text message (and if the order was very complicated, it had to be able to make a call directly to the restaurant).
  • Running had to be as free as possible.
  • Restaurants had to be able to check orders, update menus, etc. without setting up special accounts.

The solution we agreed on consisted of three parts:

  • Twilio (paid): Provides the phone number and handles most of the call.
  • Google Cloud Features (Semi-Free): When a URL is accessed, code is executed (including updating our database for the restaurant) and a response is returned.
  • Google Sheets (free): our database platform. We have a sheet that lists all the companies that use our chatbot and that is linked to the individual Google Sheets for each company.

I will walk you through each of these components one at a time and explain how you work with them.

If you return to this post or just need help in one area, you can jump to the part you're interested in:

– Twilio
– Google Sheets
– Google cloud features
– Test the bot
– Break things and have fun
– PostScript – strange hacks


It should all go pretty cheap – I'm talking about four cents per order.

Nevertheless, always make sure that price notifications are sent to an email address that you are actively monitoring.

If you are just starting out or have made changes (e.g. adding new features or adding new companies), you should check in your credits again in the next few weeks so that you know what is going on.


Local Twilio phone numbers cost around $ 1.00 a month. Sending and receiving text costs around $ 0.0075, and Twilio Studio – with which we conduct much of the "conversation" – costs $ 0.01 each time it is activated (the first 1,000 per month is free).

Suppose you have 2,500 text orders per month and each order takes approximately five text messages. So a total of about $ 100 per month is charged.

Google Sheets

Google Sheets is free and great. Long live Google Sheets.

Google cloud features

Google provides the full price details here. However, the most important information is:

1. Promotional credits

You get a free trial that lasts up to a year and includes $ 300 in promotional credit. So she'll spend it before you spend your money. We spent $ 0.00 (including advertising credit) at the end of a trial month. This is because there is also a monthly free allowance.

2. Free allowance and pricing structure

Aside from the free credits, Google grants a free allowance every month. If we assume that about 5 activations of our code are required for each order and it takes up to five seconds each time to execute our code (which takes a while, but sometimes Google Sheets is sluggish), we can previously process up to 400,000 orders Receive month We dive into the advertising credit.


Twilio is a paid platform that allows you to buy a phone number and have that number automatically send certain answers based on input.

If you no longer want to read about Twilio and just want the free Twilio chatbot flow, here it is.

Step 1: Buy a Twilio phone number

As soon as you have bought a telephone number, you can receive texts to this number that are processed in your Twilio account. You can also send texts from this number.

Step 2: find your phone number

You can view your list of purchased phone numbers by clicking the Twilio menu in the top left and then clicking "Phone Numbers". Or you can just go to phone numbers / incoming.

Write down your phone number as soon as it is listed.

Step 3: create your Studio Flow

Studio is Twilio's drag-and-drop editor that lets you create the structure of your conversation. A studio flow is just the name of a particular conversation that you created.

You can get to Twilio Studio by clicking the Twilio menu again and clicking on "Studio" under "Runtime".

Create a new flow by clicking "Create Flow".

When you create a new flow, you have the option to start over or use one of the built-in options to create your flow for you (although these aren't as detailed as the template I'm sharing here).

If you want to use a version of the process that Alex and I created, select "Import from JSON" and click "Next". Then download this file and copy the content into the field that appears.

Make sure it starts with a single bracket and ends with a single bracket. The displayed field automatically contains {}. If you do not delete them before inserting them, double them and your input will not be accepted.

If everything goes well, you will see a procedure that looks like this:

You may be wondering: what in the name of everything that is sacred is this tangle of colored spaghetti?

This is the Twilio Studio flow we created, and don't worry, it is essentially broken down into a series of multiple choice questions where the answer to each question determines where you will go next in the flow.

All you can see on the canvas is a widget from the Twilio Studio widget library associated with "If this, then that" conditions.

The studio flow process

Before we deal with certain blocks, here is an overview of what happens:

  1. A customer sends one of our Twilio numbers
  2. We use the number given to find the associated restaurant. We then use the name and saved menu of the restaurant to send a message to the customer.
  3. If the customer tries to order outside the menu, we connect a call to the restaurant
  4. When the customer selects something from our menu, we ask them for their name, write their order on the sheet for this restaurant and tell them when to arrive to pick up their order
  5. When the user informs us that he is outside the restaurant, we ask him if he is walking / a description of his vehicle. We note the vehicle description in the same restaurant sheet.

Let's look at some example building blocks.

First trigger

The first trigger appears right at the start of each flow and splits the incoming contact based on whether it's a text message, a phone call, or code access.

"Incoming message" means that the contact was made via SMS. We have to take care of it first, so let's focus on the left line.

Write down the fact that we are starting a new interaction

Next we use a "Set Variables" block that you can get from the widget library.

With the "Set Variables" block, we can save data record information that we want to refer to later. For example, we initially only set the “stage” of our interaction. We say that the "start" phase is as in, we are at the beginning of the interaction. We'll later check the value of Stage, both in Studio and in our external code, so that we know what to do and when.

Get our menu

We assume that someone who sends us a message and triggers the chatbot will look for an order. In the next phase he has to find out which menu is the right one.

Now we can just write the menu directly in Studio and say that every time someone sends us a message, we respond with the same list of options. But that has a few problems.

First, this would mean that we would have to create a new schedule for each restaurant if we wanted to set it up for multiple restaurants.

The bigger problem is that restaurants change their menus frequently. If we want this to be something we can offer to many different restaurants, we don't want to spend all our time manually updating Twilio every time a restaurant runs out of ingredients.

So what we really need is that the restaurants can list their own menus. This is where Google Sheets comes in, but we'll come back to that later. In Twilio we only have to be able to request external information and pass this external information on to the user. For this we use a webhook widget:

This widget makes a request to a URL, gets the response, and lets us use the content of the response in our messages and in the flow.

If the request to the URL is successful, Twilio automatically continues with our step of success. Otherwise, we can choose to send the reply "Oops, something went wrong" with the "Fail" option.

In this case, our webhook sends a request to the URL of the Google Cloud functions (more on this later). The request we send contains some information about the user and what the code needs to do. The information will be in JSON format (the same format we used to import the Twilio flow released above).

Our JSON contains the specific Twilio phone number that was submitted and we use this number to distinguish between restaurants and the phone number that contacted us. It also contains the content of the received text message and the predetermined “level” so that the code knows what the user is looking for.

Then the code does some things (we'll come back to that later) and returns its own information. We can then instruct Twilio to use parts of the response in messages.

Send a message as a reply

Next, we can use the information received to compose a message and send it to the user. Twilio will remember the number you are talking to and send your messages to that number.

This is the Send and Wait for Reply widget. This means that after sending this message, Twilio will assume that the conversation is still ongoing, rather than ending it there.

In this case, we write our welcome message. We could just write content, but we want to use some of the variables we got from our webhook widget. We called this special webhook widget "get_options", so we access the content we got from it by writing:


The answer comes back in JSON and luckily Twilio breaks it up for us automatically.

We can access individual parts of the answer by "analyzing" and then writing the label we specified in our answer. As it is, the answer from the code looked something like this:

{"Name": restaurant name,

"Dish string": "You can choose between Margherita Pizza, Hawaiian Pizza, Vegetarian Pizza"

"Additions": "large, medium, small"}

We get the available menu by writing "{{widgets.get_options.parsed.dishes_string}}", and then we write the following message, which is sent to people who contact the bot:

Make a decision based on a message

We cannot assume that everyone will use the bot in the same way, so we need to be able to change our work under certain conditions. With the "Split based on …" widget, we select certain conditions and determine what to do when they are met.

In this case, we use the content of the reply to our previous message, which we access with {{options_follow_up.inbound.Body}}. "Options_follow_up" is the name of the Send & Wait widget we just talked about. "Inbound" means the answer and "Body" means the text in it.

Then we set a condition. If the user answers "Other", "No", "Help", etc., the user is sent to another track to receive a call. If you answer with something that is not on this list, you may try to order. We take your order and check it with our code:

Set up a call

If the user indicates that they want something outside the menu, we have to make a call to the restaurant. We do this by first calling the user:

When they answer, connect this call to the restaurant number that we have already looked up in our sheets:

Step 4: Choose your studio flow for this phone number

Follow the instructions in step 2 to return to the specific list of the phone number you purchased. Then scroll down and select the Studio Flow you created.

Google Sheets

This chatbot uses two Google Sheets.

Free search sheet

The search sheet contains a list of Twilio phone numbers, the restaurant to which they were assigned, and the URL of the Google Sheet that contains the details for that restaurant so we know where to look for each.

You need to make a copy of the sheet to use it. I added a row to the sheet I shared to explain the individual columns. You can delete this at any time if you know what you are doing.

Free example restaurant sheet

On the restaurant-specific sheet you will find all information about the restaurant in a series of tabs. You need to make a copy of the sheet to use it.


The Orders tab is mainly used by our code. The order time, the customer name, the customer's telephone number and the details of the order are entered automatically. By default, FALSE is set to "PAID / READY?" Column that the restaurant must then update.

In the final phase, the script adds the "CUSTOMER HERE?" Column and enter the vehicle description in the "PICK UP INFO" column.

waiting period

This is a fairly simple tab, as it contains a cell where the restaurant shows how long it will take for the orders to finish. Our code extracts this and passes it on to Twilio so customers know how long they are likely to wait.

Available tabs for dishes and supplements

The restaurant lists the dishes that are now available, as well as simple adjustments to those dishes. These menus are then sent to customers when they contact the restaurant. When the code receives an order, it also checks that order against the list of dishes sent to determine if the customer selects one of the choices.

Script Using the Sheet Tab

You don't have to touch it at all – it's just a precaution to avoid our code from accidentally overwriting itself.

Imagine a situation where our code receives an order, finds the first blank line on the order sheet, and writes that order down there. At the same time, another person orders the same restaurant. Another instance of our code also looks for the first empty line, selects it and both write into it at the same time. We would lose at least one order, although the code believes everything is fine.

To avoid this, when our code starts using the sheet, it first changes the "Script with sheet" value to "TRUE" and writes it down when it uses it. When this is done, the value is changed back to FALSE.

If our script uses the sheet and determines that "Script with sheet" is set to TRUE, it waits for this value to become FALSE and then notes the order.

How do I use the leaves?

Example restaurant sheet::

  1. Make a copy of the sample restaurant sheet.
  2. Fill in all the details for your test restaurant.
  3. Copy the URL of the sheet.

Reference book::

  1. Make a copy of the search sheet (you only need to make one).
  2. Do not delete anything in the "Extracted ID" column, but replace everything else.
  3. Enter your Twilio number in the first column.
  4. Paste the URL of your test restaurant into the Business Sheet URL column.
  5. Add your company's phone number in the last column.


  1. Find the "Service Account" email address (to which I will forward you in the "Cloud Features" section).
  2. Make sure that both sheets are approved for this editable email address.

Create a new restaurant::

  1. If you need to create a new restaurant, just make a copy of the restaurant sheet.
  2. Make sure that you select the "Share with the same people" checkbox when copying.
  3. Delete the current details.
  4. Paste the new Google Sheet URL into a new line on your search sheet.

When the code runs, the search sheet opens. Use the Twilio phone number to find the specific sheet ID for this restaurant, go to that sheet and return to the menu.

Google cloud features

Google Cloud features are an easy way to automatically run code online without having to set up a server or install a number of special programs to ensure that your code is transferable.

If you don’t want to know more about Google Cloud and just want to run code, Here is the free chatbot python code.

What does the code do?

Our code doesn't try to process the actual conversations. He only receives requests from Twilio – including details about the user and at what time – and performs some simple functions.

Level 1: "Start"

The code receives a message from Twilio, including the activated Twilio number and the phase in which the user is (start). The code activates the start function based on the start phase.

It looks up the specific restaurant sheet based on the Twilio number and then returns the menu for that restaurant.

It also sends Twilio things like the number of each restaurant and a condensed version of the menu, as well as additions that we can use to check orders.

Level 2: "Chosen"

The code receives the phase the user is in (selected), as well as his order message, the sheet ID for the restaurant and the compressed menu (previously sent to Twilio), so we don't have to look up these things again.

The code activates the selected function based on the "selected" level. It is checked whether the order matches our compressed menu. If this is not the case, Twilio is informed that the message does not look like an order.

If the order matches our menu, the order is entered in the first blank line. An order number is also created, which is a combination of the time and part of the user's phone number.

Twilio receives a message stating whether the order matches our menu and whether the order number matches our menu.

Level 3: "Arrived"

The code receives the phase in which the user is (arrived) and activates the arrived function. It also receives the message that describes the user's vehicle, restaurant-specific sheet ID, and order number that Twilio was previously notified of.

It searches the restaurant sheet for the order number that matches the one sent, and then updates this line to indicate that the user has arrived and the description of their car.

Twilio takes care of the whole context

It may seem strange to you that each time the code finds information (e.g. the sheet ID to look up) it sends that information to Twilio and requests it again later. That's because our code doesn't know what's going on except what Twilio tells it to do. Every time we activate our code, it is started in the same way, so it is not known which user Twilio sends an SMS, what stage he is in or which restaurant we are talking about.

Twilio remembers these things in the course of the interaction, so we use them to do all of these things. Our code is a very simple "doer" – it "knows" nothing for more than about five seconds at a time.

How do I set up the code?

I don't have time to describe in detail how Google Cloud features are used or how Python is encoded, but the code I shared above contains a whole bunch of notes explaining what's going on, and I'll talk you through the steps specific to that process.

Step 1: set up

Make sure you:

Step 2: create a new function

Go here and click on "Create New Function". If you haven't created a project before, you may need to do so first. You can give the project any name you want.

Step 3: Define the details for your function

The screenshot below contains many details you need. I would recommend choosing 256MB for memory – it should be enough. If you find that you are having problems (or if you want to be more careful from the start), increase them to 512 MB.

Make sure you select HTTP as the trigger and make a note of the URL you get (if you forget this, you can always find the URL by going to the function's Trigger tab).

Also make sure that you enable the option to allow unauthenticated access (this way Twilio can start the function).

Select "Inline Editor" and paste in the gist code I specified (it is heavily commented. I recommend reading it to make sure you are happy with its work).

Click REQUIREMENTS.TXT and add the following library lines that you need to use:

Make sure that "Function to be performed" is SMS, and then click the Environment Variables drop-down list.

Click on "ADD VARIABLE" as above, write "Spreadsheet ID" in the "Name" column and insert the ID of your search sheet in the "Value" column. You can get the ID by looking at the URL of the search sheet and copying everything between the last two slashes (outlined in red below).

Click the "Service Account" drop-down list. It should only contain one "App Engine Standard Service Account" and give you an email address (as below) – this is the email address with which you want to share all of your Google Sheets. Write it down somewhere and add it as an editing user for your lookup and restaurant specific sheets.

Then click "Deploy".

After deployment, you should be back on the main screen for your cloud function. The green check mark in the upper left corner indicates that everything is working.

Step 4: Activate the Leaf API

If your code is trying to access Google Sheets for the first time, you may not be able to do this because you need to activate the Google Sheets API for your account. Select the project you are currently working on and click on the dropdown menu in the upper left corner. Then click on the big blue "ACTIVATE" button.

Step 5: Go back to Twilio and insert the HTTP trigger for your code

Do you remember the trigger URL we wrote down when we created our function? Go back to your Twilio studio and find all the blocks with that Sign in the upper left corner:

Click on each item one at a time and paste your Google Cloud URL into the REQUEST URL field that appears on the right side of the screen:

Test the bot

By now you should have set up your cloud function. You should also have both Google Sheets set up and shared with your Cloud Function service account.

The next step is to test the bot. Start by sending your Twilio number an SMS with the word “order” to make it work. It should respond with a menu that retrieves your code from your restaurant-specific Google Sheet. Follow the steps you get to the end and check your Google Sheet to make sure it updates properly.

If for some reason it doesn't work, you can check two places. Twilio keeps a log of all errors that you find by clicking on the small "Debugger" icon in the top right corner:

Google also records everything that happens to your cloud function. This includes error-free notifications. You can see all of this by clicking "SHOW LOGS" above:

Conclusion: break things and have fun

All of this is by no means perfect, and I'm sure there are things you can add and improve, but this is one way to build a network of scalable chatbots, each specific to a different company and partially managed by that company Costs.

Try it, break it, improve it, tear it up and start again, and let me know what you think!

Addendum: strange hacks

This bit is really only for people who are interested. However, since we deliberately did this in the smallest of spaces, we encounter a couple of strange problems – mainly when we ask our bot if it hasn't been activated for a while.

When Twilio gets news for the first time in a while, it turns on pretty quickly and expects other things to do the same. For example, if Twilio makes demands on our code, it is assumed that the code has failed if it lasts longer than five seconds. This is not uncommon – many chat platforms require a maximum processing time of five seconds.

Cloud functions can also be performed fairly quickly with less storage space required, but Google Sheets always seems to be a bit slow when accessed via the API. In fact, Google Sheets is particularly slow if it has not been accessed for a while.

This can mean that if no one has been using your bot lately, the Google Sheets API takes too long to respond the first time and Twilio gives up before our code can return, causing an error.

There are some parts of our script to avoid this.

I try again

When we activate our cloud feature for the first time, we don't want it to actually change anything, just information. In Twilio erstellen wir zunächst eine Variable namens "retries" und setzen den Wert auf 0.

Wenn die Anforderung fehlschlägt, prüfen wir, ob der Wiederholungswert 0 ist. Wenn dies der Fall ist, setzen wir den Wiederholungswert auf 1 und versuchen es erneut. Wenn es ein zweites Mal fehlschlägt, möchten wir dies nicht für immer tun, also senden wir einen Fehler und hören dort auf.

Das Blatt aufwecken

Wenn wir unsere Cloud-Funktion zum zweiten Mal aktivieren, möchten wir, dass sie etwas bewirkt. Wir können es nicht einfach noch einmal machen, wenn es nicht rechtzeitig zurückkommt, da wir am Ende doppelte Bestellungen haben, was dem Restaurant Kopfschmerzen bereitet.

Stattdessen nehmen wir während eines früheren Teils des Austauschs eine sinnlose Änderung an einem unserer Blätter vor, nur damit es bereit ist, wenn wir die wichtige Änderung vornehmen.

In unserem Gesprächsfluss haben wir:

  1. Senden Sie das Menü
  2. Erhalten Sie die Antwort
  3. Fragen Sie nach dem Namen des Benutzers
  4. Schreiben Sie die Bestellung

Wir müssen bis zum vierten Schritt nichts mit dem Blatt tun, aber nachdem wir die Antwort des Benutzers erhalten haben (bevor wir ihn nach seinem Namen fragen), aktivieren wir unseren Code einmal, um etwas Nutzloses in das Bestellblatt zu schreiben. Wir sagen zu Twilio – ob dies erfolgreich ist oder nicht – machen Sie mit der Interaktion weiter, da es zu diesem Zeitpunkt egal ist, ob wir rechtzeitig zurückgekehrt sind. Dann ist Google Sheets hoffentlich zu dem Zeitpunkt, zu dem wir in unserer Bestellung schreiben, für eine tatsächliche Verwendung bereit.

Es gibt Einschränkungen

Google Sheets ist nicht die ideale Datenbank. Sie ist langsam und kann dazu führen, dass wir die Zeitüberschreitungen für Twilio verpassen. Diese paar zusätzlichen Schritte helfen uns jedoch, einige dieser Einschränkungen zu umgehen.


Please enter your comment!
Please enter your name here