image-description
Return to Blog

SMS Merge with Google Sheets and Plivo

, Written by

Sending personalized SMS messages to your loyal customers right from Google Sheets just got a whole lot easier.

We at Plivo have developed something to get you started with your very own SMS automation power sheet! In this article, we’ll walk you through how we leveraged Google Scripts to create an ‘SMS Merge’ feature for Google Sheets.

But before we get into that, let’s go over some prerequisites.

Prerequisites

In order to use Plivo’s messaging script, you should have an active Plivo account. Setting up a new Plivo account takes less than a minute.

You will find your Plivo API auth id and auth token on your Plivo dashboard. Keep these handly as they’re required to set up the messaging script.

Find your auth id and auth token

Next, rent an SMS enabled phone number from the Numbers section on your Plivo dashboard. This is the number you would use to send messages to your customers.

If you are not sure how to buy a phone number please go through this link

If you haven’t registered yet, get your plivo account by clicking on sign up.


Now that you’ve set up your Plivo account, let’s get down to business! Follow these simple steps to generate your very own SMS Merge Google Sheet.

Steps to use

1. Create a new google sheet. Give it a name of your liking.
2. Open script editor

Go to script editor

3. Copy the code from https://github.com/plivo-dev/google-sheet-messaging-script

Paste the code

4. Put your Plivo AUTH_ID and AUTH_TOKEN in the place specified.

Fill your credentials

5. Save the Google Script. Give it a name of your liking.

Save the code

6. Reload the Google Sheet.

Data sheet

On reloading your Google Sheet, you should see two sub sheets titled ‘Data’ and ‘Template’. The ‘Data’ sheet will have few columns pre-created. The highlighted columns are meta columns which will be populated with the details of the send message API response for that specific row.


Template

‘Template’ sheet will have the SMS template in the ‘A2’ cell (orange). You will be able to update the template as per your specific use case. We will go into more details on templates in the following sections.

7. Add custom columns.

Template

Custom columns can serve as placeholders in your message template. Such placeholders are dynamically replaced with values found in custom columns with the same name. You can add as many custom columns as you wish. This is at the core of the SMS Merge functionality.

8. Edit template.

Template

Use the names of the custom columns created in the previous step as placeholders in your template.

For example, in the image above, NAME, COUPON_CODE, DISCOUNT, STORE serve as placeholders, the actual values of which would be picked up from columns in the data sheet with the same name.

Note that placeholders should be enclosed in {{ and }}.

9. Validate SMS template.

Template validation

Errors in the message template, if any, will be highlighted on clicking this button.

10. Put data in the sheet.

Put your data

Make sure to enter values for all custom columns that are part of your SMS template.

11. Send messages.

Send messages


The code behind the scene

Now that we are familiar with how to set up and use our Plivo powered SMS Merge sheet, let’s dive into the code. We will look at some important functions that are responsible for making api calls to Plivo to send the SMS.

Sending Messages

sendMessages(data) : this function takes a list of row data objects and processes each object one by one. This is how our sendMessages function looks like:-

function sendMessages(data){
  var success = 0;
  var failure = 0;
  var TOKEN = Utilities.base64Encode(AUTH_ID+":"+AUTH_TOKEN);
  for(i=0;i<data.length;i++){
    var row = data[i];
    var tempObj = {
        "src":row['SOURCE'],
        "dst":row['DESTINATION'],
        "text":createMessage(row,template),
    }
    var delivered = trySMS(tempObj,row.row,AUTH_ID,TOKEN);
    delivered?success++:failure++;
  }
  popupAlert("FINAL REPORT : \n\n"+(success+failure)+" row(s) processed \n "+success+" row(s) executed successfully \n "+failure+" row(s) encountered error \n For further details please check api details or each row in the sheet. ",false);
  unhideMetaColumns();
}


Let’s talk about this function line by line :-

var TOKEN = Utilities.base64Encode(AUTH_ID+":"+AUTH_TOKEN);

The above line creates a base64 encoded token which is used to authorize Plivo api requests. This key will be sent with every api call in headers.

for(i=0;i<data.length;i++){
  var row = data[i];
  var tempObj = {
      "src":row['SOURCE'],
      "dst":row['DESTINATION'],
      "text":createMessage(row,template),
  }
  var delivered = trySMS(tempObj,row.row,AUTH_ID,TOKEN);
  delivered?success++:failure++;
}

This for loop iterates over the list of objects and does the following opertions on it :-

  1. Creates a temporary message object containing the source number, destination number and message text. The message text is generated by createMessage function that merges the template with the placeholders. We will look at the createMessage(row,template) function later in this section.
  2. Attempts to deliver the message via Plivo API.
  3. Tracks the success and failure counts.


Now that we have success and failure counts, we simply show the report using the following line.

popupAlert("FINAL REPORT : \n\n"+(success+failure)+" row(s) processed \n "+success+" row(s) executed successfully \n "+failure+" row(s) encountered error \n For further details please check api details or each row in the sheet. ",false);


Now that we are well aware with the sendMessages function, let’s take a look at the createMessage() function.

Creating message

function createMessage(data,template_data){
  for (var key in data) {
    if (data.hasOwnProperty(key)) {
        template_data = template_data.replace(new RegExp('+key+', 'gi'),data[key]);
    }
  }
  return template_data;
}

This function iterates over all custom headers. If the custom header is found in the message template then that placeholder is replaced with the actual value.

At the end it returns the final message. Let’s see how it works using an example.

Suppose we call createMessage(data,template_data) where data and template_data are as follows :-

data = {
    "SOURCE" : "+1234567890",
    "DESTINATION" : "+2345678901",
    "FIRST_NAME" : "Jane",
    "LAST_NAME" : "Doe",
    "COUPON" : "DUMMY20",
    "STORE" : "PLIVO",
    "DISCOUNT" : "20",
}

template_data = "Hi   , your coupon code for discount of % purchase at  is "


Then createMessage(data,template_data) will return text

Hi Jane Doe , your coupon code for discount of 20% purchase at PLIVO is DUMMY20


API call to send Message

Making an api call for Sending Messages : The trySMS function takes four params - task which is the temporary object created by sendMessages(), row the row number for which the function is to run, AUTH_ID your plivo account auth id, TOKEN the token created to authorize your api call.

function trySMS(task,row,AUTH_ID,TOKEN){
   var options = {
    'method' : 'post',
    'contentType': 'application/json',
    'headers':{
      Authorization:"Basic "+ TOKEN
    },
    'muteHttpExceptions':true,
    'payload' : JSON.stringify(task)
  };
  response = UrlFetchApp.fetch('https://api.plivo.com/v1/Account/'+AUTH_ID+'/Message/', options);
  return setStatus(response,row);
}

The very first line creates an api call object options. This object contains all required http parameters to make the API call to Plivo.

UrlFetchApp.fetch is google’s pre-defined function to make api calls. This function returns the http response.

Google sheets + Google scripts is an awesome combination. One that we often use to automate our tasks and workflows at Plivo. We hope you find our SMS sender for Google Sheets useful :)


Check out our code on Github


comments powered by Disqus