How to Send SMS from a Google Spreadsheet
Time to read: 7 minutes
Google Sheets comes with a JavaScript-based language called Apps Script. In this tutorial we’ll use Apps Script to send SMS messages to phone numbers stored in a Google spreadsheet. The steps take less than ten minutes to complete — even if you’ve never written a line of code.
You may also be interested in How to Clean Up and Validate Phone Numbers in a Google Spreadsheet. With that tutorial, you can figure out which phone numbers in your spreadsheet can receive a text message before trying to send it.
The Twilio Bits
You’ll need a free Twilio account. If you don’t have one, sign up for an account here. You can do everything in this tutorial with a trial account, though you’ll be restricted to sending text messages to only your cellphone. (If you upgrade your account, $20 buys you about 2,500 text messages in the United States. Full SMS pricing can be found here).
Once you have a Twilio account, buy an SMS enabled Twilio phone number. Then go to your Programmable SMS dashboard and click Show API Credentials.
Keep your phone number and credentials handy. We’ll be pasting them into our code in a minute.
The Google Bits
Go to your Google Drive and create a new spreadsheet. Create column headings for:
- Phone Number
- Message Body
- Status
Add two rows with a phone number and message body. Preface your phone number with a ' (single tick) to tell the spreadsheet that the cell is a string (you can also format the column as “Plain Text”). To avoid spamming friends and family, you may want to use your cellphone number twice. Leave the status blank.
With our data in place, we’re ready to write some code. Click Tools -> Script Editor.
The rest of this post walks you through the code to send text messages from the spreadsheet. If you’re the type of person that likes to skip to the end of the mystery novel — or you just don’t have time for all that learning stuff — here’s the final product (take note of the placeholders for Account SID, Auth Token and Twilio Phone Number):
If you want to see how we arrived at that result, carry on.
Send an SMS from App Script
We’ll write this code in two steps:
- Write a sendSms function to send a single SMS.
- Write a sendAll function to run sendSms on every phone number and message body in the spreadsheet.
Replace the two lines of code in your script with:
Replace that (312) 313-1434 with your cell phone number.
The job of sendSms is to make an HTTP POST request to the Twilio Messaging API. You don’t need to know what that means to complete this tutorial, but you can think of it as submitting a “Send an Text Message” form with the fields of To, From, Body, Account SID, Auth Token.
All the code in this step goes inside the sendSms function. Define the URL for the Twilio Messaging API.
Replace the YOURACCOUNTSID placeholder with your Account SID from the dashboard.
Define a “payload” describing your text message that uses the parameters of the sendSms function for the to and body.
Replace the YOURTWILIONUMBER placeholder with your Twilio phone number.
Create an options hash to tell Apps Script that this is a POST request that uses the payload:
Add headers to options to authorize the request with your Account SID and Auth Token from the dashboard:
Finally, execute your HTTP request.
Save your file and run your script. You’ll be prompted to authorize your app the first time you run it. If everything goes well, your phone will light up with the message.
Send text messages from the spreadsheet
It’s not terribly practical to hardcode phone numbers into our script. Let’s add a function to read the phone number and message body from the spreadsheet, then pass that data to sendSms.
Replace what we’ve got in myFunction with:
Immediately above myFunction (and below sendSms), create the sendAll function. The rest of the code goes here.
Fetch the data from your spreadsheet:
Paste the code below that will:
- iterate through the data
- try to send an SMS to the phone number ( row[0]) with the message body ( row[1])
- if the request is successful, set a status of “sent”
- if the request throws an error, log the error and set status to “error”
- update the status column accordingly
Run your app again! You’ll need to reauthorize it since it’s now trying to access your spreadsheet as well.
Onward
Google Sheets and Apps Script provide a surprising amount of functionality for a programming language that requires no installation or configuration. If you found this useful, you may want to check out the How to Clean Up and Validate Phone Numbers in a Google Spreadsheet with which you could validate an entire spreadsheet full of phone numbers and answer questions like “is this a cellphone or a landline?”.
If you’d like to learn more about the Messaging API, check out these docs:
- Twilio REST API
- Sending SMS and MMS with the REST API
- SMS and MMS notifications in JavaScript and Node.js
If you’ve got any questions or come up with any cool use cases for sending texts from a spreadsheet, I’d love to hear about it. You can find me at gb@twilio.com or @greggyb.
Related Posts
Related Resources
Twilio Docs
From APIs to SDKs to sample apps
API reference documentation, SDKs, helper libraries, quickstarts, and tutorials for your language and platform.
Resource Center
The latest ebooks, industry reports, and webinars
Learn from customer engagement experts to improve your own communication.
Ahoy
Twilio's developer community hub
Best practices, code samples, and inspiration to build communications and digital engagement experiences.