Send Mass Personalized SMS using Twilio and Google Sheets

Trying to find a simple way to send Twilio SMS with Google Sheets? Find the source code and examples on Github – https://github.com/nathanielkam/gsheets-twilio-sms

Disclaimer:

This code is meant for personal use, make sure you have permission to send text to the recipients before using or you may face FCC violations.

Why Would You Want to Send Mass Twilio SMS With Google Sheets?

Over the past 10 years, I have seen the efficacy of phone and email on the down turn. Users’ email inboxes are often unmanaged and neglected affairs. But during that time, users’ phone time has increased.

This means, we as marketers, sales people, and technologists need to meet users where they are livings and that is on their home screens and text conversations.

As a personal example, in 2019 I got engaged, and if you have ever tried planning a wedding, you know how hard it is to get a hold of people. We sent out save the dates, email reminders, calendar invites, but the best way to get everyone on the same page was definitely SMS text.

Why You Should Personalize Communications

Personalization increases engagement, and ultimately participation in whatever offer or update you are providing.

For example, in our wedding case, we had specific instruction sets for our wedding party and family versus extended family and guests. Additionally, we could start each text by including the recipient’s name, so that it sounded as if the text was just being sent to that person.

Why Twilio SMS with Google Sheets is Amazing

Programmable SMS has been on my professional radar for years. But even with the ease of the Twilio API, SDK, and TwiML, the cost of building on the platform versus a turnkey SaaS solution had kept it deep in our integration backlog in favor of web clients.

Even so, nothing has changed my personal belief that Twilio is the most full featured communications platform out there today. If you build your business on communication, Twilio is an amazing partner to build with (non-sponsored opinion).

Why I Chose Twilio SMS for This Project

There were 2 main reasons I chose Twilio:

• The first reason I chose Twilio for this personal project was the “pay for what you use” pricing model. In the true spirit of cloud computing, Twilio charges by consumption rather than the monthly or user license pricing models that most SaaS companies require.
• The second reason is TwiML Bins. This features allows for simple automation and declarative triggers to automate simple business processes.

Why I Chose App Script for Personalized Twilio SMS with Google Sheets

If you need a fast, simple database, a spreadsheet is a great option. With some simple string concatenation and vlookups we can mimic merge field variables and construct personalized messages per recipient.

Example: using =concatenate(“Hey “, NAME_CELL, ” this is a test”) will give a personal greeting to your SMS.

Next, app script is just javascript with Google Libraries loaded in, meaning we can use one of the world’s most popular languages to implement our business logic.

Twilio Phone and SMS Setup

You will need to register for a Twilio account here, and do the following:

1. Login to Twilio
2. Get a phone number that is SMS enabled
3. Write down your ACCOUNT_SID and AUTH_TOKEN

Google Sheets Setup

This is a very simple setup – you just need one sheet named “Send Text” and 3 columns:

1. Phone Number – must be in 555-555-5555 or +1-555-555-5555 format
2. Message – can be any text
3. Status – leave it blank, it will be filled out by the sendAll() function

Google App Script Setup

Grab the app script code from github – https://github.com/nathanielkam/gsheets-twilio-sms/blob/master/Code.gs

1. Open the app script editor from tools->script editor
   1. 
2. Paste your code into the editor
   1. 
3. Add your ACCOUNT_SID, and AUTH_TOKEN with the ones from your Twilio account console
4. Add YOUR_PHONE number that you purchased here.
5. Save your code

Send Your Twilio SMS with Google Sheets

To send your SMS all you need to do is:

1. Add phone numbers
2. Add or generate the text via formula to the message column
3. In the Script Editor press the play arrow to run the sendAll() function
   1. 
4. Review the status column for “sent” confirmations or any errors

Use TwiML to Forward Responses to Your Cell

By default, this Twilio setup is a one way text. You can send out to people, but if they reply, it will only be logged in Twilio rather than made visible anywhere you can respond. If that is enough for you, great – you can find responses in the logs here.

If you are looking to pick up conversations after someone replies, you are going to want a response handler in Twilio. This is where TwiML Bins come in. TwiML is the markup language for Twilio. TwiML can be used to construct response templates which can be added to the receipt trigger on your Twilio phone number.

Create a TwiML Bin Template for the forward

1. Create a new TwiML Bin – https://www.twilio.com/console/twiml-bins
2. Call it whatever you want. Ex: “Forward to my cell”
3. Add the following code, replacing YOUR_ACTUAL_PHONE with the phone number you want to forward replies to. If you want to forward to multiple phones (like how both my fiancee and I needed a copy), you can just add another line with a 2nd phone number.

<Response>
    <Message to='YOUR_ACTUAL_PHONE'>{{From}}: {{Body}}</Message>
</Response>

<Response>
    <Message to='YOUR_ACTUAL_PHONE_1'>{{From}}: {{Body}}</Message>
    <Message to='YOUR_ACTUAL_PHONE_2'>{{From}}: {{Body}}</Message>
</Response>

Attach the TwiML Bin to the Phone

Now that you have a forwarding template you can add it to the response trigger on your Twilio phone number. This will ensure when an SMS text comes into Twilio it will be handled by the TwiML Bin.

1. Go to your phone numbers
2. Click into your phone number
3. In the “Messaging” section, under “Configure with,” set it to “Webhooks, TwiML Bins, Functions, Studio, or Proxy”,
4. Next for when “A Message Comes In” select your TwiML Bin.
   1. 
5. Save and you are all setup

Now, when someone responds to your Twilio SMS it will automatically forward to the phone number(s) you set in the TwiML Bin, allowing you to keep the conversation going from there on.

Having Issues Sending Twilio SMS with Google Sheets?

If you are still having issues sending SMS via Google Sheets, drop me a line so I can update this post to reflect your use case.

Summary

Author Rating

Aggregate Rating

4.5 based on 15 votes

Software Name

Google App Script for Twilio Mass SMS Text

Operating System

Web

Software Category

Communication, Marketing

Price

USD 0.00

Landing Page

https://github.com/nathanielkam/gsheets-twilio-sms