Using your Twilio phone number and Google Sheets

Need a simple way to send out templated text messages to any number of people?

This easy-to-copy script lets you use your existing Twilio phone number to send out text messages to any number of people in Google Sheets.

https://www.awesomescreenshot.com/embed?id=13832218&shareKey=9875a879569024f9020c634d347aebce&info=false"></iframe>

Sign into your Twilio account
Open a custom Google Sheet
Enter your Twilio Account SID, Auth Token, and Phone number into the Credentials tab
Choose to send a dynamic SMS (including your customer's first name) or plain text with no name.
Once triggered, the Status field will update with the date and time your message was sent.
Add more customers - and any customer with an EMPTY status field will get a text the next time you send out a message (it won’t send a text unless the status field is blank)

How to build your own SMS buttons in Google Sheets

<aside> 🧑🏽‍💻 If you need help with anything or find yourself short on time, don't worry! You can always hire me to finish the job for you at the end. No pressure, just an option in case you need it! Skip to then end for more info.

</aside>

⌛ How long? From 30 - 60 minutes (depending on your familiarity with Google Sheets and Google Apps Scripts)

🛠️ What you need?

A Google account
A Twilio account with an active phone number, Account SID and Auth Token. If you don't have one, sign up at https://www.twilio.com/
A Google Sheets document that you have permission to edit.

⚠️ DON’T SPAM PEOPLE ⚠️ While this is super helpful to reach a large number of customers - be sure to follow all industry marketing best practices and opt-in rules, and review all the Twilio guidelines like Restricted SMS message types using Twilio.

Let’s build!

Follow these steps to build the send SMS message option into Google Sheets:

Open the Google Sheets document you want to use and label the first four columns as follows:
1. Column A: First name
2. Column B: Last name
3. Column C: Phone number
4. Column D: Status

<aside> 💁🏽‍♂️ The first four columns in your sheet need to stay in this order unless you intend to edit the script listed below

</aside>

Go to Tools > Script Editor to open the Google Apps Script editor.
Copy and paste the code into the editor.

var CUSTOMER_FIRST_NAME = 0;
var CUSTOMER_LAST_NAME = 1;
var CUSTOMER_PHONE_NUMBER = 2;
var MESSAGE_STATUS = 3;

var TWILIO_ACCOUNT_SID = 'placeholder';    
var TWILIO_PHONE_NUMBER = 'placeholder'; 
var TWILIO_AUTH_TOKEN = 'placeholder'; 

var ui = SpreadsheetApp.getUi();
var userProperties = PropertiesService.getUserProperties();

function onOpen() {
 ui.createMenu('Credentials')
   .addItem('Set Twilio Account SID', 'setTwilioAccountSID') 
   .addItem('Set Twilio Auth Token', 'setTwilioAuthToken')
   .addItem('Set Twilio phone number', 'setTwilioPhoneNumber')
   .addItem('Delete Twilio Account SID', 'deleteTwilioAccountSID')
   .addItem('Delete Twilio Auth Token', 'deleteTwilioAuthToken')
   .addItem('Delete Twilio phone number', 'deleteTwilioPhoneNumber')
   .addToUi(); //using the above would create a menu option forcing the user to input their Twilio creditials each time they use the spreadsheet. This may or may not be a cumbersome task. 
   ui.createMenu('Send SMS')
   .addItem('Send with name', 'sendWithName')
   .addItem('Send without name', 'sendWithoutName')
   .addToUi();
};

function setTwilioAccountSID(){
 var scriptValue = ui.prompt('Enter your Twilio Account SID' , ui.ButtonSet.OK);
 userProperties.setProperty('TWILIO_ACCOUNT_SID', scriptValue.getResponseText());
};

function setTwilioAuthToken(){
 var scriptValue = ui.prompt('Enter your Twilio Auth Token' , ui.ButtonSet.OK);
 userProperties.setProperty('TWILIO_AUTH_TOKEN', scriptValue.getResponseText());
};

function setTwilioPhoneNumber(){
 var scriptValue = ui.prompt('Enter your Twilio phone number in this format: +12345678900' , ui.ButtonSet.OK);
 userProperties.setProperty('TWILIO_PHONE_NUMBER', scriptValue.getResponseText());
};

function deleteTwilioAccountSID(){
 userProperties.deleteProperty('TWILIO_ACCOUNT_SID');
};

function deleteTwilioAuthToken(){
 userProperties.deleteProperty('TWILIO_AUTH_TOKEN');
};

function deleteTwilioPhoneNumber(){
 userProperties.deleteProperty('TWILIO_PHONE_NUMBER');
};  //the above functions are for adding and deleting the API creds for temp storage 

function sendSms(customerPhoneNumber, customerFirstName, customerStatus) {
 var twilioAccountSID = userProperties.getProperty('TWILIO_ACCOUNT_SID');
 var twilioAuthToken = userProperties.getProperty('TWILIO_AUTH_TOKEN');
 var twilioPhoneNumber = userProperties.getProperty('TWILIO_PHONE_NUMBER');
  var twilioUrl = '<https://api.twilio.com/2010-04-01/Accounts/>' + twilioAccountSID + '/Messages.json';
 var authenticationString = twilioAccountSID + ':' + twilioAuthToken;
 try {
   UrlFetchApp.fetch(twilioUrl, {
     method: 'post',
     headers: {
       Authorization: 'Basic ' + Utilities.base64Encode(authenticationString)
     },
     payload: {
       To: "+" + customerPhoneNumber.toString(),
       Body: "Hi, " + customerFirstName + ", this is Sponge Bob! Thanks for requesting more info about building your cartoon brand.",
       From: twilioPhoneNumber,  // Your Twilio phone number
     },
   });
   return 'sent: ' + new Date();
 } catch (err) {
   return 'error: ' + err;
 }
};

function sendSms2(customerPhoneNumber, customerFirstName, customerStatus) {
 var twilioAccountSID = userProperties.getProperty('TWILIO_ACCOUNT_SID');
 var twilioAuthToken = userProperties.getProperty('TWILIO_AUTH_TOKEN');
 var twilioPhoneNumber = userProperties.getProperty('TWILIO_PHONE_NUMBER');
  var twilioUrl = '<https://api.twilio.com/2010-04-01/Accounts/>' + twilioAccountSID + '/Messages.json';
 var authenticationString = twilioAccountSID + ':' + twilioAuthToken;
 try {
   UrlFetchApp.fetch(twilioUrl, {
     method: 'post',
     headers: {
       Authorization: 'Basic ' + Utilities.base64Encode(authenticationString)
     },
     payload: {
       To: "+" + customerPhoneNumber.toString(),
       Body: "Hi, this is Sponge Bob! Thanks for requesting more info about building your cartoon brand.",
       From: twilioPhoneNumber,  // Your Twilio phone number
     },
   });
   return 'sent: ' + new Date();
 } catch (err) {
   return 'error: ' + err;
 }
};

//SEND wiht the customer first name
function sendWithName() {
 var sheet = SpreadsheetApp.getActiveSheet();
 var rows = sheet.getDataRange().getValues();
 var headers = rows.shift();
 
rows.forEach(function(row) {
  if (row[MESSAGE_STATUS] === '') {
    row[MESSAGE_STATUS] = sendSms(row[CUSTOMER_PHONE_NUMBER], row[CUSTOMER_FIRST_NAME]);
  }
});
sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);
};

//SEND wihtout using the customer first name
function sendWithoutName() {
 var sheet = SpreadsheetApp.getActiveSheet();
 var rows = sheet.getDataRange().getValues();
 var headers = rows.shift();
 
rows.forEach(function(row) {
  if (row[MESSAGE_STATUS] === '') {
    row[MESSAGE_STATUS] = sendSms2(row[CUSTOMER_PHONE_NUMBER]);
  }
});
sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);

};

To customize the message that will be sent, edit the "**Hi**, " + customerFirstName + ", this is Sponge Bob! Thanks for requesting more info about building your cartoon brand." line of code for sendWithName() function or "**Hi, this is Sponge Bob! Thanks for requesting more info about building your cartoon brand.**" line of code for sendWithoutName() function.

<aside> ℹ️ Only edit the text in RED listed above. Certain characters, like quotes and plus signs, are needed for code to properly execute

</aside>

Save the code by clicking on File > Save and name it something like, “Send SMS”
On the upper navigation bar in Apps Script, select the function onOpen.
From the Run menu, select onOpen.
Grant permissions for the script to run, if prompted.