I was speaking with a CFO yesterday who complained that he had missed a critical benchmark in a spreadsheet report he receives every week and it was going to cost him a pretty penny. When I told him that he could have been alerted by his spreadsheet (either Factivate or Google Sheets) he was so mad that no one in his company had told him of this that he almost threw his drink against the wall. Crazy dude. But, he did have a point. Finding out about our spreadsheet actions has been relatively difficult so far. It’s our responsibility to tell people about this awesome functionality if we want to help them. This is why I’m writing this post on our Spreadsheet SMS action: how to have your spreadsheet (Google Sheets spreadsheet or Factivate spreadsheet) automatically send you an SMS alert when a cell value changes.
The Factivate way to send an sms from your spreadsheet when a cell value changes (#easy #noprogramming)
Here’s a quick step by step video on using Factivate’s spreadsheet alerts to send you an SMS alert from your spreadsheet to show you how easy it is. In this example, we’re sending it at a specific time interval so I will describe just how easy it is to send the SMS alert when a cell value changes.
FYI – If you’re all ready to get this for your spreadsheets, you can sign up for Factivate or the Factivate Google Sheets Action add-on, here: Spreadsheet Trigger Actions https://factivate.com/spreadsheet-actions/
It’s pretty simple huh?
Here’s a step by step guide to have your spreadsheet send you an SMS alert when a cell changes using Factivate
Step 1 – Open Spreadsheet Actions Menu > IF
Go to the actions panel on the right-hand side and click on the IF statement. Below the IF area, you will click on the text that says f(x) formula.
Step 2 – Write your Formula
This selection is basically telling Factivate that you will use a formula to trigger the SMS so all you have to do is write down your conditional formula to trigger the alert.
In this example, we’re going to monitor Ad Spend Budget vs Realized so our formula will read F9>=G9 (when cell F9 is greater than or equal to G9, send me an SMS alert).
Once we’re done with the formula, click on Save & Continue. This will immediately open the THEN window shown below.
Step 3 – Select Send SMS to send an SMS text message if a cell value changes
Step 4 – complete the information required on the pop-up.
You can add your phone number and message by typing in the boxes. However, this isn’t’ as practical as simply referencing cells.
To reference cells with phone numbers or with the message, simply click on the cell selection icon (right side) and then drag your cursor to select the cells you choose. For example, if I want to have my SMS sent out to 5 different phone numbers, I can click on the little cell icon and simply select 5 cells which have the phone numbers rather than writing it all in. Once you’re done, your screen can look like the image below:
Step 5 – Test and Save
Once you’re happy with your message, you can click on Test. This will trigger the action to happen right away for the phone number listed on the pop-up. If you’re happy with the spreadsheet action trigger, just click on Save. This will enable the spreadsheet actions to start tracking your cell(s) and react with the SMS alert per your settings.
That’s it! You just learned how to send an SMS alert whenever a formula or cell changes in value from your spreadsheet. Isn’t it easy! Your new action will now be listed under the Actions panel under the Send SMS drop down like below:
As I mentioned, the “Sends SMS from spreadsheet alert” can be found using only Factivate or the Factivate Google Sheets add-on. You can sign up for either one by going to the Spreadsheet Trigger Actions page here: https://factivate.com/spreadsheet-actions/
Want any other spreadsheet triggers or actions to fire off when your cells change? Email firstname.lastname@example.org so that we can build them for you!
The Old Complicated Google Sheets way to send SMS alerts when a cell changes (#harder) (#programming) (#wasteofyourtime)
If you’re dead set on learning how to do this from scratch using Google Sheets, you will first need to decide which SMS service you want to yous. In this example, we will program our code to use Twilio. You will need to sign up for Twilio (pay your $20 for 2500 messages) and use the following script (take note of the placeholders for Account SID, Auth Token and Twilio Phone Number):
If you’re still following me, let’s keep going. You will need to replace the phone number if your phone number. Then replace YOURACCOUTSID with the Account SID from your dashboard. Then define a “payload” describing your text messages”. Then replace YOURTWILIONUMBER. Then add headers to options to authorize the request with your Account SID and Auth Token from the dashboard.
Finally, execute your http request.
All you need to do then is save your file and run your script.
As always, now it’s your turn. If you have any questions, please feel free to reach out. We’re here to help!