Rewind: Connecting Twilio SMS to Google Spreadsheet

Rewind: Connecting Twilio SMS to Google Spreadsheet

Back in 2012, we wrote a post that showed you how easy it was set up a simple data collection system using Twilio SMS and a Google Spreadsheet.  Well its 2 years later and  some things have changed.  In this post we’ll walk through an updated version of the little utility that makes it easy for developers and non-developers alike to receive SMS messages in a Google Spreadsheet called TwilioSheet.
I write a lot of simple little SMS applications, but they can be daunting at times. I often wonder if the idea is worth the time it takes to write the code to store, process, and act on the incoming messages. Why not use something a little easier? A spreadsheet perhaps? Well, that’s what TwilioSheet lets you do.

Once you’ve set up TwilioSheet, SMS messages sent to your Twilio number will show up in your spreadsheet after a few seconds. You can easily process the SMS messages that are coming into your spreadsheet using built-in spreadsheet functions or you can write your own functions in JavaScript.

While I wouldn’t recommend using a Google Spreadsheet as the data store for a production application, using a Google Spreadsheet is a great way to try out an idea. What I particularly like about Google Spreadsheets is how accessible they are: Many people who don’t consider themselves to be “programmers” can do amazing things with a spreadsheet. Additionally, for the scenarios where you can’t do something with the existing spreadsheet functions you can just use JavaScript.

Want to set up your own version of TwilioSheet? Head over to the TwilioSheet GitHub repository and grab the source.  Otherwise, read on to learn how simple it is to use Twilio and a Google Spreadsheet to collect SMS data, no coding required.

SMS Marketing in 15 Minutes

The story of TwilioSheet and how I discovered how easy it was to store SMS data in a Google spreadsheet has to do with the story of how I became inspired to create it in the first place.

Several months ago, while I was attending Startup Weekend Chandler, I ran into Jason Laveglia. Jason asked me if he could use Twilio to set up a marketing campaign where people could sign up for updates by texting a phone number a keyword and email address, separated by a space. I told Jason that Twilio would be perfect for that and I could write him an example application in 15 minutes.

As I wrote the application, I found myself wondering what I was going to use to store the text message data. Furthermore, I wanted Jason to be able to start playing with the data as it arrived. Clearly, meeting those criteria wasn’t going to happen in 15 minutes. What was I going to do? Then it hit me, a spreadsheet. I quickly set up a Google Spreadsheet and then turned it into a Google Form. Next, I wrote a simple little program to process the text message data and then submit the data to the Google Spreadsheet (by way of the Google Form).

Fifteen minutes and less than 50 lines of code later I had a demo working for Jason.

Enter TwilioSheet

After I built the sample application for Jason, I realized that with a little more code I could turn my sample application into something that could be used for all sorts of other use cases. Thus, TwilioSheet was born. At it’s core, TwilioSheet is a pretty simple little application. Its made up of two main parts:

• A class that handles submitting data to a Google Form.
• Code that takes an incoming SMS from Twilio and translates the incoming Twilio parameters parameters that the application can POST to the Google Form.

Virtually everything else is code for error handling and validation.

Lets take a look at how you can use Twilio, Google Forms and TwilioSheet to set up a simple keyword marketing campaign without writing a line of code.  Before you start you’ll need to make sure you have a Google account and a Twilio account.  If you don’t have a Twilio account its easy to sign up.

Once you’ve got those accounts created, you’ll need to set up a new Google Form which can collect incoming SMS data and connect your Twilio phone number to that form.  If you’re wondering how to do that, check out the step-by-step instructions on the TwilioSheet website.

All done?  Great!  Now you’re ready to test out your new keyword marketing campaign app.  Send your Twilio number the following text as an SMS:

Monkeys user@example.com

If everything worked as planned, you should see that SMS message data shows up in your spreadsheet in about 7 seconds.

Now that you’ve started collecting some raw data, you can use the power of the Google spreadsheet to start to manipulate it.  For example you can use formulas to separate out the keyword and the email address.

To start enter into cell F1:

=ARRAYFORMULA(IF(ISERROR(FINDB(" ", E:E)),"", FINDB(" ",E:E)))

This formula finds the location of the space between the keyword and the email address.  Next, enter this formula into G1:

=ARRAYFORMULA(IF(F:F="","", TRIM(LEFT(E:E,F:F))))

This formula checks to see if the F cell is empty and if not takes the keywork portion of the text.

Finally, enter this formula into cell H1:

=ARRAYFORMULA(IF(F:F="","", TRIM(RIGHT(E:E, LEN(E:E)-F:F))))

This formula also checks to see if the F cell is empty and if not takes the email portion of the text.

At this point, here is what you should see:

There you go, you just made a proof-of-concept keyword marketing campaign.

Just remember what I said before, this is for doing quick little proof-of-concept applications. Please, don’t run your production code off of a Google Spreadsheet.

Tune In Next Time

You can build all sorts of simple little SMS applications using TwilioSheet. Other use cases that come to my mind are: SMS “contact forms”, expense tracking, time tracking, and polling/voting applications.

In the meantime, please tell me what you think of TwilioSheet. Did you have trouble setting it up? Did you build something cool? Let me know by dropping me a line on email or hitting me up on twitter.