Send SMS from Google Spreadsheets

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 Extensions > App script… 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 find out what the spreadsheet is called and copy it (In a new spreadsheet it’s called “Sheet1”).

Switch back to the script editor. Go to Extensions > App script. If you’re asked to choose a project name, call it anything you like. Next, choose the “Project settings” tab in the menu to the left. Scroll down until you find “Script Properties” Add this script property: 46elksSender to the property to be and the name of who is sending the SMS. For my case its going to be 46elks.

Next you’re going to need to enter your 46elks details into the same script properties settings. Go to 46elks.com and 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 two new rows into the “Script properties” box: One for your API username, one for your API password. The keys must be called exactly 46elksUsername, 46elksPassword.

For United States, you need to write noreply as the Value for 46elksSender instead of the phone number you registered your account with.

It should look something like this:

Save the script properties and go back to the editor and click save project.

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.

Please be advised that Google has a limitation on how long a script is allowed to run: “maximum execution time”. It depends on which kind of Google account you use, the lowest time allows a script to run for 6 minutes. If you plan to send or receive a large amount of SMS at the same time, please contact 46elks support on +46766861004 or help@46elks.com.

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 fall of 2023 to keep up with changes in Google Sheets and to make it easier to use.