46elks
46elks
Feb 10, 2017 · 5 min read

You have a Google spreadsheet containing the names and phone numbers of people and would like to send them a text message (SMS).

This tutorial assumes no prior knowledge of programming or custom telephony. If you like to code we highly recommend that you check out our API over at 46elks.com.

1. Create a 46elks account

First off, you’re going to register an account at 46elks. 46elks lets your applications send and receive SMS, MMS and voice calls over the internet . We’ll be using the SMS features in this tutorial.

Once you’ve created your account you’ll be sent to the Dashboard. Leave it open, you’ll need it later.

2. Create a Google spreadsheet

Now go ahead and create a new Google spreadsheet. Name it sms_contacts and add headings Name, Number, Message, State and Info to the first five columns.

3. Add some contacts

Now let’s populate it with a few people we want to send messages to. Note that the phone number needs to be in international format. This is easy enough to fix if you have your numbers in a local format, just:

  • Remove any spaces and parenthesis
  • Remove any leading zeros
  • Prepend your country code to the number

For example, a local US number such as (216) 208–0459 would be written as 12162080459. A Swedish mobile number such as 076 686 1004 would be formatted as 46766861004.

The message
You can set this to anything you want, of course, but since we have the names it would be nice to include them in the message. One way of doing that is to paste the code below into all of your “Message” cells:

=CONCATENATE("Hi ", INDIRECT("R[0]C[-2]", false), "! Thanks for coming to the event last night!")
All messages are generated by the same formula but interpolate different names

4. Add a Google script to the spreadsheet

Now we’re going to connect the spreadsheet to your 46elks account via Google Scripts. Create a new script from your spreadsheet by selecting Tools > Script editor… in the toolbar. You should be met with a new page that looks something like this:

Replace everything in the editor with the following code:

function sendElksSMS() {
// Get the properties
var prop = PropertiesService.getScriptProperties();
var username = prop.getProperty('46elksUsername');
var password = prop.getProperty('46elksPassword');
var spreadsheetUrl = prop.getProperty('spreadsheetUrl');
var sender = prop.getProperty('46elksSender');
var auth = Utilities.base64Encode(username + ":" + password);

// Open the requested SpreadSheet and Sheet
var spreadSheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
var sheet = spreadSheet.getSheets()[0];
var range = sheet.getRange(2, 1, sheet.getLastRow() - 1, 5).getValues();

// Loop over all rows in the range and send the SMS
for (var i in range) {
if (range[i][1] == '' || range[i][3] == 'SENT') continue;
var state = sheet.getRange(2+Number(i), 4);
var info = sheet.getRange(2+Number(i), 5);
try {
UrlFetchApp.fetch("https://api.46elks.com/a1/SMS", {
"method": "post",
"headers": { "Authorization": "Basic " + auth },
"payload": {
"from" : sender,
"to" : '+' + range[i][1],
"message" : range[i][2]
}
});
state.setValue('SENT').setBackground('#93c47d');
info.setValue('Sent on ' + new Date());
} catch (err) {
state.setValue('FAILED').setBackground('#e06666');
info.setValue(String(err).replace('\n', ''));
}
}
}

We now need to tell the script which spreadsheet it should work with. Open your spreadsheet again and click on the address bar.

The address bar

The highlighted text in the image below is theURL of your spreadsheet. This is what the script needs in order to figure out which spreadsheet to work with. Copy everything up until #gid.

The spreadsheet URL. Every spreadsheet has a unique URL so yours will be different.

Switch back to the script editor. Go to File > Project Properties. If you’re asked to choose a project name, call it anything you like. Next, choose the “Script properties” tab in the pop-up menu. Add a new row. Set the property to be speadsheetUrl and the value to be the ID you copied from the address bar. You should end up with something like this:

Next you’re going to need to enter your 46elks details into the same script properties popup. Open the Dashboard. On the right of the page there’s a box containing your API username and password. The password is blurred out but reveals itself if you hover over it.

Now you’re going to enter three new rows into the “Script properties” box: One for your API username, one for your API password and one for the phone number you to used register your 46elks account. The keys must be called exactly 46elksUsername, 46elksPassword and 46elksSender.

Save the changes.

Change script to sendElksSMS and click Run.

You’ve now sent text messages from inside Google Spreadsheets!

Let us know (help@46elks.com) if we can help you, if perhaps there is anything unclear in this tutorial, or if you’d like for us to write another one.

More parts of this mini series on Google Spreadsheets and SMS:
Part 1: Receive SMS from Google Spreadsheet
Part 3: Schedule SMS in Google Spreadsheet

// 46elks team

P.S. This post was updated in the summer of 2019 to keep up with changes in Google Sheets and to make it easier to use.

46 thoughts

The 46elks blog

46elks

Written by

46elks

Voice, SMS & MMS in one easy API.

46 thoughts

The 46elks blog

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade