Building a Twilio-to-Spreadsheets Notification System

Time to read: 8 minutes

October 25, 2024

Written by

Samuel Busayo

Contributor

Reviewed by

Dhruv Patel

Twilion

Article: Building a Twilio-to-Spreadsheets Notification System

Integrating a notification system into your spreadsheet is very useful when dealing with dynamic data that changes frequently. How do you keep track of these changes, particularly when certain updates require immediate action? Having a notification system that allows you to watch for breaking changes in your data and increase response time makes a lot of difference in your workflow.

This article will guide you through writing a few scripts to build an automated notification system for Microsoft Excel and Google Sheets. Using Twilio Programmable Messagingand Twilio SendGrid, this system will alert you when specific, predefined metrics in your data change. Let's get right into it!

Prerequisites

1. Spreadsheet Software: You'll need one of the following:

Google Account: For accessing Google Sheets.
Microsoft Excel: Installed on your Windows computer.

2. Twilio Account: Sign up for a Twilio account if you haven't already. You'll need this to use the Programmable Messaging and SendGrid services.

Twilio phone number: Get a Twilio phone number for sending SMS notifications.
SendGrid API key: Set up a SendGrid account through Twilio and generate an API key for sending emails.

3. Basic programming knowledge:

For Google Sheets: Familiarity with JavaScript for Google Apps Script.
For Excel: Familiarity with Visual Basic for Applications (VBA).

Overview

The spreadsheet sample data that will be used for this project is a basic sales inventory table that includes Total Sales as part of the columns. The script will focus on checking if total sales exceed a predetermined threshold. Each time someone edits the Total Sales column, the script runs. When the data surpasses the threshold, it triggers an email notification through Twilio SendGrid and an SMS notification through Twilio Programmable Messaging.

You can copy the table provided here or use any dataset that suits your needs. Keep in mind that you can adapt the principles outlined here to different use cases.

Building a Notification System in Microsoft Excel

Excel allows developers to write code to automate tasks and build personalized solutions for specific use cases in the spreadsheet using VBA (Visual Basic for Applications). Before writing any code, ensure that your Excel application has enabled the developer’s tab.

To enable the developer’s tab, open Excel on your desktop; at the top of the spreadsheet, click on File, then select Options at the end of the sidebar. In the Excel Options dialog box, select Customize Ribbon. Check the Developer option in the right pane and click OK to close the modal.

Add SMS API Module

To start adding code to your spreadsheet, click on the Developer tab at the top of the spreadsheet. Then, click on Visual Basic or press Alt + F11 to open the VBA editor. In the VBA editor, click Insert at the top of the page, then select Module from the dialog options. This will create a new module where you can write your VBA code. From the sidebar, double-click on Module 1 to open the editor.

Modules allow you to organize your code into manageable sections that you can reuse in other modules. Add the code below to the module editor:

Private accountSID As String
Private authToken As String
Private fromNumber As String
Private toNumber As String
Private Sub SetTwilioCredentials()
    ' Set Twilio credentials here
    accountSID = "your twilio SID"
    authToken = "your twilio auth token"
    fromNumber = "your twilio number"
    toNumber = "receiver number"
End Sub

The code above initializes private variables needed in the subroutine. The variables are tagged as private to prevent external access by other modules.

Next, you’ll add a subroutine called SendTwilioSMS. As its name implies, it sends an SMS using the Twilio SMS API. It takes one parameter: message, which is the SMS content.

Sub SendTwilioSMS(message As String)
    Dim http As Object
    Dim url As String
    ' Set credentials before sending SMS
    SetTwilioCredentials
    ' Create the URL for the request
    url = "https://api.twilio.com/2010-04-01/Accounts/" & accountSID & "/Messages.json"
    ' Create the HTTP request
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "POST", url, False, accountSID, authToken
    http.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    http.send "From=" & fromNumber & "&To=" & toNumber & "&Body=" & message
    ' Clean up
    Set http = Nothing
End Sub

The code sets up Twilio credentials (Account SID, Auth Token, and phone numbers) inside the HTTP request. To get these credentials, navigate to your Twilio Console. The http.send method sends the required parameters (sender number, recipient number, and message body) to the SMS API. Finally, http is set to Nothing to release the memory allocated to the HTTP object.

Add Email API Module

Similar to how the SMS API was added, create a new module and add the following code to the editor:

Option Explicit
' SendGrid API Key (replace with your actual API key)
Private Const SENDGRID_API_KEY As String = "YOUR_SENDGRID_API_KEY"
' Email settings
Private Const FROM_EMAIL As String = "sender_email"
Private Const TO_EMAIL As String = "receiver_email"
Private Const subject As String = "Alert: Your sales has exceeded $100000"

Option Explicit forces you to initialize all the variables used to prevent errors usually caused by undeclared variables. Ensure the FROM_EMAIL you intend to use is verified in your Twilio SendGrid account. Learn how to verify your account here.

Proceed to add the subroutine for sending emails:

Sub SendEmail(cellAddress As String, cellValue As Double)
    Dim objHTTP As Object
    Dim postData As String
    Dim body As String
    Dim success As Boolean
    ' Construct the email body
    body = "Alert: The value in cell " & cellAddress & " has changed to " & cellValue & ", which exceeds the threshold of 100000."
    ' Create the HTTP request object
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    ' Open the HTTP request
    objHTTP.Open "POST", "https://api.sendgrid.com/v3/mail/send", False
    objHTTP.setRequestHeader "Authorization", "Bearer " & SENDGRID_API_KEY
    objHTTP.setRequestHeader "Content-Type", "application/json"
    ' Construct the JSON payload
    postData = "{""personalizations"":[{""to"":[{""email"":""" & TO_EMAIL & """}]}],""from"":{""email"":""" & FROM_EMAIL & """},""subject"":""" & subject & """,""content"":[{""type"":""text/plain"",""value"":""" & body & """}]}"
    ' Send the HTTP request
    objHTTP.send postData
    ' Check the response status
    If objHTTP.Status = 202 Then
        success = True
    Else
        success = False
    End If
    ' Clean up
    Set objHTTP = Nothing
    ' Return the success status
    If success Then
        MsgBox "Email sent successfully!"
    Else
        MsgBox "Failed to send email."
    End If
End Sub

The SendEmail subroutine takes two parameters: cellAddress, the name of the target column whose metric is being measured, and cellValue, the result of the calculated metric.

An HTTP object is then constructed with the API key in the header and the previously initialized variables in the postData. After the HTTP object has been cleaned up, we create a message box dialog to send an alert on the request’s status.

Integrate Notification Modules Within the Spreadsheet

In this section, you will connect the sendTwilioSMS and sendEmail modules to your sheet. To get started, open up an editor for your sheet. On the VBA sidebar, locate Sheet 1 (or the name of your sheet) under Microsoft Excel Objects. Double-click on it to open the code editor for that specific sheet.

The code below is a private subroutine that assigns IntersectRange to column E, which represents the Total Sales column. Before summing up the Total Sales column, the code checks to ensure an edit has been made inside column E. It then verifies that the sum of Total Sales exceeds the threshold of $100,000. If these two conditions are met, both the sendEmail and sendTwilioSMS subroutines are triggered.

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Declare variables
    Dim lastRow As Long
    Dim totalSales As Double
    Dim cell As Range
    Dim intersectRange As Range
    ' Check if the changed cell is in column E
    Set intersectRange = Intersect(Target, Me.Columns("E"))
    ' Exit if no change was made in column E
    If intersectRange Is Nothing Then Exit Sub
    ' Find the last row with data in column E
    lastRow = Me.Cells(Me.Rows.Count, "E").End(xlUp).Row
    ' Calculate total sales in column E
    totalSales = Application.WorksheetFunction.Sum(Me.Range("E2:E" & lastRow))
    ' Check if total sales exceed $100,000
    If totalSales > 100000 Then
        ' Find the first empty cell in column F for the alert message
        Set cell = Me.Cells(Me.Rows.Count, 6).End(xlUp).Offset(1, 0)
        ' Send email alert
        SendEmail "Total Sales", totalSales
        ' Send SMS alert
        SendSMS "Alert: Total sales exceed $100,000! Current total: $" & Format(totalSales, "#,##0.00")
    End If
End Sub

Click onthefloppy disk icon in the second row of the VBA page to save your code, then return to your Excel workbook.

Since the standard Excel format (.xlsx) does not support macros, you need to save your spreadsheet using the .xlsm extension. To do this, click on the File tab at the top-left corner of the page, then select Save As from the menu. Click on Browse to open the Save As dialog box. In the Save as type dropdown menu, select Excel Macro-Enabled Workbook (*.xlsm). Enter a name for your file and click Save.

Testing the Scripts

To test that your code works fine, edit the Total Sales column with a figure above $100,000. If all goes well, you should see a dialog alert on the spreadsheet that says “Email sent successfully!”. Check both your email and SMS apps to verify the messages were delivered. You should see notifications similar to the images below.

Building a Notification System for Google Sheets

Google Sheets allows users to write code to automate certain parts of the data using Apps Script, which is written in JavaScript. To get started, open up a sheet in Google Sheets, click on the Extensions tab, then select Apps Script. Inside the Apps Script code editor, remove any pre-existing code and add your Twilio variables at the top of the editor:

// Constants for email settings
const FROM_EMAIL = 'YOUR_VERIFIED_EMAIL';
const TO_EMAIL = 'RECIPIENT_EMAIL';
const SENDGRID_API_KEY = 'YOUR_SENDGRID_API_KEY';
// Constants for SMS settings
const TWILIO_ACCOUNT_SID = 'YOUR_SID';
const TWILIO_AUTH_TOKEN = 'YOUR_AUTH_TOKEN';
const TWILIO_FROM_NUMBER = 'TWILIO_NUMBER';
const TWILIO_TO_NUMBER = 'RECIPIENT_NUMBER';

Replace all of the placeholders with their respective values.

Ensure the FROM_EMAIL you intend to use is verified in your Twilio SendGrid account. Learn how to verify your account here. You can get the SMS variables from your Twilio Console.

Next, add a function that checks if the sum of the 5th column (Total Sales column) is greater than $100,000 before triggering notifications. Add the following code below the defined variables:

function checkSalesAndAlert(e) {
  const sheet = e.source.getActiveSheet();
  const range = e.range;
  // Check if the edit is in column E
  if (range.getColumn() !== 5) return;
  const lastRow = sheet.getLastRow();
  const totalSales = sheet.getRange('E2:E' + lastRow).getValues().flat().reduce((a, b) => a + b, 0);
  if (totalSales > 100000) {
    sendAlertEmail('Total Sales', totalSales);
    sendTwilioSMS(`Alert: Total sales exceed $100,000! Current total: $${Number(totalSales).toFixed(2)}`);
  }
}

The sendAlertEmail function below takes in two parameters: cellAddress, which is the name of the Total Sales column, and cellValue, which is the sum of total sales.

The UrlFetchApp.fetch is a built-in Apps Script method that makes HTTP requests to external APIs or web services. It takes the URL of the API and an options object. The object includes headers for the API key and a payload containing the details for the email.

Add the following code below the checkSalesAndAlerts() function:

function sendAlertEmail(cellAddress, cellValue) {
  const subject = 'Sales Alert: Threshold Exceeded';
  const body = `Alert: The ${cellAddress} has reached $${Number(cellValue).toFixed(2)}, which exceeds the threshold of $100,000.`;
  const url = 'https://api.sendgrid.com/v3/mail/send';
  const payload = {
    personalizations: [{ to: [{ email: TO_EMAIL }] }],
    from: { email: FROM_EMAIL },
    subject: subject,
    content: [{ type: 'text/plain', value: body }]
  };
  const options = {
    'method': 'post',
    'headers': {
      'Authorization': 'Bearer ' + SENDGRID_API_KEY,
      'Content-Type': 'application/json'
    },
    'payload': JSON.stringify(payload)
  };
  try {
    const response = UrlFetchApp.fetch(url, options);
    if (response.getResponseCode() === 202) {
      Logger.log('Alert email sent successfully!');
    } else {
      Logger.log('Failed to send alert email. Status: ' + response.getResponseCode() + ', Response: ' + response.getContentText());
    }
  } catch (error) {
    Logger.log('Error sending email: ' + error.toString());
  }
}

Finally, add the sendTwilioSMS function to your code. This function also uses the UrlFetchApp.fetch method to hit the SMS endpoint:

function sendTwilioSMS(message) {
  const url = `https://api.twilio.com/2010-04-01/Accounts/${TWILIO_ACCOUNT_SID}/Messages.json`;
  const options = {
    'method': 'post',
    'headers': {
      'Authorization': 'Basic ' + Utilities.base64Encode(`${TWILIO_ACCOUNT_SID}:${TWILIO_AUTH_TOKEN}`)
    },
    'payload': {
      'From': TWILIO_FROM_NUMBER,
      'To': TWILIO_TO_NUMBER,
      'Body': message
    }
  };
  try {
    const response = UrlFetchApp.fetch(url, options);
    if (response.getResponseCode() === 201) {
      Logger.log('SMS alert sent successfully!');
    } else {
      Logger.log('Failed to send SMS alert. Status: ' + response.getResponseCode() + ', Response: ' + response.getContentText());
    }
  } catch (error) {
    Logger.log('Error sending SMS: ' + error.toString());
  }
}

Setting Up Script Triggers and Testing

With your script complete, all that’s left is to trigger the script every time an edit happens in the spreadsheet. To do this, click the floppy disk icon at the top of the page to save your code, then click on the clock icon on the sidebar to open up the Trigger settings page. In the bottom-right corner of the page, click on the + Trigger button to open a modal.

From the “Choose which function to run” dropdown, select checkSalesAndAlert. Under the event source options, choose Spreadsheet, and set the event type to On edit. Then, click the Save buttonto close the modal.

Now that you are all set up, it is time to test your code. Go back to your spreadsheet and change any cell in the Total Sales column to a number greater than $100,000. You should see both an email and SMS notification.

Troubleshooting

If you encounter any errors or you are not receiving any notifications, consider the following:

Ensure that you have verified your email account and that you are sending your email from the verified email address.
If you use a free Twilio account, note that trial accounts can only send SMS to verified numbers. If you haven’t done that, learn how to verify your number here or upgrade to a paid account. Also, ensure that you have enough free credits to send SMS.

Best Practices for Setting up an Effective Notification System

To get the best out of a notification system that is efficient and meets your goals, here are a few things to keep in mind:

Avoid alert fatigue, where you are receiving frequent alerts within a short period, by adding a tolerance range to account for minor fluctuations in your data. This way, you're only notified of alert-worthy events.
Understand your data and consider seasonal fluctuations and nuances that can affect the measurement of your metrics. In cases of possible fluctuations, review your metrics and adjust your calculations to avoid false alarms that end up wasting your time and resources.
Add a documentation system alongside your code. This allows you to understand the rationale behind your current notification system and easily make adjustments if needed. It's also useful for businesses with multiple team members who need onboarding.
For Google Sheets users, use Google Cloud's Secret Manager API to store sensitive data like API keys and auth tokens. For Excel users, consider saving your sensitive data as environment variables. You can write a script that pulls the variables into your Excel sheet.

Tips for scaling your notification system

In cases where you have multiple spreadsheets or a large dataset, consider these tips to optimize your notification system and avoid performance issues.

Create a central configuration spreadsheet that manages your notification settings across all relevant spreadsheets if you have multiple spreadsheets.
As your notification system scales, upgrading your Twilio plans will give your application the leverage to handle a lot more notification volume.
Use throttling and debouncing to limit the frequency of notifications sent within a period of time, to avoid overwhelming the system.
If you need to pull data from a wide range of databases or handle larger datasets, integrate Power Query into your Excel spreadsheet to get more out of your notification system.
If your dataset has frequent updates, you should consider processing your notifications in batches. For instance, within 1 hour, all alert-worthy events that happen in your dataset will be combined into a single notification report. This makes for a better-optimized system rather than sending a report for each event. Keep in mind that using this method means you'll be giving up real-time updates.

Conclusion

At long last, we have come to the end of this article. You transformed a simple spreadsheet into a powerful, real-time alert tool. As a developer looking to automate communication processes or a business owner trying to streamline your day-to-day activities, this article should help you get the job done. With Twilio handling notifications and your spreadsheet managing data, you no longer need to worry about missing important changes or manually tracking updates.

As you continue to expand this system, you can add more triggers and customize notifications to optimize your processes. Make sure to upgrade your Twilio SendGrid account and Twilio Programmable SMS to access all the functionalities that come with Twilio's messaging tools.

Busayo Samuel is a full-stack MERN developer. She enjoys building web apps from scratch. In her free time, she can be found buried in a mystery novel or binging a sitcom.

Related Resources

Twilio Docs

From APIs to SDKs to sample apps

API reference documentation, SDKs, helper libraries, quickstarts, and tutorials for your language and platform.

Resource Center

The latest ebooks, industry reports, and webinars

Learn from customer engagement experts to improve your own communication.

Ahoy

Twilio's developer community hub

Best practices, code samples, and inspiration to build communications and digital engagement experiences.

Building a Twilio-to-Spreadsheets Notification System

Related Posts

Related Resources

From APIs to SDKs to sample apps

The latest ebooks, industry reports, and webinars

Twilio's developer community hub