How to Receive SMS Messages into Google Sheets with Apps Script and Twilio

Time to read: 4 minutes

May 15, 2018
Written by
David Thurston
Twilion

Received_Messages_-_Google_Sheets copy

You want to receive SMS messages for business or for personal reasons, but not on your day-to-day mobile phone. Twilio has phone numbers which make it simple to receive SMS and we can easily place the messages into a Google Sheet.

Getting Started

Go to the Twilio Console, sign into your existing account or create a new account. Obtain a Twilio phone number or use an existing number. As a quick test, send a message from your mobile phone to your new phone number. On your mobile phone, you receive an automatic reply message from Twilio. You go to the Twilio Console SMS logs and view the message.

You can receive messages separate from your mobile phone, success! However, then you realize that the Console is functional for testing, but not practical for quickly searching and sorting the messages you’ve received.

How do we read the messages in a user friendly format? The solution is to use a Google Sheet to download and read your messages. As a bonus, you can copy the messages to another spreadsheet for organizational purposes. Following is the steps to get yourself set up.

Remove the Automatic SMS Replies

By default, a new Twilio phone number sends automatic reply message to the sender. Before having people send you messages, remove the automatic SMS reply.

  1. Log into the Twilio Console and go to the manage your phone numbers page.
  2. Click your phone number.
  3. Beside the Messaging incoming (A Message Comes In) Webhook, click the create icon (white plus sign in red circle).
  4. In the popup set, Friendly Name to: NoSmsAutoReply, and set, TwiML to: 

<?xml version="1.0" encoding="UTF-8"?>
<Response/>

Or, if you want to return a custom reply message by using this TwiML:

<?xml version="1.0" encoding="UTF-8"?>
<Response><Message>Thanks for your message.<Message/><Response/>

  1. Click Create.

Send another message to your Twilio phone number. Now, there is no automatic reply. Or, your custom reply message is received.

Setting up Google Sheets

Time to create a new Google Spreadsheet using your Google account. Once created, you will add column headings, and format two of the columns for the message receipt date and time information. Then you will copy and paste my sample script into the spreadsheet Script Editor. The last step is to update the script with your Twilio account information. You will then be ready to retrieve your SMS messages into your spreadsheet.

From your web browser, go to Google Drive where your Sheets are stored.

Start a new spreadsheet. Click the spreadsheet tab at the bottom, Sheet1. Rename the sheet to Receive.

In row 1 of the spreadsheet, add column titles:

  • Column 2: Date
  • Column 3: Time
  • Column 4: To
  • Column 5: From
  • Column 6: Message

Next, set the column data types:

  • Click column 2 (B). Click the menu item: Format/Number/Date
  • Click column 3 (C). Click the menu item: Format/Number/Time

Select the spreadsheet menu option: Tools/Script Editor.
Copy and paste the following into the Script Editor:

function myFunction() {
var ACCOUNT_SID = "your_account_SID";
var ACCOUNT_TOKEN = "your_account_auth_token";
var toPhoneNumber = "+16502223333";
var numberToRetrieve = 100;
var hoursOffset = 0;
var options = {
   "method" : "get"
};
options.headers = {
   "Authorization" : "Basic " + Utilities.base64Encode(ACCOUNT_SID + ":" + ACCOUNT_TOKEN)
};
var url="https://api.twilio.com/2010-04-01/Accounts/" + ACCOUNT_SID + "/Messages.json?To=" + toPhoneNumber + "&PageSize=" + numberToRetrieve;
var response = UrlFetchApp.fetch(url,options);
// -------------------------------------------
// Parse the JSON data and put it into the spreadsheet's active page.
// Documentation: https://www.twilio.com/docs/api/rest/response
var theSheet = SpreadsheetApp.getActiveSheet();
var theRow = 3;
var startColumn = 2;
var dataAll = JSON.parse(response.getContentText());
for (i = 0; i < dataAll.messages.length; i++) {
   theColumn = startColumn;
   // -------------------------------------
   // Date and Time
   rowDate = dataAll.messages[i].date_sent;
   var theDate = new Date (rowDate);
   if(isNaN(theDate.valueOf())) {
      theDate = 'Not a valid date-time';
      theColumn++;
      theColumn++;
   }
   else {
      theDate.setHours(theDate.getHours()+hoursOffset);
      theSheet.getRange(theRow, theColumn).setValue(theDate);
      theColumn++;
      theSheet.getRange(theRow, theColumn).setValue(theDate);
      theColumn++;
   }
   // -------------------------------------
   theSheet.getRange(theRow, theColumn).setValue(dataAll.messages[i].to);
   theColumn++;
   theSheet.getRange(theRow, theColumn).setValue(dataAll.messages[i].from);
   theColumn++;
   theSheet.getRange(theRow, theColumn).setValue(dataAll.messages[i].body);
   theRow++
   }
}

Click the Save icon, or menu option File/Save. Your script editor should look like this:


Replace your_account_SID and your_account_auth_token, with your account values. These credentials are available when you first log into the Twilio Console. Replace +16502223333 with your Twilio phone number.

Note that numberToRetrieve, is the number of messages to retrieve and list into the spreadsheet. It is currently set to 100. Feel free to adjust it to your requirements.

Click the Save icon.

You’re now ready to retrieve your messages into your spreadsheet.

In the Script Editor, click the run icon (the right pointing triangle). Messages will popup, first Preparing, then Running. When the messages are gone, you have received your SMS.

Change to the spreadsheet to view your messages.

Regarding the organization of your messages: each time you run the script, the spreadsheet message list is re-written. So don’t make changes in the retrieval spreadsheet. Instead, copy and paste the message list into another Google spreadsheet or into a spreadsheet on your computer. 

For example, following is a screen print of only the survey messages, sorted by answer.

To create this second spreadsheet, while in the Receive spreadsheet, click the add icon ( ) to add a new spreadsheet. Click the knew spreadsheet tab, Sheet2, and rename it, Survey. Copy and paste the headings from Receive to Survey. Copy and paste the survey messages into Survey. While in Survey, click View/Freeze/2 rows, to create the heading.  Click column F. Click Data/Sort sheet by column F, Z -> A. Now you have your survey messages sorted. We can see that the “yeses” win 3 to 2.

Receiving SMS and More with Google Sheets

Twilio phone numbers give you the power to receive SMS messages separately from your personal mobile phone number. Using a Google Spreadsheet you easily and quickly set up to view your messages. Using Spreadsheet tools and techniques, you can manage and organize your messages.

For further reading and testing, check out this article on How to Send SMS from a Google Spreadsheet. If you are a developer, check out our API documentation, SMS API: Send, Receive and Reply to Texts.

For casual SMS users who want to receive messages on your Android phone, have a look at my SMS app, Owl SMS, which is posted on Google Play.

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.