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

A large number of us are helping organizations that are confronting tough situations, or we’re confronting difficult situations ourselves. In case you’re working for an organization (or customer) that is in a difficult situation, the utilization of SMS chatbots could be a path for you to look outside your typical rundown of arrangements and assist them with prevailing in a totally unique manner. In case you’re an advertiser searching for work, adding this to your rundown of abilities could mean you keep things ticking along while a considerable lot of the standard entryways are shut — or that you open new entryways.

What you’ll get

In this post, I give you directions and code to create one, yet a progression of text-based chatbots that can be overseen by Google Sheets.

The model here is set up to work with restaurants, however could be adjusted to work with any business that necessities to get orders, check them against stock/menus, and note them down to be satisfied.

When the framework is set up, there will be no coding important to make another SMS-based chatbot for another business. In addition, that business will have the option to oversee key subtleties (like approaching orders and a menu) by essentially refreshing a Google Sheet, making the entirety of this undeniably more open than most different choices.

On the whole, some unique circumstance.

Some unique circumstance

In September 2017, as one of my first huge purposeful ventures at Distilled, I composed a Caseybiggs blog entry advising individuals how to make a chatbot and parting with some model code.

This April, I got an email from a man named Alexandre Silvestre. Alex had propelled “a non-benefit exertion to help the neighborhood entrepreneurs explore these difficult occasions, spare whatever number occupations as could be allowed, and keep on serving our locale while assisting with smoothing the bend.”

This exertion started by concentrating on restaurants. Alex had discovered my 2017 post (heavenly moly, content advertising works!) and inquired as to whether I could assist his with joining manufacture a chatbot. We concurred on some essential prerequisites for the bot:

  • It needed to work altogether inside instant message (and if the order was overly entangled it must have the option to set up a call straightforwardly with the restaurant).
  • Running it must be as near free as could be expected under the circumstances.
  • Restaurants must have the option to keep an eye on orders, update menus, and so forth., without setting up uncommon records.

The arrangement we concurred on had three sections:

  • Twilio (paid): supplies the phone number and handles the greater part of the conversational to and fro.
  • Google Cloud Functions (without semi): when a URL is called it runs code (counting refreshing our database for the restaurant) and returns a reaction.
  • Google Sheets (free): our database stage. We have a sheet which records the entirety of the organizations utilizing our chatbot, and connecting off to the individual Google Sheets for every business.

I’ll take you through every one of these segments thus and reveal to you how to function with them.

In case you’re returning to this post, or simply need assistance with one territory, don’t hesitate to hop to the particular part you’re keen on:

— Pricing

— Twilio

— Google Sheets

— Google Cloud Functions

— Test the bot

— Break things and have a great time

— Postscript — unusual hacks


This should all run pretty inexpensively — I’m talking like four pennies an order.

All things considered, consistently ensure that any evaluating cautions are coming through to an email address you effectively screen.

At the point when you’re simply beginning this, or when you’ve rolled out an improvement (like including new usefulness or new organizations), ensure you inquire in on your credits throughout the following not many weeks so you recognize what’s happening.


Neighborhood Twilio phone numbers cost about $1.00 every month. It’ll cost about $0.0075 to send and get writings, and Twilio Studio — which we use to do a great deal of the “discussion” — costs $0.01 each time it’s actuated (the initial 1,000 consistently are free).

Along these lines, accepting you have 2,500 content orders a month and each order takes around five instant messages, it’s coming to about $100 every month altogether.

Google Sheets

Google Sheets is free, and extraordinary. Long live Google Sheets.

Google Cloud Functions

Google shares full evaluating subtleties here, yet the significant things to think about are:

  1. Limited time credits

You get a free preliminary which keeps going as long as a year, and it incorporates $300 of limited time credits, so it’ll go through that before it goes through your cash. We’d burned through $0.00 (counting special credits) toward the finish of a month of testing. That is on the grounds that there’s additionally a month to month free recompense.

  1. Free remittance and valuing structure

Indeed, even beside the free credits, Google gives a free recompense each month. In the event that we expect that each order requires around 5 enactments of our code and our code takes as long as five seconds to run each time (which is some time yet some of the time Google Sheets is drowsy), we could be getting up to more than 400,000 orders for every month before we dunk into the special credits.


Twilio is a paid stage that lets you purchase a phone number and have that number naturally send certain reactions dependent on input.

In the event that you would prefer not to peruse more about Twilio and simply need the free Twilio chatbot stream, here it is.

Stage 1: Buy a Twilio phone number

When you’ve purchased a phone number, you can get messages to that number and they’ll be handled in your Twilio account. You can likewise send messages from that number.

Stage 2: Find your phone number

You can see your rundown of bought phone numbers by tapping the Twilio menu in the upper left hand corner and afterward clicking “Phone Numbers”. Or on the other hand, you can simply go to phone-numbers/approaching.


When you see your phone number recorded, make a note of it.

Stage 3: Create your Studio Flow

Studio is Twilio’s simplified editorial manager that lets you make the structure of your discussion. A studio “stream” is only the name of a particular discussion you’ve developed.

You can get to Twilio Studio by tapping on the Twilio menu again and tapping on “Studio” under “Runtime”.


Make another stream by clicking “Make a stream”.

At the point when you make another stream, you’ll be given the choice to begin without any preparation or utilize one of the inherent alternatives to construct your stream for you (in spite of the fact that they won’t be as top to bottom as the format I’m sharing here).

In the event that you need to utilize an adaptation of the stream which Alex and I assembled, select “Import from JSON” and snap “Next”. At that point, download this record and duplicate the substance into the crate that surfaces.

Ensure that it begins with a solitary { support, and finishes with a solitary } support. The container that surfaces will consequently have {} in it and on the off chance that you don’t erase them before you glue, you’ll bend over and it won’t acknowledge your information.

In the event that all works out positively, you’ll be given a stream that resembles this:


You may be soliciting: What for the sake of all that is heavenly is that tangle of hued spaghetti?

That is the Twilio Studio stream we made and, don’t stress, it fundamentally separates into a progression of numerous decision addresses where the response to each figures out where you go next in the stream.

Everything on the canvas that you can see is a gadget from the Twilio Studio gadget library associated along with “on the off chance that this, at that point that” type conditions.

The Studio Flow process

Before we go into explicit squares all the while, here’s a diagram of what occurs:

  1. A client messages one of our Twilio numbers
  2. In light of the particular number informed, we look into the restaurant related with it. We at that point utilize the name and spared menu of the restaurant to message the client.
  3. On the off chance that the client attempts to order off-menu, we interface a call to the restaurant
  4. In the event that the client picks something from our menu, we ask their name, at that point record their order in the sheet for that restaurant and reveal to them when to show up to get their order
  5. As/when the client messages to reveal to us they are outside the restaurant, we ask whether they are walking/a portrayal of their vehicle. We record the vehicle depiction in a similar restaurant sheet.

How about we take a gander at some model structure squares will we?

Beginning Trigger


The underlying trigger shows up directly toward the beginning of each stream, and parts the approaching contact dependent on whether it’s an instant message, a phone call, or if code is getting to it.

“Approaching Message” signifies the contact was by means of instant message. We just need to stress over that one until further notice, so how about we center around the left-hand line.

Record the way that we’re beginning another connection


Next, we utilize a “Set Variables” square, which you can snatch from the gadget library.

The “Set Variables” square lets us spare record data that we need to allude to later. For instance, we start by simply setting the “stage” of our connection. We state that the stage is “start” as in, we are toward the beginning of the connection. Later on we’ll check what the estimation of stage is, both in Studio and in our outside code, with the goal that we recognize what to do, when.


Get our menu

We expect that on the off chance that somebody informed us, setting off the chatbot, they are hoping to order so the following stage is to work out what the appropriate menu is.

Presently, we could simply work the menu out legitimately into Studio and state that at whatever point somebody sends us a message, we react with a similar rundown of choices. In any case, that has a few issues.

To start with, it would imply that on the off chance that we need to set this up for numerous restaurants, we’d need to make another stream for each.

The greater issue is that restaurants frequently change their menus. In the event that we need this to be something we can offer to heaps of various restaurants, we would prefer not to invest all our energy physically refreshing Twilio each time a restaurant comes up short on a fixing.

So what we truly need is for the restaurants to have the option to list their own menus. This is the place Google Sheets comes in, yet we’ll get to that later. In Twilio, we simply should have the option to request outer data and forward that outside data to the client. To do that we utilize a Webhook gadget:


This gadget makes a solicitation to a URL, gets the reaction, and afterward lets us utilize the substance of the reaction in our messages and stream.

On the off chance that the solicitation to the URL is effective, Twilio will naturally proceed to our prosperity step, else we can set it to send an “Uh oh, something turned out badly” reaction with the Fail alternative.

For this situation, our Webhook will make a solicitation to the Google Cloud capacities URL (more on that later). The solicitation we send will incorporate some data about the client and what we need the code to do. The data will be in JSON design (a similar organization that we used to import the Twilio stream I shared previously).

Our JSON will incorporate the particular Twilio phone number that has been informed, and we’ll utilize that number to separate between restaurants, just as the phone number that reached us. It’ll likewise incorporate the substance of the instant message we got and the “stage” we set prior, so the code comprehends what the client is searching for.


At that point the code will do some stuff (we’ll get to that later) and return data of its own. We would then be able to advise Twilio to utilize portions of the reaction in messages.

Communicate something specific accordingly

Next we can utilize the data we got to develop and make an impression on the client. Twilio will recall the number you’re in a discussion with and it’ll send your messages to that number.


This is the “Send and Wait For Reply” gadget, implying that once this message is sent, Twilio will expect the discussion is as yet going instead of consummation it there.

For this situation, we’re composing our welcome message. We could work out downright substance, yet we need to utilize a portion of the factors we got from our Webhook gadget. We called that particular Webhook gadget “get_options”, so we get to the substance we got from it by composing:


The reaction returns JSON, and luckily Twilio consequently splits that up for us.

We can get to singular pieces of the reaction by expressing “parsed” and afterward the mark we gave that data in our reaction. For what it’s worth, the reaction from the code looked something like this:

{“name”: restaurant_name,

“dishes_string”: “You can look over Margherita Pizza, Hawaiian Pizza, Vegetarian Pizza”

“increments”: “huge, medium, small”}

We get the accessible menu by stating “{{widgets.get_options.parsed.dishes_string}}”, and afterward we compose the message underneath which will be sent to individuals who contact the bot:


Settle on a choice dependent on a message

We can’t accept everybody is going to utilize the bot in the very same manner so we should have the option to change what we do dependent on specific conditions. The “Split Based On… ” gadget is the means by which we select certain conditions and set what to do in the event that they are met.


For this situation, we utilize the substance of the reaction to our past message which we get to utilizing {{options_follow_up.inbound.Body}}. “Options_follow_up” is the name of the Send and Wait gadget we just talked about, “inbound” signifies the reaction and, “Body” signifies the content inside it.

At that point we set a condition. In the event that the client reacts with anything along the lines of “other”, “no”, “help”, and so on., they’ll get sent off on another track to have a phone call. On the off chance that they react with anything not on that rundown, they may be attempting to order, so we take their order and check it with our code:


Set up a call

On the off chance that the client says they need something off-menu, we’ll have to set up a call with the restaurant. We do that by first calling the client:


At that point, when they get, associating that call to the restaurant number which we’ve just gazed upward in our sheets:


Stage 4: Select your studio stream for this phone number

Adhere to the guidelines in sync two to return to the particular posting for the phone number you purchased. At that point look to the base and select the Studio Flow you made.


Google Sheets

This chatbot utilizes two Google Sheets.

Free query sheet

The query sheet holds a rundown of Twilio phone numbers, the restaurant they have been appointed to, and the URL of the Google Sheet which holds the subtleties for that restaurant, so we realize where to search for each.

You’ll have to make a duplicate of the sheet to utilize it. I’ve remembered a line for the sheet I shared, clarifying every one of the segments. Don’t hesitate to erase that when you realize what you’re doing.


Free model restaurant sheet

The restaurant-explicit sheet is the place we remember the entirety of our data about the restaurant for a progression of tabs. You’ll have to make a duplicate of the sheet to utilize it.


The orders tab is mostly utilized by our code. It will consequently write in the order time, client name, client phone number, and subtleties of the order. As a matter of course it’ll compose FALSE in the “PAID/READY?” section, which the restaurant will at that point need to refresh.

In the last stage, the content will add TRUE to the “Client HERE?” section and give the vehicle depiction in the “Get INFO” segment.


Hold up time

This is a genuinely straightforward tab, as it contains one cell where the restaurant writes in how long it’ll be before orders are prepared. Our code will extricate that and offer it to Twilio to tell clients how long they’ll likely be pausing.


Accessible dishes and augmentations tabs

The restaurant records the dishes that are accessible now alongside straightforward adjustments to those dishes, at that point these menus are sent to clients when they contact the restaurant. At the point when the code gets an order, it’ll likewise watch that order against the rundown of dishes it sent to check whether the client is choosing one of the decisions.


Content utilizing sheet tab

You don’t have to contact this one by any means — it’s only a precautionary measure to evade our code incidentally overwriting itself.

Envision a circumstance where our code gets an order, finds the principal void line in the orders sheet, and records that order there. In any case, simultaneously, another person makes an order for a similar restaurant, another occasion of our code likewise searches for the primary void line, chooses a similar one, and the two of them write in it simultaneously. We’d lose at any rate one order despite the fact that the code thinks all is well.

To attempt to maintain a strategic distance from that, when our code begins to utilize the sheet, the primary thing it does is change the “Content utilizing sheet” worth to TRUE and records when it begins utilizing it. At that point, when it’s set, it changes the incentive back to FALSE.

On the off chance that our content goes to utilize the sheet and sees that “Content utilizing sheet” is set to TRUE, it’ll hold up until that worth turns out to be FALSE and afterward record the order.


How would I utilize the sheets?

Model restaurant sheet:

  1. Make a duplicate of the model restaurant sheet.
  2. Round out all the subtleties for your test restaurant.
  3. Duplicate the URL of the sheet.

Query sheet:

  1. Make a duplicate of the query sheet (you’ll just need to make one).
  2. Try not to erase anything in the “extricated id” segment yet supplant everything else.
  3. Put your Twilio number in the main segment.
  4. Glue the URL of your test restaurant in the Business Sheet URL segment.
  5. Include your business’ phone number in the last segment.


  1. Discover the “Administration Account” email address (which I’ll guide you to in the Cloud Functions area).
  2. Ensure that the two sheets are imparted to that email address having alter get to.

Making another restaurant:

  1. Whenever you have to make another restaurant, simply make a duplicate of the restaurant sheet.
  2. Ensure you tick “share with similar individuals” when you’re duplicating it.
  3. Get out the current subtleties.
  4. Glue the new Google Sheet URL in another line of your query sheet.

At the point when the code runs, it’ll open up the query sheet, utilize the Twilio phone number to locate the particular sheet ID for that restaurant, go to that sheet, and return the menu.

Google Cloud Functions

Google Cloud Functions is a basic method to naturally run code online without setting up workers or introduce an entire bundle of exceptional projects some place to ensure your code is adaptable.

On the off chance that you would prefer not to get familiar with Google Cloud and simply need code to run — here’s the free chatbot Python code.

What’s happening with the code?

Our code doesn’t attempt to deal with any of the genuine discussions, it just gets demands from Twilio — including insights regarding the client and what stage they are at — and plays out some basic capacities.

Stage 1: “Start”

The code gets a message from Twilio including the Twilio number that was initiated and the stage the client is at (start). In light of it being the “start” stage, the code initiates the beginning capacity.

It looks into the particular restaurant sheet dependent on the Twilio number, at that point restores the menu for that restaurant.

It additionally sends Twilio things like the particular restaurant’s number and a dense rendition of the menu and options for us to check orders against.

Stage 2: “Picked”

The code gets the stage the client is at (picked) just as their order message, the sheet ID for the restaurant, and the consolidated menu (which it sent to Twilio previously), so we don’t need to gaze those things upward once more.

In view of it being the “picked” stage, the code actuates the picked work. It checks if the order coordinates our dense menu. On the off chance that they didn’t, it reveals to Twilio that the message doesn’t resemble an order.

On the off chance that the order coordinates our menu, it records the order in the principal clear line. It additionally makes an order ID, which is a blend of the time and a part of the client’s phone number.

It sends Twilio a message back saying if the order coordinated our menu and, on the off chance that it matched our menu, what the order number is.

Stage 3: “Showed up”

The code gets the stage the client is at (shown up) and actuates the showed up work. It additionally gets the message portraying the client’s vehicle, the restaurant-explicit sheet ID, and the order number, all of which it recently told Twilio.

It looks into the restaurant sheet, and finds the order ID that coordinates the one it was sent, at that point refreshes that column to show the client has shown up and the portrayal of their vehicle.

Twilio handles all the specific circumstance

It may appear to be peculiar to you that each time the code discovers some data (for example, the sheet ID to look into) it sends that data to Twilio and solicitations it over again later on. That is on the grounds that our code doesn’t have the foggiest idea what’s happening by any means, aside from what Twilio tells it. Each time we actuate our code, it begins the very same way so it has no chance to get of knowing which client is messaging Twilio, what stage they’re at, or even what restaurant we’re discussing.

Twilio recollects these things for the course of the connection, so we use it to deal with the entirety of that stuff. Our code is a straightforward “do-er” — it doesn’t “know” anything for more than around five seconds one after another.

How would I set up the code?

I don’t have the opportunity to portray how to utilize Google Cloud Functions top to bottom, or how to code in Python, yet the code I’ve shared above incorporates a reasonable number of notes clarifying what’s happening, and I’ll talk you through the means explicit to this procedure.

Stage 1: Set up

Ensure you:

  • Have a Google account
  • Go to Google Cloud Console
  • Set up charging for your record (until you do, it won’t let you make capacities)
  • Duplicate the Python code from the area I connected to above

Stage 2: Create another capacity

Go here and click “make another capacity”. On the off chance that you haven’t made a venture previously, you may need to do that first, and you can give the undertaking whatever name you like.


Stage 3: Set out the subtleties for your capacity

The screen shot underneath gives you a great deal of the subtleties you need. I’d suggest you pick 256MB for memory — it ought to be sufficient. On the off chance that you discover you run into issues (or in the event that you need to be more mindful from the beginning), at that point increment it to 512MB.


Ensure you select HTTP as the trigger and note down the URL it gives you (on the off chance that you overlook, you can generally discover the URL by setting off to the “Trigger” tab of the capacity).

Additionally ensure you tick the alternative to permit Unauthenticated Access (that way Twilio will have the option to begin the capacity).

Select “Inline editorial manager” and glue in the Gist code I gave you (it’s intensely remarked, I prescribe giving it a read to fulfill sure you’re with what it’s doing).

Snap “REQUIREMENTS.TXT” and glue in the accompanying lines of libraries you’ll have to utilize:

  • jar
  • twilio
  • pytz

Ensure “capacity to execute” is SMS, at that point click “Nature Variables” dropdown.

Much the same as I’ve done above, click “+ ADD VARIABLE”, express “spreadsheet_id” in the “Name” section, and in the “Worth” segment, glue in the ID of your query sheet. You get the ID by taking a gander at the URL of the query sheet, and duplicating everything between the last two slices (laid out in red beneath).


Snap on the “Administration account” drop down. It should think of just “Application Engine default administration record” and give you an email address (as beneath) — that is the email address you need the entirety of your Google Sheets to be imparted to. Record it some place and include it as an alter client for both your query and restaurant-explicit sheets.


When you’ve done the entirety of that, click “Convey”.

When you send, you should land back on the fundamental screen for your Cloud Function. The green tick in the upper left hand corner reveals to you everything is working.

Stage 4: Turn on Sheets API


The first run through your code attempts to get to Google Sheets, it probably won’t have the option to in light of the fact that you have to turn on the Google Sheets API for your record. Go here, select the task you’re taking a shot at with the dropdown menu in the upper left corner, at that point click the huge blue “Empower” button.

Stage 5: Go back to Twilio and glue in the HTTP trigger for your code

Recollect the trigger URL we noted down from when we were making our capacity? Return to your Twilio Studio and discover the entirety of the squares with the sign in the upper left corner:


Snap on one by one and glue your Google Cloud URL into the REQUEST URL put away that goes ahead the correct side of the screen:


Test the bot

At this point you ought to have your Cloud Function set up. You ought to likewise have both of your Google Sheets set up and imparted to your Cloud Function administration account.

The subsequent stage is to test the bot. Start by messaging your Twilio number “order” to make it go. It ought to react with a menu that your code pulls from your restaurant-explicit Google Sheet. Follow the means it sends you all the way to the finish and check your Google Sheet to ensure it’s refreshing appropriately.

In the event that for reasons unknown it’s not working, there are two spots you can check. Twilio keeps a log of the considerable number of blunders it sees which you can discover by tapping the little “Debugger” image in the upper right corner:


Google additionally tracks everything that occurs with your Cloud Function. This incorporates non-mistake warnings. You can see the entirety of that by clicking “VIEW LOGS” at the top:


End: break things and have a fabulous time

The entirety of this is in no way, shape or form great, and I’m certain there’s stuff you could include and improve, however this is a method of building a system of adaptable chatbots, every particular to an alternate business, and each halfway oversaw by that business at negligible expense.

Check out this, break it, improve it, destroy it and start once more, and let me realize what you think!

You have successfully subscribed to the newsletter

There was an error while trying to send your request. Please try again.

CaseyBiggs will use the information you provide on this form to be in touch with you and to provide updates and marketing.