How to Make a Scalable SMS Chatbot Using Twilio, Python, and Google Sheets (with Free Code)

Many of us are serving to companies which might be going through arduous occasions, or we’re going through arduous occasions ourselves. If you’re working for a firm (or shopper) that is in bother, using SMS chatbots could possibly be a approach for you to look exterior your regular listing of options and assist them reach a utterly completely different approach. If you’re a marketer on the lookout for work, including this to your listing of abilities may imply you retain issues ticking alongside whereas lots of the common doorways are closed — or that you simply open new doorways.

What you’ll get

In this publish, I offer you directions and code to produce not only one, however a sequence of text-based chatbots that may be managed by Google Sheets.

The instance right here is about up to work with eating places, however could possibly be tailored to work with any enterprise that wants to obtain orders, examine them in opposition to stock/menus, and observe them down to be fulfilled.

Once the system is about up, there might be no coding crucial to create a new SMS-based chatbot for a new enterprise. Plus, that enterprise might be ready to handle key particulars (like incoming orders and a menu) by merely updating a Google Sheet, making all of this much more accessible than most different choices.

But first, some context.

Some context

In September 2017, as one in all my first large ardour tasks at Distilled, I wrote a Moz weblog publish telling folks how to make a chatbot and giving freely some instance code.

This April, I bought an electronic mail from a man named Alexandre Silvestre. Alex had launched “a non-profit effort to help the local small business owners navigate these challenging times, save as many jobs as possible, and continue to serve our community while helping to flatten the curve.”

This effort started by specializing in eating places. Alex had discovered my 2017 publish (holy moly, content material advertising and marketing works!) and requested if I may assist his group construct a chatbot. We agreed on some primary necessities for the bot:

  • It had to work solely inside textual content message (and if the order was tremendous sophisticated it had to have the option to arrange a name instantly with the restaurant).
  • Running it had to be as shut to free as potential.
  • Restaurants had to have the option to examine on orders, replace menus, and many others., with out organising particular accounts.

The answer we agreed on had three components:

  • Twilio (paid): provides the telephone quantity and handles a lot of the conversational back-and-forth.
  • Google Cloud Functions (semi-free): when a URL is named it runs code (together with updating our database for the restaurant) and returns a response.
  • Google Sheets (free): our database platform. We have a sheet which lists all the companies utilizing our chatbot, and linking off to the person Google Sheets for every enterprise.

I’ll take you thru every of those parts in flip and inform you how to work with them.

If you’re coming again to this publish, or simply need assistance with one space, be at liberty to leap to the precise half you’re eager about:



—Google Sheets
—Google Cloud Functions
—Test the bot
—Break issues and have enjoyable
—Postscript — bizarre hacks


This ought to all run fairly cheaply — I’m speaking like 4 cents an order.

Even so, all the time ensure that any pricing alerts are coming by means of to an electronic mail handle you actively monitor.

When you are simply beginning on this, or if you’ve made a change (like including new performance or new companies), be sure you examine again in in your credit over the following few weeks so you already know what’s happening.


Local Twilio telephone numbers price about $1.00 per thirty days. It’ll price about $zero.0075 to ship and obtain texts, and Twilio Studio — which we use to do a lot of the “conversation” — prices $zero.01 each time it’s activated (the primary 1,00zero each month are free).

So, assuming you’ve got 2,500 textual content orders a month and every order takes about 5 textual content messages, it’s coming to about $100 a month in complete.

Google Sheets

Google Sheets is free, and nice. Long stay Google Sheets.

Google Cloud Functions

Google shares full pricing particulars right here, however the vital issues to find out about are:

1. Promotional credit

You get a free trial which lasts up to a yr, and it contains $300 of promotional credit, so it’ll spend that earlier than it spends your cash. We’d spent $zero.00 (together with promotional credit) on the finish of a month of testing. That’s as a result of there’s additionally a month-to-month free allowance.

2. Free allowance and pricing construction

Even other than the free credit, Google provides a free allowance each month. If we assume that every order requires about 5 activations of our code and our code takes up to 5 seconds to run every time (which is a whereas however typically Google Sheets is sluggish), we could possibly be getting up to over 400,00zero orders per thirty days earlier than we dip into the promotional credit.


Twilio is a paid platform that permits you to purchase a telephone quantity and have that quantity robotically ship sure responses primarily based on enter.

If you don’t need to learn extra about Twilio and simply need the free Twilio chatbot circulate, right here it’s.

Step 1: Buy a Twilio telephone quantity

Once you’ve purchased a telephone quantity, you possibly can obtain texts to that quantity and they’ll be processed in your Twilio account. You may ship texts from that quantity.

Step 2: Find your telephone quantity

You can see your listing of bought telephone numbers by clicking the Twilio menu within the prime left hand nook and then clicking “Phone Numbers”. Or, you possibly can simply go to phone-numbers/incoming.

Once you see your telephone quantity listed, make a observe of it.

Step three: Create your Studio Flow

Studio is Twilio’s drag-and-drop editor that permits you to create the construction of your dialog. A studio “flow” is simply the title of a particular dialog you’ve constructed.

You can get to Twilio Studio by clicking on the Twilio menu once more and clicking on “Studio” beneath “Runtime”.

Create a new circulate by clicking “Create a flow”.

When you create a new circulate, you’ll be given the choice to begin from scratch or use one of many built-in choices to construct your circulate for you (though they gained’t be as in-depth because the template I’m sharing right here).

If you need to use a model of the circulate which Alex and I constructed, choose “Import from JSON” and click on “Next”. Then, obtain this file and copy the contents into the field that comes up.

Make certain that it begins with a single brace, and ends with a single  brace. The field that comes up will robotically have in it and should you don’t delete them earlier than you paste, you’ll double-up and it gained’t settle for your enter.

If all goes effectively, you’ll be offered with a circulate that appears like this:

You may be asking: What within the title of all that’s holy is that tangle of coloured spaghetti?

That’s the Twilio Studio circulate we created and, don’t fear, it principally splits up into a sequence of multiple-choice questions the place the reply to every determines the place you go subsequent within the circulate.

Everything on the canvas that you could see is a widget from the Twilio Studio widget library linked along with “if this, then that” sort circumstances.

The Studio Flow course of

Before we go into particular blocks within the course of, right here’s an summary of what occurs:

  1. A buyer messages one in all our Twilio numbers
  2. Based on the precise quantity messaged, we glance up the restaurant related to it. We then use the title and saved menu of the restaurant to message the shopper.
  3. If the shopper tries to order off-menu, we join a name to the restaurant
  4. If the shopper chooses one thing from our menu, we ask their title, then report their order within the sheet for that restaurant and inform them when to arrive to decide up their order
  5. As/when the consumer messages to inform us they’re exterior the restaurant, we ask whether or not they’re on-foot/a description of their automobile. We report the automobile description in the identical restaurant sheet.

Let’s have a look at some instance constructing blocks lets?

Initial Trigger

The preliminary set off seems proper initially of each circulate, and splits the incoming contact primarily based on whether or not it’s a textual content message, a telephone name, or if code is accessing it.

“Incoming Message” means the contact was through textual content message. We solely want to fear about that one for now, so let’s deal with the left-hand line.

Record the truth that we’re beginning a new interplay

Next, we use a “Set Variables” block, which you’ll seize from the widget library.

The “Set Variables” block lets us save report info that we would like to refer to later. For instance, we begin by simply setting the “stage” of our interplay. We say that the stage is “start” as in, we’re initially of the interplay. Later on we’ll examine what the worth of stage is, each in Studio and in our exterior code, in order that we all know what to do, when.

Get our menu

We assume that if somebody messaged us, triggering the chatbot, they’re trying to order so the following stage is to work out what the relevant menu is.

Now, we may simply write the menu out instantly into Studio and say that every time somebody sends us a message, we reply with the identical listing of choices. But that has a couple issues.

First, it could imply that if we would like to set this up for a number of eating places, we’d have to create a new circulate for every. 

The larger concern is that eating places usually change their menus. If we would like this to be one thing we will provide to a number of completely different eating places, we don’t need to spend all our time manually updating Twilio each time a restaurant runs out of an ingredient.

So what we actually want is for the eating places to have the option to listing their very own menus. This is the place Google Sheets is available in, however we’ll get to that later. In Twilio, we simply want to have the option to ask for exterior info and ahead that exterior info to the consumer. To do this we use a Webhook widget:

This widget makes a request to a URL, will get the response, and then lets us use the content material of the response in our messages and circulate.

If the request to the URL is profitable, Twilio will robotically proceed to our success step, in any other case we will set it to ship an “Oops, something went wrong” response with the Fail possibility.

In this case, our Webhook will make a request to the Google Cloud capabilities URL (extra on that later). The request we ship will embrace some details about the consumer and what we want the code to do. The info might be in JSON format (the identical format that we used to import the Twilio circulate I shared above).

Our JSON will embrace the precise Twilio telephone quantity that is been messaged, and we’ll use that quantity to differentiate between eating places, in addition to the telephone quantity that contacted us. It’ll additionally embrace the content material of the textual content message we obtained and the “stage” we set earlier, so the code is aware of what the consumer is on the lookout for.

Then the code will do some stuff (we’ll get to that later) and return info of its personal. We can then inform Twilio to use components of the response in messages.

Send a message in response

Next we will use the knowledge we obtained to assemble and ship a message to the consumer. Twilio will keep in mind the quantity you’re in a dialog with and it’ll ship your messages to that quantity.

This is the “Send & Wait For Reply” widget, that means that after this message is shipped, Twilio will assume the dialog remains to be going quite than ending it there.

In this case, we’re writing our welcome message. We may write out simply plain content material, however we would like to use a number of the variables we bought from our Webhook widget. We referred to as that particular Webhook widget “get_options”, so we entry the content material we bought from it by writing:


The response comes again in JSON, and fortuitously Twilio robotically breaks that up for us. 

We can entry particular person components of the response by writing “parsed” and then the label we gave that info in our response. As it’s, the response from the code seemed one thing like this:

“name”: restaurant_name,

“dishes_string”: “You can select from Margherita Pizza, Hawaiian Pizza, Vegetarian Pizza”

“additions”: “large, medium, small”

We get the out there menu by writing “”, and then we write the message under which might be despatched to individuals who contact the bot:

Make a resolution primarily based on a message

We can’t assume everybody goes to use the bot in precisely the identical approach so we want to have the option to change what we do primarily based on sure circumstances. The “Split Based On…” widget is how we choose sure circumstances and set what to do if they’re met.

In this case, we use the content material of the response to our earlier message which we entry utilizing options_follow_up.inbound.Body. “Options_follow_up” is the title of the Send & Wait widget we simply spoke about, “inbound” means the response and, “Body” means the textual content inside it.

Then we set a situation. If the consumer responds with something alongside the traces of “other”, “no”, “help”, and many others., they’ll get despatched off on one other observe to have a telephone name. If they reply with something not on that listing, they may be making an attempt to order, so we take their order and examine it with our code:

Set up a name

If the consumer says they need one thing off-menu, we’ll want to arrange a name with the restaurant. We do this by first calling the consumer:

Then, once they decide up, connecting that decision to the restaurant quantity which we’ve already seemed up in our sheets:

Step four: Select your studio circulate for this telephone quantity

Follow the directions in step two to get again to the precise itemizing for the telephone quantity you acquire. Then scroll to the underside and choose the Studio Flow you created.

Google Sheets

This chatbot makes use of two Google Sheets.

Free lookup sheet

The lookup sheet holds a listing of Twilio telephone numbers, the restaurant they’ve been assigned to, and the URL of the Google Sheet which holds the small print for that restaurant, in order that we all know the place to search for every.

You’ll want to create a copy of the sheet to use it. I’ve included a row within the sheet I shared, explaining every of the columns. Feel free to delete that when you already know what you’re doing.

Free instance restaurant sheet

The restaurant-specific sheet is the place we embrace all of our details about the restaurant in a sequence of tabs. You’ll want to create a copy of the sheet to use it. 


The orders tab is especially utilized by our code. It will robotically write within the order time, buyer title, buyer telephone quantity, and particulars of the order. By default it’ll write FALSE within the “PAID/READY?” column, which the restaurant will then want to replace.

In the ultimate stage, the script will add TRUE to the “CUSTOMER HERE?” column and give the automotive description within the “PICK UP INFO” column.

Wait time

This is a pretty easy tab, because it accommodates one cell the place the restaurant writes in how lengthy it’ll be earlier than orders are prepared. Our code will extract that and give it to Twilio to let prospects know the way lengthy they’re going to probably be ready.

Available dishes and additions tabs

The restaurant lists the dishes which might be out there now together with easy variations to these dishes, then these menus are despatched to prospects once they contact the restaurant. When the code receives an order, it’ll additionally examine that order in opposition to the listing of dishes it despatched to see if the shopper is choosing one of many selections.

Script utilizing sheet tab

You don’t want to contact this one in any respect — it’s simply a precaution to keep away from our code by accident overwriting itself.

Imagine a scenario the place our code will get an order, finds the primary empty row within the orders sheet, and writes that order down there. However, on the similar time, another person makes an order for a similar restaurant, one other occasion of our code additionally seems to be for the primary empty row, selects the identical one, and they each write in it on the similar time. We’d lose at the very least one order although the code thinks all the pieces is okay.

To attempt to keep away from that, when our code begins to use the sheet, the very first thing it does is change the “Script using sheet” worth to TRUE and writes down when it begins utilizing it. Then, when it’s accomplished, it adjustments the worth again to FALSE.

If our script goes to use the sheet and sees that “Script using sheet” is about to TRUE, it’s going to wait till that worth turns into FALSE and then write down the order.

How do I take advantage of the sheets?

Example restaurant sheet:

  1. Make a copy of the instance restaurant sheet.
  2. Fill out all the small print to your check restaurant.
  3. Copy the URL of the sheet.

Lookup sheet:

  1. Make a copy of the lookup sheet (you’ll solely want to create one).
  2. Don’t delete something within the “extracted id” column however exchange all the pieces else.
  3. Put your Twilio quantity within the first column.
  4. Paste the URL of your check restaurant within the Business Sheet URL column.
  5. Add your small business’ telephone quantity within the last column.


  1. Find the “Service Account” electronic mail handle (which I’ll direct you to within the Cloud Functions part).
  2. Make certain that each sheets are shared with that electronic mail handle having edit entry.

Creating a new restaurant:

  1. Any time you want to create a new restaurant, simply make a copy of the restaurant sheet.
  2. Make certain you tick “share with the same people” if you’re copying it.
  3. Clear out the present particulars.
  4. Paste the brand new Google Sheet URL in a new line of your lookup sheet.

When the code runs, it’ll open up the lookup sheet, use the Twilio telephone quantity to discover the precise sheet ID for that restaurant, go to that sheet, and return the menu.

Google Cloud Functions

Google Cloud Functions is a easy approach to robotically run code on-line with out having to arrange servers or set up a entire bunch of particular packages someplace to ensure your code is transferable.

If you don’t need to be taught extra about Google Cloud and simply need code to run — right here’s the free chatbot Python code.

What is the code doing?

Our code doesn’t attempt to deal with any of the particular conversations, it simply will get requests from Twilio — together with particulars in regards to the consumer and what stage they’re at — and performs some easy capabilities.

Stage 1: “Start”

The code receives a message from Twilio together with the Twilio quantity that was activated and the stage the consumer is at (begin). Based on it being the “start” stage, the code prompts the beginning operate.

It seems to be up the precise restaurant sheet primarily based on the Twilio quantity, then returns the menu for that restaurant.

It additionally sends Twilio issues like the precise restaurant’s quantity and a condensed model of the menu and additions for us to examine orders in opposition to.

Stage 2: “Chosen”

The code receives the stage the consumer is at (chosen) in addition to their order message, the sheet ID for the restaurant, and the condensed menu (which it despatched to Twilio earlier than), so we don’t have to look these issues up once more.

Based on it being the “chosen” stage, the code prompts the chosen operate. It checks if the order matches our condensed menu. If they did not, it tells Twilio that the message doesn’t appear like an order. 

If the order does match our menu, it writes the order down within the first clean line. It additionally creates an order ID, which is a mixture of the time and a portion of the consumer’s telephone quantity.

It sends Twilio a message again saying if the order matched our menu and, if it did match our menu, what the order quantity is.

Stage three: “Arrived”

The code receives the stage the consumer is at (arrived) and prompts the arrived operate. It additionally receives the message describing the consumer’s automobile, the restaurant-specific sheet ID, and the order quantity, all of which it beforehand instructed Twilio.

It seems to be up the restaurant sheet, and finds the order ID that matches the one it was despatched, then updates that row to present the consumer has arrived and the outline of their automotive.

Twilio handles all of the context

It might sound bizarre to you that each time the code finds some info (as an illustration, the sheet ID to lookup) it sends that info to Twilio and requests it afresh in a while. That’s as a result of our code doesn’t know what’s happening in any respect, apart from what Twilio tells it. Every time we activate our code, it begins precisely the identical approach so it has no approach of understanding which consumer is texting Twilio, what stage they’re at, and even what restaurant we’re speaking about.

Twilio remembers this stuff for the course of the interplay, so we use it to deal with all of that stuff. Our code is a quite simple “do-er” — it doesn’t “know” something for greater than about 5 seconds at a time.

How do I arrange the code?

I don’t have time to describe how to use Google Cloud Functions in-depth, or how to code in Python, however the code I’ve shared above contains a honest variety of notes explaining what’s happening, and I’ll speak you thru the steps particular to this course of.

Step 1: Set up

Make certain you:

Step 2: Create a new operate

Go right here and click on “create a new function”. If you haven’t created a challenge earlier than, you may want to do this first, and you may give the challenge no matter title you want.

Step three: Set out the small print to your operate

The display shot under provides you a lot of the small print you want. I’d suggest you select 256MB for reminiscence — it ought to be sufficient. If you discover you run into issues (or if you need to be extra cautious from the beginning), then enhance it to 512MB.

Make certain you choose HTTP because the set off and observe down the URL it provides you (should you overlook, you possibly can all the time discover the URL by going to the “Trigger” tab of the operate).

Also be sure you tick the choice to enable Unauthenticated Access (that approach Twilio might be ready to begin the operate).

Select “Inline editor” and paste within the Gist code I gave you (it’s closely commented, I like to recommend giving it a learn to be sure you’re proud of what it’s doing).

Click “REQUIREMENTS.TXT” and paste within the following traces of libraries you’ll want to use:

Make certain “function to execute” is SMS, then click on the “Environment Variables” dropdown.

Just like I’ve accomplished above, click on “+ ADD VARIABLE”, write “spreadsheet_id” within the “Name” column, and within the “Value” column, paste within the ID of your lookup sheet. You get the ID by trying on the URL of the lookup sheet, and copying all the pieces between the final two slashes (outlined in crimson under).

Click on the “Service account” drop down. It ought to give you simply “App Engine default service account” and offer you an electronic mail handle (as under) — that is the e-mail handle you want all your Google Sheets to be shared with. Write it down someplace and add it as an edit consumer for each your lookup and restaurant-specific sheets.

Once you’ve accomplished all of that, click on “Deploy”.

Once you deploy, it is best to land again on the primary display to your Cloud Function. The inexperienced tick within the prime left hand nook tells you all the pieces is working.

Step four: Turn on Sheets API

The first time your code tries to entry Google Sheets, it won’t have the option to since you want to swap on the Google Sheets API to your account. Go right here, choose the challenge you’re engaged on with the dropdown menu within the prime left nook, then click on the large blue “ENABLE” button.

Step 5: Go again to Twilio and paste within the HTTP set off to your code

Remember the set off URL we famous down from once we had been creating our operate? Go again to your Twilio Studio and discover all the blocks with the </> signal within the prime left nook:

Click on every in flip and paste your Google Cloud URL into the REQUEST URL field that comes up on the proper aspect of the display:

Test the bot

By now it is best to have your Cloud Function arrange. You must also have each of your Google Sheets arrange and shared along with your Cloud Function service account.

The subsequent step is to check the bot. Start by texting your Twilio quantity the phrase “order” to get it going. It ought to reply with a menu that your code pulls out of your restaurant-specific Google Sheet. Follow the steps it sends you thru to the tip and examine your Google Sheet to ensure it’s updating correctly.

If for some cause it’s not working, there are two locations you possibly can examine. Twilio retains a log of all of the errors it sees which you could find by clicking the little “Debugger” image within the prime proper nook:

Google additionally retains a report of all the pieces that occurs along with your Cloud Function. This contains non-error notifications. You can see all of that by clicking “VIEW LOGS” on the prime:

Conclusion: break issues and have enjoyable

All of that is under no circumstances excellent, and I’m certain there’s stuff you possibly can add and enhance, however that is a approach of constructing a community of scalable chatbots, every particular to a completely different enterprise, and every partially managed by that enterprise at minimal price.

Give this a attempt, break it, enhance it, tear it up and begin once more, and let me know what you suppose!

Postscript: bizarre hacks

This bit is just actually for people who find themselves , however as a result of we’ve intentionally accomplished this on a shoestring, we run into a couple bizarre points — primarily round requests to our bot when it hasn’t been activated for a bit.

When Twilio will get messages for the primary time in a whereas, it activates fairly rapidly and expects different issues to accomplish that, too. For instance, when Twilio makes requests to our code, it assumes that the code failed if it takes greater than about 5 seconds. That’s not that uncommon — a lot of chat platforms demand a five-second max turnaround time.

Cloud Functions are ready to run fairly quick, even with decrease reminiscence allowances, however Google Sheets all the time appears to be a bit gradual when accessed by means of the API. In reality, Google Sheets is especially gradual if it hasn’t been accessed in a while.

That can imply that, if nobody has used your bot just lately, Google Sheets API takes too lengthy to reply the primary time and Twilio provides up earlier than our code can return, inflicting an error.

There are a couple components of our script designed to keep away from that.

Trying once more

The first time we activate our Cloud Function, we don’t need it to truly change something, we simply need info. So in Twilio, we begin by creating a variable referred to as “retries” and setting the worth as zero. 

If the request fails, we examine if the retries worth is zero. If it’s, then we set the retries worth to 1 and attempt once more. If it fails a second time, we don’t need to maintain doing this eternally so we ship an error and cease there.

Waking the sheet up

The second time we activate our Cloud Function we do need it to do one thing. We can’t simply do it once more if it doesn’t return in time as a result of we’ll find yourself with duplicate orders, which is a headache for the restaurant.

Instead, throughout an earlier a part of the trade, we make a pointless change to one in all our sheets, simply in order that it’s prepared for once we make the vital change.

In our conversational circulate we:

  1. Send the menu
  2. Get the response
  3. Ask for the consumer’s title
  4. Write the order

We don’t want to do something to the sheet till step 4, however after we get the consumer’s response (earlier than we ask their title), we activate our code as soon as to write one thing ineffective into the order sheet. We say to Twilio — whether or not that succeeds or fails — maintain going with the interplay, as a result of it doesn’t matter at that time whether or not we’ve returned in time. Then, hopefully, by the point we go to write in our order, Google Sheets is prepared for some precise use.

There are limitations

Google Sheets just isn’t the best database — it’s gradual and may imply we miss the timeouts for Twilio. But these couple of additional steps assist us work round a few of these limitations.

Source hyperlink Internet Marketing