article

vlad avatar image
vlad posted

Send Messages in Bulk Using Google Sheets with the Telnyx API

GoogleSheetsMessaging.png

Introduction


The most straightforward way to catalogue people, things, and events is by the use of spreadsheets. If you start a business or a project, chances are you won't be wanting to invest initially in highly structured databases or spend time creating robust resources to keep track of your relatively small project base.

That's where this tutorial comes in: together we will be forming a basic application that will mass send messages from a Google Sheets spreadsheet with just one run input. No need for external library setup or convoluted implementations! Everything can be done all from one source.


Prerequisites

Step 1 : Telnyx setup

You need to sign up for a Telnyx account to acquire a Telnyx phone number and API key. Then configure the number to have SMS capabilities.

  • Sign up for Telnyx account

  • Obtain a number with SMS capabilities for the app

    • After creating an account and signing in, you need to acquire a number for the application. Search for a number by selecting your preferred 'Region' or 'Area Code'.

    • Make sure that the number supports SMS feature(Very Important!) as it will be used by our application.

  • Create a messaging profile

    • Next create a messaging profile by clicking on "Add new profile" and provide a suitable profile name to it(you do not need to provide any other detail for now).

  • Configure the number for messaging

    • Go to the numbers page, look for the number you created and set the number's Messaging Profile to the profile you created in the previous step.


      What if the Telnyx number is an international number for a User

      • If you want to send the message to a phone number that is not in the country where you are, then you need to click on the 'Routing' option.

      • After clicking on 'Routing', a dialog box will open. In there, select the traffic type as "P2P" to allow International Inbound and Outbound SMS deliverability. And do not forget to save the changes!

  • Acquire Telnyx API key

    • Go to the API Keys page and copy the API Key for the future steps. Incase there is no API Key, then create one.

You now should now have a Telnyx Phone number and an API key. Take note of both of these.

Step 2 : Google Sheets Setup

Open up a fresh Google Sheets page. In here we will construct 3 columns:

  • Destination Phone Number

  • Message Text Body

  • Status of Message

It will look something like this:

DO NOTE

You can expand with more options as you please, just for the sake of the tutorial we will only be going through those three listed above.

Afterwords, click on extensions then Apps Script:

Here we will be adding a new Script

Paste the following code into the new script that we created:

 API_KEY = ""
 fromNumber = ""
    
 function sendTelnyxMessage(to, body) {
   var data = {
   'to': to,
   'from': fromNumber,
   'text': body
   };
    
   var options = {
     'method' : 'post',
     'payload' : JSON.stringify(data)
   };
    
   options.headers = {
     'Content-Type': 'application/json',
     "Accept": "application.json",
     "Authorization": "Bearer " + API_KEY
   }
    
   UrlFetchApp.fetch('https://api.telnyx.com/v2/messages', options);
 }
    
 function sendFromSheets(){
   var sheet = SpreadsheetApp.getActiveSheet();
   var startRow = 2;
   var numRows = sheet.getLastRow() - 1;
   var dataRange = sheet.getRange(startRow, 1, numRows, 2);
   var data = dataRange.getValues();
    
   for (i in data){
     var row = data[i];
     try {
       response_data = sendTelnyxMessage("+" + row[0], row[1]);
       status = "Sent Sucessfully";
     } catch(err) {
       Logger.log(err);
       status = "Error Sending";
     }
     sheet.getRange(startRow + Number(i), 3).setValue(status);
   }
 }
    
 function sendAll(){
   sendFromSheets();
 }

Fill in the API_KEY and phoneNumber variables from above!
Take note of the name of the 3rd function we created: sendAll(). We will be using this for the next step. Now we should be all set from the code side.

Let's go back to our spreadsheet. To enact the function to send all in a relatively pain-free way, we should create a button. Thankfully Google Sheets makes this process incredibly simple!

Click on insert drawing at the top of the page.

In this menus we can use our creativity to create a good looking button, but in developer fashion for right now we will just make a textbox:

The button will be posted on our spreadsheet. Let's add the function to it:

We want to assign it to the function that we took note of above to it:

And that's it!

Let's test our app. Once you click on the newly created button, it should go through your list and columns until it reaches the end and send texts. It will also update the status column and inform you if the messages were delivered or not! Here's my example below:

Step 3 : Next Steps

From here, you have a good base to work with. We've created a simple spreadsheet application that goes through the 1st column of phone numbers and sends the text bodies of the 2nd column while giving status updates to the 3rd.

We can start expanding this for future business needs. Add more things to track like sent/received statuses, order fulfillments, you can even add parsing ability to the texts you receive and highlight messages based on the context of the text message. The possibilities are limitless!

messaginggoogle-sheets
8 comments
10 |600

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

klane@telnyx.com avatar image klane@telnyx.com ♦♦ commented ·

Can this also be used for MMS?

1 Like 1 ·
klane@telnyx.com avatar image klane@telnyx.com ♦♦ klane@telnyx.com ♦♦ commented ·

Yes, we can use it for MMS but we need to add one more column(for media_url) in spreadsheet and one more parameter in function(media_url) to be able to use it for MMS. If you need help with this process please post here on the community site and our developer team can help!

1 Like 1 ·
caketelecom avatar image caketelecom klane@telnyx.com ♦♦ commented ·

This has worked great, could someone explain the process for adding media_url function for MMS please? thank you

0 Likes 0 ·
Show more comments
klane@telnyx.com avatar image klane@telnyx.com ♦♦ commented ·

One customer got an error until removing the leading + from the destination number format, so if you are stuck that is one thing to try!

0 Likes 0 ·
klane@telnyx.com avatar image klane@telnyx.com ♦♦ klane@telnyx.com ♦♦ commented ·

Also, one customer was stuck until hitting the "run" button on the script page. Please don't forget to erase the default script that is the script page when you first open it before you copy in the script from the tutorial.

0 Likes 0 ·

Manage Your Content

Article

Contributors

michaelp contributed to this article vlad contributed to this article