Sending numbers directly from Google Sheets

In a previous guide, we explained how to send a message from a Google Sheet, using the C# SDK. 

This is useful if you are building custom software to use a shared spreadsheet document. However, for simpler cases where you just want to send multiple text messages using spreadsheet data, using an SDK may be overkill. Thankfully, it’s possible to integrate with the MessageMedia API directly from Google Sheets, using App Scripts. 

Google App Scripts enable you to customise your G-Suite documents using JavaScript. You don’t have to install anything and it operates on Google servers. There are libraries that enable you to interact with Google web services, as well as regular networking APIs, that allow you to interact with external services (such as MessageMedia Messaging APIs). This is what we will set up today. 

Getting started

The first this you need to do is create a new Google Sheets Spreadsheet. In a single column, add a list of numbers you want to send messages. You can also include any other relevant data, such as information to feed into the message body. For this example, the spreadsheet looks like this:

To create a script in the Google Sheets menu, go to Tool>Script editor. Now, in the script editor, clear any existing functions and create a new function, “sendMessages()”. In the body of this function, we can start to create an API call.The first part of calling the MessageMedia API, will be to set up the headers. We need to do some work to encode the API credentials correctly, since we're not using an SDK to do this for us. Get your API Key and Secret, and sub your credentials in this code: 

  //set up the headers
  var url = "https://api.messagemedia.com/v1/messages";

  var username = "YOUR API KEY";
  var password = "YOUR SECRET";

  var plaintext = username + ':' + password;
  var encoded = Utilities.base64Encode(plaintext);
  var header = "Basic " + encoded;

  var headers = {

    'Authorization': header, 
    'Content-Type': 'application/json'

  };

The next part of the API call will be to access the data. In this example we are assuming that you are accessing the active spreadsheet (as you will be triggering the function from a button in that sheet):

 //set up the messages
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var toNumber = null;
    var name = null;
  var time = null;
  var message = null;
  var messagesarray = [];

  // loop through all the contacts in the Contacts sheet
  var data = ss.getDataRange().getValues();

  //start at 1 to acocunt for header row
  for (var i = 1; i < data.length; i++) {
    toNumber = data[i][2];
    name = data[i][1];
    time = data[i][3];
    message = "Hi " + name + " your shift starts at " + time; 

    var item = {
            "content": message,
            "destination_number": toNumber,
            "delivery_report": true,
            "format": "SMS",
        }
    messagesarray.push(item)
  }

 var content = { "messages" : messagesarray }
 var payload = JSON.stringify(content);

Now we have the header and the payload, and we can make a full API call:

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

  var result = JSON.parse(UrlFetchApp.fetch(url, options).getContentText());
  Logger.log(result);

This is all you need to make an API call and you can simply edit the message content to your requirements. Now we can make a way to trigger this function.

Finishing touches

In your spreadsheet, insert an image to the side of your data. I found a big “SEND” button that I have used for my example. When you have inserted the image and you hover over it there will be three dots in the top right corner, indicating the presence of a menu. Open this menu and select “Assign Script”. In the box that opens, type the name of your function, with no brackets, i.e. “sendMessages”. Now you have absolutely everything set up and ready to go. Click on your send button, and it will send out all the messages.

Wrapping up

I’ve set up a fully working version of the spreadsheet here. You can duplicate this spreadsheet and follow the instructions to update it with your own credentials. Some possible ways to use this include; 

• Messaging all of your event attendees about last minute changes to the venue
• Messaging everyone in your futsal group about the draw

 In particular, using a Google Sheet is really useful for marketing messaging where you’re not sending messages directly from your CRM. You can copy paste data from your customer sign-up list, and send directly from your Google Sheet.