Simple Urgent SMS Notifications with Google Sheets and Twilio

Simple Urgent SMS Notifications with Google Sheets and Twilio

If you find yourself needing to send urgent messages out to customers or staff but don’t have developers on hand or access to your usual tools. Then this tutorial that shows you how to send SMS notifications from Google sheets may be perfect for you. In this blog post we’ll show you how to send messages to a list of numbers from a Google Sheet using Apps Script.

Tutorial Requirements

To follow this tutorial you need the following items:

• A Twilio account — If you are new to Twilio you can create a trial account with $10 US trial credit
• A Google or G-Suite account to access Google Sheets. You can create one here.

From Number or Alphanumeric Sender ID?

Twilio supports different options for sending SMS messages to numbers. These include standard phone numbers, Toll Free Numbers, Short Codes and Alphanumeric Sender IDs. Additionally, SMS regulations are different in almost every different country around the world. To understand what type of number you can use in your country of choice please refer to Twilio Regulatory Guidelines for SMS.

Another consideration is that each number type has different sending capacities. You can find more information about sending rates here. If you wish to send messages at a rate higher than that supported by your number you can use a Messaging Service. You can configure a messaging service in the Console. Sending SMS at large scale is not as simple as it seems to if you need to send large volumes per day or have them delivered in a certain amount of time please contact Twilio.

Setup

First step is to make a copy of the Google Sheet: Emergency SMS Notifications Powered by Twilio. The sheet explains the steps required to configure but for clarity we will run through them here as well.

1. Firstly create a copy of the sheet by selecting File -> Make a Copy.

1. Enter your Twilio Account SID and Auth Token which are available from your account dashboard. (More information here).
2. Decide where you are sending from.

1. To send from a single phone number or Alphanumeric Sender ID enter this in the From field.

1. Alternatively to use a Message Service set “Use Message Service?” to true and enter the Service SID as shown below.

Testing

Now that you’ve set up your number and account credentials you can enter a test number, such as your own and test that everything is working correctly. Click on Send Test.

As this is the first time you’ve run the script you’ll see a message like below.

Click continue and then select the Google Account that you would like to associate with the script.

You’ll also need to approve the permissions and click allow.

Once that is done you should get a notification that the message was sent successfully and a message on your phone.

Sending Bulk Messages

Now to send out your notification you’ll need to fill in the Greeting and Message boxes. Your message will be in the form “Greeting Name, Message”

1. Pick a Greeting to send for example “Hi” or for me in Australia “G’day” and enter this in the greeting box. If you don’t enter anything it will default to “Hi”
2. Enter your message in the Message box.

1. Now switch to the Numbers sheet and paste in your destination names and numbers.
2. Switch back to the Settings sheet and hit “Send SMS”

If you used the settings above you should get something like the message below on your phone.

Checking Message Status

When you hit Send SMS we will send all the SMS messages to Twilio using the REST API then we will store the initial status response that we get back. Usually if everything is fine this will be queued. If there is an error like the number isn’t valid this will be displayed on the Numbers sheet next to the number.

While waiting for Twilio to send all your messages you can click “Check Status” periodically and the status next to each contact in the Numbers sheet will update, as will the Status graph on the settings tab. This application does not update the message status automatically, for ways to do this check out the link in next steps below.

How it works

This blog builds on the functionality explained in this earlier post How to Send SMS from a Google Spreadsheet. If you would like an in-depth understanding of how to make the API calls from Google Apps script I would read through this post. You can read through and modify all the code by going to Tools -> Script Editor. This will open “SMS Notifications Script”.

The main difference between this script and the one outlined in the earlier blog post is that in this script we store the message SID (See What is a String Identifier (SID)?) in a hidden column D on the Numbers sheet and we use the below function to check the status of each individual message sent. This uses the Message Resource to check each individual message.

Troubleshooting

Hopefully, everything works without a hitch. But if there are any problems, first check that you have entered all your account details correctly. Next, check the Numbers sheet for any errors for particular numbers. Finally, you can check the logs inside the Script Editor under Tools -> Logs (Google Script Logging).

Conclusion and Next Steps

The solution shown here will allow you to send SMS notifications out to a moderately sized group of people quickly and efficiently. Now, that you can send them out you probably want to be able to receive replies as well. You can read all about how to do this in another post How to Receive SMS Messages into Google Sheets with Apps Script and Twilio.

Toby Allen is a Twilio Solutions Architect working with customers large and small in Australia. He helps introduce the world's best Communications Cloud downunder, one cURL request at a time. He is helping companies build great user experiences making sure that they leverage all the contextual information they have at hand. He has been working in real time communications for 15 years from developing video solutions for enterprises to helping carriers deploy globe spanning voice and messaging solutions including more than two years working with communications clouds. He’s active on Twitter @tobyallen and LinkedIn.