How to make an SMS bot
with Google Sheets + Twilio

by Nicole He

This tutorial shows you how to set up an SMS bot in Google Sheets using a Twilio phone number in about 30 minutes. This guide is written with non-developers in mind, so it doesn’t require you to code, but we will be copying and pasting some code. If you are a developer you may want to check out the GitHub repo.

When people text your bot, it will ask a series of questions that you define, and then save their answers in your spreadsheet.

FAQ before we begin

What do I need to make this?

You need a Gmail account that is either a personal account, or if it’s a corporate account, has permission to share docs outside the organization. If you’re using a corporate account, you may need to check the admin settings. You might want to set up a new, personal Gmail account just for this bot.

You will also need a paid Twilio account and a Twilio phone number.

How much does it cost?

The cost is based on Twilio’s SMS pricing, which for US numbers is $0.0075 per message sent or received. So if your bot has 5 questions, there will be about 10 total messages per person, which will come out to about $0.075 per person who texts your bot and completes the flow. Twilio charges $1.00 a month for a local phone number, but there are more options, so see their page for more info.

Who can see the data collected by my bot?

Only people who have access to your Twilio account or your spreadsheet can see the data. 🔔  IMPORTANT 🔔: because you are collecting personal information (phone numbers at the very least), make sure to restrict the sharing permissions on your spreadsheet so only people you give access to can see it.

What are some best practices?

Make sure the people texting your bot know what type of information you’ll be collecting. Write each question as clearly as possible so the user knows what to say next. Start with a welcome message and end with a goodbye message so the user knows that the interaction is over, and what next steps will happen if any.

Don’t edit the sheet directly, use the add-on to change your bot’s messages.

Let’s get started!

Account and spreadsheet setup

  1. Sign up for a Twilio account and add billing. If you are going to use an existing Twilio phone number for your SMS bot, make sure it has SMS capabilities. (You can also add a new number in the tutorial.)
  2. Sign in to the Gmail account you will use for your spreadsheet.
  3. Open this spreadsheet template, and make a copy by clicking File → Make a copy. You may rename your sheet as you like.


Code setup

  1. Go to https://script.google.com/home/start
  2. Click the New project icon

  1. Your page should look like this:

  1. First, click Untitled project at the top and rename it to something like “SMS Bot” (or whatever you want). Hit OK.

  1. Delete the code that’s on the page.  Your page should now look like this:

  1. We’re now going to copy and paste some code to put in there. Go to https://raw.githubusercontent.com/nicolehe/sms-bot/main/Code.js, copy it all and paste it back into the Code.gs page. (You can do this by opening the page, hitting Cmd + A and then Cmd + C, then going back to Code.gs and hitting Cmd + V).
  2.  Your page should now look like this:

  1.  Go to File → Save or hit Cmd + S to save.
  2.  Now go to File → New → HTML File, and in the Enter new file name field type “Sidebar.html”

  1. On this new page, delete all the existing code from Sidebar.html so your page looks like this:

        

  1. Now,  just like before, go to https://raw.githubusercontent.com/nicolehe/sms-bot/main/Sidebar.html, copy it all and paste it back into the Sidebar.html page. (You can do this by opening the page, hitting Cmd + A and then Cmd + C, then going back to Sidebar.html and hitting Cmd + V). It should now look like this:

  1. Go to File → Save or hit Cmd + S to save. We are now done adding the code, the next step will be to “deploy” it!

Webapp URL for Twilio

  1. Now we are going to deploy our app and get (part of) a URL we’ll use for Twilio. Go to Publish → Deploy as web app… and a box will appear. Make sure under Execute the app as:  it says “Me (your email)” (it should be that by default). Under Who has access to the app: change it from “Only myself” to “Anyone, even anonymous”

 If you don’t see the “Anyone, even anonymous” option, you are likely using a corporate Gmail account and you do not have the permission to share documents outside your organization. Contact your admin to change the permissions, or start over with a personal Gmail address instead.

  1. Hit the Deploy button. A popup that says “Authorization required” will appear – hit the Review Permission button. It will ask you to sign in to your Gmail account. Sign in, making sure it’s still the same account as you will use for your spreadsheet. Another popup that says “This app isn’t verified” will appear. Click the Advanced button, and then click Go to SMS Bot (unsafe). Then click Allow.
    Note: this warning appears because the app you’re creating needs access to your spreadsheets in order to work, and it isn’t a “published” app since you are building it yourself. More info here.
  2. You should now see a box that says Deploy as web app with a field labeled Current web app URL.

  1. 🔔  IMPORTANT  🔔

This URL is important – this is what will allow Twilio to communicate with your Google Sheet. This is called your web app URL. Copy the web app URL or save it somewhere – you’ll need it in a minute.

  1. OPTIONAL: If you want other users besides the Gmail account you are currently using to be able to use your add-on and change the questions/messages your bot will send, you have to Disable the V8 engine. You can do that by going to Run → Disable new Apps Script runtime powered by Chrome V8

        

Now we’re going to install the add-on we’ve just created so we can use it in our spreadsheet.

Installing the add-on on your spreadsheet

  1. We are now going to add this add-on to our spreadsheet. Hit Run → Test as add-on…
  2. In the Configure New Test pop up, under INSTALLATION CONFIG, select “Installed and enabled”. In Select Doc, select the copy of the template spreadsheet we just made, which should be called “Copy of SMS Bot Response (TEMPLATE)” unless you changed the name. Click Save.
  3. In the Test as add-on box that appears, check the “Latest code” radio button and then click Test. It should open the spreadsheet.


  1. 🔔  IMPORTANT 🔔

You should notice that in your URL bar, the URL is very long and contains “addon_dry_run=”. Bookmark or save this URL to open the sheet again in the future with the add-on installed. This should also be the URL that you share with other collaborators who have access to your sheet if you want them to also be able to use the add-on. Otherwise the add-on won’t appear in the menu.

Using the add-on

  1. Go to Add-ons → SMS Bot → Show sidebar (sometimes this can take a few seconds to load)


  1. The sidebar will appear. Remember the web app URL we copied in step 19? We’re going to use it in the sidebar. In the top section of the sidebar, paste in the web app url in the area that tells you to “Paste the web app url here.”

  1. Then hit the Generate URL For Twilio button, which will give you another URL in the field below.


  1. Copy the new URL and then go to https://www.twilio.com/console/phone-numbers/incoming. Buy a new phone number for your SMS bot, or click on the existing one you have (if you are buying a new number, make sure to check “SMS” in the “Capabilities” section).
  2. In your phone number configuration page, scroll down to the Messaging section (make sure it’s Messaging and not Voice & Fax!):


  1. We are going to replace the https://demo.twilio.com/welcome/sms/reply URL with the new URL we just created. Copy the URL from the spreadsheet sidebar field above “Now copy the above URL…” from step 27 and replace https://demo.twilio.com/welcome/sms/reply with it.
  2. Next to the URL you pasted, it says HTTP POST – click that and change it to HTTP GET.
  3. It should now look like this (though your URL will be slightly different):

  1. Click Save.
  2. Now go back to your spreadsheet, which should still have the sidebar open.
  3. The bottom half of the sidebar is labeled SMS Bot Questions – there are some placeholder messages. Try editing them or adding the messages you want your bot to say. You can also change these later.
  4. When you’ve filled it out, click the Add questions to spreadsheet button.
  5. The right side of your sheet should now look like this (with whatever questions you put in):


  1. The bot is now ready to test! Try texting any message to your Twilio phone number – the bot will ask the questions in order and save the responses to the sheet.

You’re all set! 🎉

Nicole’s note: I would love to hear how people use this, so if you make an SMS bot please drop me a line and let me know! You can email hi@nicole.pizza