Sending Messages with Google Sheets

Hi there!

Would you like to send a bulk of personalized Viber messages using nothing but Google Sheets? 

In this tutorial, you will learn how to send Viber messages from Google Sheets tables, which will hold personalization parameters, in just four steps. This integration will allow you to send bulk Viber messages using the tyntec API and Google Sheets. 

You will need

  • Your tyntec API Key from the Business Center
  • Your Viber Service ID from tyntec
  • A Google Account
  • A mobile phone with the Viber application and a working phone number

First, you need to make a table with the data of the messages you want to send in a batch. Then you’ll write a simple script, which will send these messages using tyntec’s API.

Note: You may also use the SMS channel of the tyntec Conversations API as a fallback for Viber plain text messages. Information on Conversations channels can be found in the API reference or contact us for more information.

Important: All phone numbers must be in the international format. Leading + or 00 may be omitted.

Step One: Create a table in Google Sheets

To start sending messages from your Spreadsheet, you need to prepare your Spreadsheet beforehand. 

1. Go to Google Sheets and create a new table. It is a good idea to name it something like Viber messages.

2. In this demo, you will use the following parameters:

  • The phone number (All phone numbers must be in the international format. Leading 00 should be excluded.)
  • The first parameter (in this case, the name of your customer)
  • The second parameter (in this case, a date of an appointment)
  • The third parameter (in this case, a time of an appointment)
  • Result (the final script below will add the result here)
  • Info (the final script below will add extra result info here)

3. Add your values for parameters. Your table can look like this:

Ready Sheet

The resulting message for the first data row will read:

“Dear Customer A, your appointment is on April 1 at 3:00 PM”

Step Two: Create a script to send a testing message

You can start by sending a single message through the tyntec Conversations API to try out the functionality of the API. 

1. Open the Script Editor from the toolbar menu Tools > Script Editor.

Script Editor

Note: The editor will suggest a name for your new function. You may rename it.

2. Now you will populate the function with some code. Let’s make two constants which will contain request headers and body.

const headers = {
    'Content-Type':'application/json',
    'Accept':'application/json',
    'apikey':'YOUR_API_KEY'
  };

Put your tyntec API Key in the apikey variable

const inputBody = {    
  "to": 'Phonenumber',
  "channels": ["viber"],    
  "viber": {     
    "from": 'YOUR_VIBER_SERVICE_ID',
    "messagePurpose":"promotion",
    "components": [
      {
        "type": "text", 
        "text": 'Your message'
      }
    ]
  }
}
;
  var body = JSON.stringify(inputBody);

Update the ‘to’ variable with the phone number of your testing recipient and the ‘from’ value with your Viber Service ID.

3. With your constants ready, you will add the fetch() method. The fetch() allows your code to send your constants to the Conversations API. Since you’re going to use the Google Sheets table to log the results, you must use the fetch() method from Google Apps Script. Copy the following sample and paste it at the end of your function:

UrlFetchApp.fetch('https://api.tyntec.com/chat-api/v2/messages',
        {
            method: 'POST',
            payload: body,
            headers: headers,
            contentType: "application/json"
        });

4. With fetch() ready, you can test your script. Save the script, and run it by pressing the play button, as shown below.

Run script

You will be asked to review Google permissions – allow the script to connect to an external service and the script will continue.

Voila! Take a look at your phone, you will have a new Viber message!

Viber Message

Step Three: Change the Google Script to send bulk messages

Now you will update the script with functionality to get all data from your Spreadsheet and send a Viber message to each contact.

NOTE: You are going to use some functions from the Google Apps Spreadsheet service. You can copy code samples from this tutorial or have a look at the Spreadsheets Apps Script documentation.

1. You need to set variables that get data from your Spreadsheet. Copy the following code:

var spreadSheet = SpreadsheetApp.openByUrl(SpreadsheetApp.getActiveSpreadsheet().getUrl());
	    var sheet = spreadSheet.getSheets()[0];
            var rangeValues = sheet.getRange(2, 1, sheet.getLastRow() - 1, 5).getValues();

Note: If you don't use headers in your table, change the first argument of the getRange() method to the number of your first row.

2. After setting the variables, you need to wrap the logic in a loop to iterate over all values in the selected range, like this:  

for (var i in rangeValues) {
  const inputBody = { // ...
}

The loop must be before the inputBody definition and have the fetch() method on end.

3. With a loop in place, you want to automatically update phone numbers and text with data from your Spreadsheet table. Update ‘to’ and ‘text’ members with your appropriate range coordinates, such as:

‘to’: rangeValues[i][0],
        ‘text’: "Dear" + rangeValues[i][1] + ...
  • ‘to’ will get values from the first column,
  • the first parameter from the second column and so on.

4. It is good programming practice to log the results of your messages. To log the results, you need to select the cells to be updated, wrap your fetch() method with a try/catch to set the values based on the success or failure of your request.

Before the fetch() method, add variables with the getRange() method for selecting table cells that log the results.

Before the fetch() method, add variables with getRange() method for selecting table cells that will be used to log the results.

var result = sheet.getRange(2 + Number(i), 5);
var info = sheet.getRange(2 + Number(i), 6);

If you’re using more than two parameters, you need to increase the column numbers to match the Result and Info columns in your table, which are 5 and 6 in our example.

Wrap the fetch() method with the try/catch block, and add setValue() methods to log.

try {
      UrlFetchApp.fetch('https://api.tyntec.com/chat-api/v2/messages',
        {
          method: 'POST',
          payload: body,
          headers: headers
        });
 
      result.setValue('SENT').setBackground('#93c47d');
      info.setValue('Sent on ' + new Date());
    } catch (err) {
      result.setValue('FAILED').setBackground('#e06666');
      info.setValue(String(err).replace('\n', ''));
    }

5. Your final code should look like the following.

function Viber() {
 
var spreadSheet = SpreadsheetApp.openByUrl(SpreadsheetApp.getActiveSpreadsheet().getUrl());
var sheet = spreadSheet.getSheets()[0];
var rangeValues = sheet.getRange(2, 1, sheet.getLastRow() - 1, 5).getValues();
 
 
   const headers = {
    'Content-Type':'application/json',
    'Accept':'application/json',
    'apikey':'YOUR_API_KEY',
  };
 
 
for (var i in rangeValues) {
  const inputBody = {    
  "to": rangeValues[i][0],
  "channels": ["viber"],    
  "viber": {     
    "from": 'YOUR_VIBER_SERVICE_ID',      
    "components": [
      {
        "type": "text", 
        "text": "Dear "+ rangeValues[i][1] +", your appointment is on  "+ rangeValues[i][2] + "at " + rangeValues[i][3],
      }
    ]
  }
}
var result = sheet.getRange(2 + Number(i), 5);
var info = sheet.getRange(2 + Number(i), 6);
var body = JSON.stringify(inputBody);      
 
 try {
  UrlFetchApp.fetch('https://api.tyntec.com/chat-api/v2/messages',
        {
            method: 'POST',
            payload: body,
            headers: headers,
            contentType: "application/json"
        })
  result.setValue('SENT').setBackground('#93c47d');
  info.setValue('Sent on ' + new Date());;
}
      catch (err) {
      result.setValue('FAILED').setBackground('#e06666');
      info.setValue(String(err).replace('\n', ''));
      }
   }
}

Step Four: Run your script

Press the play button to run the script, and review Google permissions once more (this time to edit values in your Google Spreadsheet). You can now take a cup of coffee. All the contacts have received your message. You will see a confirmation at your phone notification bar. 

If you check the Spreadsheet table, you will see logs in Results and Info columns. It should look like this:

Bulk Messaging results

Congratulations on your working script for sending bulk messages from Google Sheets

More?

You can find some inspiration in the functionality of WhatsApp Business API | Sending Messages with Google Sheets which uses a similar script to send WhatsApp messages from your Spreadsheet. The difference between those scripts is in the used channel (look at tyntec's Conversations API for a reference).

You could also extend this script to send bulk messages to various channels. For that you would need to:

  • Add a column defining which channel will be used (SMS, WhatsApp, Viber)
  • Add If (or Switch case) to select a channel based on the column
  • Define a second request body for WhatsApp channel using a pre-approved template and a third request body for SMS message