Problem
My mother takes medication and, unfortunately, the time-released version doesn’t work as well so she has to take a small dose every two hours between 8 a.m. and 6 p.m. daily.
When you’re active, trying to remember to stop and take medication can be tough - if she misses one, she feels the effect within 30 minutes so anything we can do to avoid this is worthwhile.
Solution
If she was a smartphone user, there are plenty of apps we could install to take care of this but she’s not much on technology - she does have a mobile phone and occasionally gets text messages but that’s about it.
I wanted to put a solution in place that would call her cell phone when it was time for her medication. For most days, making a call in these two-hour increments is fine with the exception of Sunday. On Sundays, she goes to church and, although she still needs the reminder, she doesn’t want her phone ringing during service. She has tried muting the phone during church but when she’s done that, she’s forgotten to unmute it. I was looking for a solution to avoid her making changes to her phone.
The solution I came up with was to create a schedule matrix in Google Sheets and schedule an Apps Script function to execute periodically to make a phone call or send an SMS through Twilio.
Prerequisites
There are a few things we need to setup before moving forward:
- Create a Twilio account and purchase a phone number
- Decide where you will store files that will be publicly accessible
- For calls, create an audio file to play when the recipient answers the call
- For calls, create a file that provides Twilio the instructions of what should happen when someone answers the call
Twilio
Twilio is a cloud communications platform that allows developers to send SMS messages, make voice calls, and a ton of other things. I use it mostly for sending SMS and making calls and, in this case, the communication with Twilio is quite simple.
You can get started with Twilio here. Once you create an account, you’ll need to add a payment method because we’ll need to purchase a phone number and credits to make calls and send SMS. For my account, I have it setup to recharge my account when the balance gets below $10 - this way, I don’t have to worry that I’ll run out of credits. The pricing is very reasonable - check out current pricing on their pricing page.
Once your account is created, you can setup billing on the console billing page. On the Phone Numbers Buy a Number page, you can search and purchase a phone number.
Before leaving the console, be sure and go to your project settings page and get your Account SID and Auth Token. Keep the auth token safe - the account SID and auth token are credentials that allow you to programmatically access your account. Make note of these two as well as the phone number for the App Scripts logic in a later step.
Publicly-accessible file storage
Many options exist for public file storage - you may use Dropbox or some other cloud service and any of these are fine provided you can access the file publicly (without credentials).
Because I’m a heavy AWS user, I’ll put my files in a bucket that I make publicly accessible. I tend to name any bucket that I want to have public access by including “public” in the name - this helps me remember that it is publicly accessible so I don’t put sensitive files in this bucket.
Audio file
For an audio file, there are a number of ways to generate one of these - you may have your favorite and feel free to create this as you see fit.
Being an AWS user, and with the release of Amazon Polly a couple of years ago, I use it for all of my text-to-speech needs. If you have an AWS account, you can go to the Amazon Polly Text-to-Speech page, enter the text you want to say into the Plain text field, listen to the speech that Amazon Polly generates, and download an MP3 file. You can do all of this programmatically but for this use case, we can generate our file right in the AWS console.
Once your audio file is created, store it where it is publicly accessible and keep the link to the file handy to add to our TwiML response coming up.
TwiML response
When a phone call is initiated through Twilio, once someone answers, we need to provide Twilio with what it should do next. Twilio has a specific markup language called TwiML and you can read more about voice-specific TwiML here.
The response we need to provide is simple:
<?xml version="1.0" encoding="UTF-8"?>
<Response>
<Play>LINK TO YOUR AUDIO FILE GOES HERE</Play>
</Response>
Note: Enter the link to your audio file in the Play
block.
This simply instructs Twilio to play the audio file at the provided link when someone answers.
Once the file is created, store it where it is publicly accessible and keep the link to the file close by so we can tell Twilio where to find it within our Apps Script code.
Google Sheets
Google Sheets is an online spreadsheet that most people are familiar with. If you have a Google account, from Google Drive, you can choose New -> Google Sheets to create a new spreadsheet. Google Sheets has a lot of functionality that I won’t go into here but, rest assured, it can probably meet any of your spreadsheet needs.
I came up with the following layout - it’s easy to read both from the UI as well as programmatically through the Apps Script SpreadsheetApp class:
Here, I’ve listed the days of the week as row headers and the hours of the day as column headers. At the intersection of the day and hour, I’ve entered a “c” for call (voice) and a “t” for text (SMS). For example, on Tuesdays at noon (hour = 12), I want to make a call.
To make it a little easier to get the range of cells that I want to work with programmatically, I selected all of the cells (A1 thru G8) and chose Data -> Named ranges… and when prompted, I called my range “schedule” - click Done to save the named range. Now that the named range is created, we can reference the range by name in our Apps Script code.
We’ll need to reference this spreadsheet in our script so, before we go, let’s get our spreadsheet ID. You can locate your spreadsheet ID in your browser URL section - the spreadsheet ID is between https://docs.google.com/spreadsheets/d/ and /edit#gid=0.
Google Apps Script
Google Apps Script is a lesser-known Google product than Google Sheets. Per the Apps Script home page, it is JavaScript platform in the cloud that lets you do things programmatically within the Google ecosystem, such as Calendar, Docs, Gmail, etc. It can also make and respond to GET and POST requests - we’ll make POST requests to the Twilio REST API in this post. Read more about Google Apps Script here.
To create a new Apps Script, from Google Drive, choose New -> More -> Google Apps Script. I named my script reminder but name yours as you like.
We’ll need a few functions in our script: a make call function, send text function, and functions to help us find the right cell in our spreadsheet to determine if we are making a call or sending a text message. Let’s start with making a call.
If you plan to reuse these functions, it would be a good idea to pass some of these values the function uses as parameters rather than hard-coding them here but you get the idea.
Making a Twilio call
To make a call, we need a few things: our Twilio Account SID and auth token we made note of earlier, the number we want to call, the number we are calling from (this is our Twilio phone number we purchased earlier), and a link to our TwiML response we added earlier. Once we have these items, add this function in your Code.gs file and updated the placeholders I have below with your values.
Note: when adding your Twilio Account SID and auth token to options.header
, be sure to include the colon : between these two values.
function makeCall() {
var callUrl = "https://api.twilio.com/2010-04-01/Accounts/<ADD YOUR TWILIO ACCOUNT SID HERE>/Calls.json";
var payload = {
"To": "<ADD THE NUMBER YOU WANT TO CALL HERE>",
"From" : "<ADD YOUR TWILIO NUMBER HERE>",
"Url": "<ADD YOUR LINK TO YOUR TWIML RESPONSE HERE>",
"Method": "GET"
};
var options = {
"method" : "post",
"payload" : payload
};
options.headers = {
"Authorization" : "Basic " + Utilities.base64Encode("<ADD YOUR TWILIO ACCOUNT SID HERE>:<ADD YOUR TWILIO AUTH TOKEN HERE>")
};
UrlFetchApp.fetch(callUrl, options);
}
Sending a Twilio SMS
As with a Twilio call, we need a few items to send an SMS message - the main difference here is that for an SMS message, we’ll provide a Body
value and will not provide Url
and Method
parameters.
function sendText() {
var messagesUrl = "https://api.twilio.com/2010-04-01/Accounts/<ADD YOUR TWILIO ACCOUNT SID HERE>/Messages.json";
var payload = {
"To": "<ADD THE NUMBER YOU WANT TO CALL HERE>",
"From" : "<ADD YOUR TWILIO NUMBER HERE>",
"Body" : "Time to take your medicine",
};
var options = {
"method" : "post",
"payload" : payload
};
options.headers = {
"Authorization" : "Basic " + Utilities.base64Encode("<ADD YOUR TWILIO ACCOUNT SID HERE>:<ADD YOUR TWILIO AUTH TOKEN HERE>")
};
UrlFetchApp.fetch(messagesUrl, options);
}
Locating a cell in our spreadsheet
We need a couple of functions to locate the correct cell in our spreadsheet based on the current day of the week and hour of the day. The following functions allow us to pass a range and a value to look for and get back the corresponding row or column.
function rowOfDay(range, day){
var data = range.getValues();
for(var i = 0; i<data.length;i++){
if (data[i][0] == day) {
return i + 1;
}
}
}
function columnOfHour(range, hour){
var data = range.getValues()[0];
for(var i = 0; i<data.length;i++){
if (data[i] == hour) {
return i + 1;
}
}
}
Each time our main function runs, it will determine a day of the week and hour of the day to pass to these functions to locate the proper cell.
Initiating the contact
Now that we have all of our support functions in place, we can write our main function that will be called periodically by a time trigger. Here’s the code - we’ll discuss what we’re doing below. The only placeholder you’ll need to populate here is your spreadsheet ID.
function remind() {
var spreadsheet = SpreadsheetApp.openById("<YOUR SPREADSHEET ID GOES HERE>");
var range = spreadsheet.getRangeByName("schedule");
var weekdays = ["Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"];
var now = new Date();
var topOfHour = now.getMinutes() == 0;
var column = columnOfHour(range, now.getHours());
var row = rowOfDay(range, weekdays[now.getDay()]);
var contactType;
if (topOfHour && column && row) {
contactType = range.getCell(row, column).getValue();
if (contactType == 'c') {
makeCall();
}
if (contactType == 't') {
sendText();
}
}
}
We’re using SpreadsheetApp
and calling a method to open the spreadsheet by it’s ID. Next, we’re get access to the range using the getRangeByName
. The weekdays
array is simply for convenience - I could have listed a numeric value for the days of the week in my spreadsheet but that’s harder to read. Another option would to possibly format the current date to get the day of the week but this works fine for my needs.
Next, we’re creating a new date which will default to the current date and time. The topOfHour
variable is used to determine if we’re at the top of the hour. This is only needed because I decided to schedule the trigger every minute as opposed to every hour. Every hour will work provided anytime within the hour meets your needs.
To get the column, we’re calling the columnOfHour
function and passing in the current hour. Likewise, we’re calling the rowOfDay
function to get the row and using our weekdays
array to pass the correct day of the week.
We’re checking that the topOfHour
is true, and that the column
, and row
have values - in JavaScript, this is referred to truthy , if a column or row isn’t found, they would evaluate to false and the code within the if
statement wouldn’t execute.
If the code does execute, we’re checking the value in the cell to determine what needs to be done and calling the appropriate function.
Before scheduling your trigger, be sure to test your functions. You can test your makeCall
and sendText
functions simply by selecting the function in the Select function dropdown list and clicking the Run button. The others may require that you change your spreadsheet or the parameters/variables the functions are using. TEST!!! You don’t want any surprises!
Triggering the function periodically
There are some quotas associated with Google Apps Script execution so you will want to check these out here.
To schedule a trigger, from your script page, choose Edit -> Current project’s triggers - this will take you to the triggers page.
On the triggers page, choose the Add Trigger button located in the lower-right corner of the page. On the Add Trigger dialog, choose the function you want to execute (in my case, it was named remind
). For which deployment should run, you probably on have Head and that’s fine (this is related to version control of your function which we don’t need to worry about at this time).
The event source should be Time-driven and the type of time-based trigger should be either Hour timer or Minutes timer.
Note: If anytime within the hour is fine, feel free to use the Hour timer - if you do this, you’ll want to remove the topOfHour
variable from the if
statement in your remind
function. I do want the top of hour in my case so I chose the Minutes timer but this, of course, means that it will trigger every minute and, even though it may not be time for a call or SMS, you’ll be consuming a portion of your quota time. For me, mine is currently running under one second so I shouldn’t reach the daily quota. For interval, choose an appropriate interval - I chose Every minute.
Once you’ve completed the form, choose Save to save the trigger - you should now see it listed on your trigger page.
Wrap up
In this post, we’ve created a Google Sheet to store our schedule and created an Apps Script to run periodically to check the schedule and make a call or send an SMS through Twilio.
I hope you found this post helpful. If you have questions, you can reach out to me on Twitter @ballenkidd