How to Send SMS from a Google Spreadsheet

Time to read: 7 minutes

February 26, 2016
Written by
Greg Baugues
Twilion

Screen Shot 2016-02-26 at 4.09.59 PM

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):

function sendSms(to, body) {
  var messages_url = "https://api.twilio.com/2010-04-01/Accounts/YOURACCOUNTSID/Messages.json";

  var payload = {
    "To": to,
    "Body" : body,
    "From" : "YOURTWILIONUMBER"
  };

  var options = {
    "method" : "post",
    "payload" : payload
  };

  options.headers = { 
    "Authorization" : "Basic " + Utilities.base64Encode("YOURACCOUNTSID:YOURAUTHTOKEN")
  };

  UrlFetchApp.fetch(messages_url, options);
}

function sendAll() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2; 
  var numRows = sheet.getLastRow() - 1; 
  var dataRange = sheet.getRange(startRow, 1, numRows, 2) 
  var data = dataRange.getValues();

  for (i in data) {
    var row = data[i];
    try {
      response_data = sendSms(row[0], row[1]);
      status = "sent";
    } catch(err) {
      Logger.log(err);
      status = "error";
    }
    sheet.getRange(startRow + Number(i), 3).setValue(status);
  }
}

function myFunction() {
  sendAll();
}

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: 

function sendSms(to, body) {

}

function myFunction() {
  sendSms("(312) 313-1434", "Hello phone!");
}

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. 

var messagesUrl = "https://api.twilio.com/2010-04-01/Accounts/YOURACCOUNTSID/Messages.json";

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. 

var payload = {
  "To": to,
  "Body" : body,
  "From" : "YOURTWILIOPHONENUMBER"
};

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:

var options = {
  "method" : "post",
  "payload" : payload
};

Add headers to options to authorize the request with your Account SID and Auth Token from the dashboard:

options.headers = {    
  "Authorization" : "Basic " + Utilities.base64Encode("YOURACCOUNTSID:YOURAUTHTOKEN")
};

Finally, execute your HTTP request. 

UrlFetchApp.fetch(messagesUrl, options);

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: 

function myFunction() {
  sendAll();
}

Immediately above myFunction (and below sendSms),  create the  sendAll function. The rest of the code goes here. 

function sendAll() {

}

Fetch the data from your spreadsheet: 

var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; // First row of data to process
var numRows = sheet.getLastRow() - 1; 
var dataRange = sheet.getRange(startRow, 1, numRows, 2) 
var data = dataRange.getValues();

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

for (i in data) {
  var row = data[i];
  try {
    response_data = sendSms(row[0], row[1]);
    status = "sent";
  } catch(err) {
    Logger.log(err);
    status = "error";
  }
  sheet.getRange(startRow + Number(i), 3).setValue(status);
}

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.

Related Posts

Related Resources

A newspaper article

Twilio Docs

From APIs to SDKs to sample apps

API reference documentation, SDKs, helper libraries, quickstarts, and tutorials for your language and platform.
An Open book

Resource Center

The latest ebooks, industry reports, and webinars

Learn from customer engagement experts to improve your own communication.
User group reactions

Ahoy

Twilio's developer community hub

Best practices, code samples, and inspiration to build communications and digital engagement experiences.