How to Send SMS from a Google Spreadsheet

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.

show-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.

phone-numbers

With our data in place, we’re ready to write some code. Click Tools -> Script Editor.

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:

  1. Write a sendSms function to send a single SMS.
  2. 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:

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.

  • Zac Campbell

    Greg, you are the man! Eventually more people will use appscript, but for now – finishing mine up. Going to save my team tons of time! Thanks again

  • arul selvan

    Greg,
    I got the script to send SMS to myself for all the rows. I am willing to buy Twilio credit.

    I want to send SMS acknowledgement to my customers from the below google sheet

    https://docs.google.com/spreadsheets/d/1Jpka0Wn8cQ6J6Be8Ks5vF-JJ50ykdCuMIetrWjAi7Kw/edit?usp=sharing

    I want SMS to go once to all the customers for whom the Status is not equal to “sent”

    The SMS will be like this

    Complaint No “SER 160530” with Megawin registered on “16/5/16” for customer “TNEB” due to “CT Failed”. Please call 18004257865 for details

    The message is made up of some text and the value from some cells in a particular row
    All rows where the phone number is not null must be processed and SMS must go
    The SMS will be sent only one to a customer for a row. Once it is sent, it will not be sent again

    Can you guide me?

  • sterch

    Hi, thank you for this.
    Could you help me with an option that the script send the SMS only to the rows where column D contains “YES”?
    thank you

  • Philly Ciz

    Worked the first two times then I got an error and It wont send anything else.

    • bryanwil

      I’m having an issue getting this to work as well. The credentials are correct, and they work in my Python script. However, I get an error every time I run this script saying, “Authentication Error – No credentials provided”.

      • Weird. If you’re still trying to figure this out, drop me an email at gb@twilio.com

        • bryanwil

          Thanks Greg. It actually turned out to be a syntax error. Once I cleaned up my JS code, it worked great.
          We’ve been using a version of this script 2-3 per month for 4 months now without any issues.
          Great post! Thanks again for sharing.

  • Daniel Harrison

    Im having an issue when sending a message out to my customers, when I run the script, I get about 50% of them state “Error” in the status box. What would cause an Error? I assume that it didn’t send?

    • So sorry you’re running into trouble here. Are you able to output the error to the console and check the logs?

  • Wei Miin Chie

    Hi Greg, I’d like to change the “From” Twillo number to Alpha Sender ID. Can you advice how this can be done? I tried based on this post by simply changing the enabled and verified Alpha Sender ID in my account but it did not work, http://twilioinc.wpengine.com/2015/08/send-branded-sms-messages-using-twilio-alphanumeric-sender-id.html

    Appreciate advice :)

    • @miintea:disqus Are you located in the UK?

      • Tiprin A. Lujan

        Hi Greg, I’ve been using this script and it’s working beautiful. I too am trying to send from a ‘caller ID’ that simply looks like my own phone number but am just not sure how to go about this.

  • Gary Pryde

    Set this up per instructions and everything works well except the maximum characters I can send is 22. The program fills in the spreadsheet fields as Message Sent and Texted but Twilio dashboard reports failed. Can anyone help tell me what to do to get Twilio to allow 160 characters with this script?

    • @gary_pryde:disqus you want to drop me an email and we’ll see if we can get this sorted out if you’re still having issues?

  • silverelf

    Hi Greg,

    Thanks for the guide!
    Can you do an example for Twilio Fax with App Script please?

    Thank you!

  • Clayton

    Can you set this up to pair with a google form submission? I would like to set up a service for the managers at our facility that they just submit a google form and it would send their submission as a message to the list of staff.

    • @disqus_W38ew8Qu7m:disqus while you could write this using actionscript, it may be easier to accomplish it using IFTTT or Zapier. They both act duct tape between misc internet services (such as Google Forms and Twilio).

  • Jon W.

    Awesome functionality.

    Would greatly appreciate the addition of script to tell sendSMS to skip existing sheet entries where ‘sent’ appears in the relevant cell, so as to allow the sheet to function as a database as well as a mere messaging machine. I know that’s not a Twilio thing, but it would certainly make this script complete, at least for my purposes.

    • Mrs. Gavers

      Did you get an answer to this question? We would like to do the same. If you received a modified script to complete this process, please share.

      • Avi

        just add an if statement inside the “try”. something like if row[3] = ‘sent’ (skip the send function.. else do the send function

        • Thanks Avi!

          @mrsgavers:disqus Did this work for you?

          • Mrs. Gavers

            I am just now reading this…so sorry. I am giving it a try now and will let you know. Thanks so much for responding to my problem.

          • Mrs. Gavers

            @baugues:disqus Did you get this to work? If so, can you share that section of script?

        • Mrs. Gavers

          Thanks so much for responding to my problem. Can you give me the exact code to send only if “sent” is not in column C? I am not experienced with using code. Thank you.

  • Avi

    This is super helpful! Just out of curiosity, is there a way to send an image (MMS) via url using this? Not just the link to the image, but the actual image itself.

    • Avi, Yeah, it’s super easy! Just add a fourth parameter to the payload, like this:

      var payload = {
      “To”: to,
      “Body” : body,
      “From” : “YOURTWILIOPHONENUMBER”,
      “MediaUrl” : “http://example.com/yourimage.png”
      };

      For more, check out the REST API docs on how to Send SMS and MMS:
      https://www.twilio.com/docs/api/rest/sending-messages

  • Anthony DeFallo

    Hello, I want to be able to have a large list of numbers going cell by cell. However, right now it looks as if I have to enter all the numbers in a single cell to get it to go to all recipients. Is there anyway I can have it read the list of numbers down the line and then just have a cell where I enter the message and it will send to the whole column of phone numbers that I have

  • Kim Hinge

    How can I make the script only send to new rows instead of all rows?

  • Great tutorial! Worked for me no problem!