Send Messages in Bulk Using Google Sheets with the Telnyx API
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
Telnyx Account (https://telnyx.com/sign-up)
Access to Google Sheets (https://www.google.com/sheets/about/)
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
Set up a developer account with Telnyx from https://telnyx.com/sign-up
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 Profileto the profile you created in the previous step.
What if the Telnyx number is an international number for a UserIf 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!