Tackling Medication Scheduling with a Developer's Toolkit

Introduction: Blending Software Engineering with Personal Healthcare

As a software engineer at Google and previously AWS, I usually think about programming in big, abstract concepts which can make me feel disconnected from non-technical folks. However, sometimes I'm able to use programming to solve an everyday problem for someone and it is very fulfilling. This time, it was about tackling a health challenge for the most important person in my life - my wife.

The Problem: Beyond Alarms and Manual Logging

My wife had two surgeries last year. The first time, we tracked the post-op medications manually (with human memory). That didn't go well. Was it 2am or 3am when we woke up to take a dose?

For the second surgery we tried logging each time dose of each med. This worked, but was annoying because determining when we need to wake up in the middle night felt automatable.

A month ago we found out she'll need another. This go 'round, I've automated it! Woohoo!

Her prescriptions for this surgery:

1. Oxycodone every 4 hours (as needed)
2. Tylenol every 6 hours
3. Ibuprofen every 8 hours
4. Docusate every 8 hours

Now, I am not commenting on the actual medicines involved. I'm just solving the tracking and scheduling problem. I am tying to answer the 2-in-1 question when do I need what next?

First I opened a new spreadsheet (fun fact: sheets.new creates a new google sheet) and listed the meds and their frequencies.

Config
med	hours between dose
oxy	4hr
Tylenol	6hr
ibuprofen	8hr
docusate	8hr
methocarbamol	8hr
gas-x	12hr

Then I manually logged when she a med.

med	taken time
oxy	4PM on Tue Oct 31
ibuprofen	4PM on Tue Oct 31
Tylenol	7PM on Tue Oct 31
...

Together, these two datasets answer the question when do I take that next? which solves half of the 2-in-1 question that I am really trying to answer: when do I take what next? To pull the datasets together, I used a VLOOKUP.

==IFNA(H3+VLOOKUP(G3, Sheet1!K:L, 2, false), "")

med	taken time	can take again after
oxy	4PM on Tue Oct 31	8PM on Tue Oct 31
ibuprofen	4PM on Tue Oct 31	==IFNA(H3+VLOOKUP(G3, Sheet1!K:L, 2, false), "")
Tylenol	7PM on Tue Oct 31	3AM on Wed Nov 1

After whipping together a PIVOT table, we are easily able to answer the question.

med	MAX of can take again after
Tylenol	8PM on Wed Nov 1
oxy	9PM on Wed Nov 1
ibuprofen	12AM on Thu Nov 2
docusate	12AM on Thu Nov 2

Set the Values to "can take again after" summarizing by MAX. Set Rows too "med" and sort by "MAX of can take again after" ascending.

Now that answers the question when do I take what next? and I quite like it. But... I soon realized how annoying it is to add an entry to the Log. Especially on my phone.

So, I added a button to do it for me by attaching a function to a drawing of a button. When I went to show it off to my wife, though, the button didn't work. Turns out, mobile Sheets doesn't support attaching functions to buttons. To make it work on mobile, I had to hook into the onEdit event which fires every time the sheet is edited. so, I created a checkbox for each med. Checking a box counts as an edit, so the onEdit event is fired. The event includes information about where the edit occurred, so I can make a table of a checkbox per med and then I can check a box and it'll log the med with the current time.

Action: Log med taken
oxy
ibuprofen
Tylenol
docusate
methocarbamol
gas-x

function onEdit(e) {
  var sheet = getSheet("Sheet1");
  var actionsRange = getNamedRange(sheet, "MedActions").getRange();
  const editedRange = e.range;

  // only proceed if the edited range is within the MedActions range.
  if (!rangesIntersect(actionsRange, editedRange)) {
    return;
  }

  if (editedRange.isChecked()) {
    logMedTaken(editedRange.offset(0, -1, 1, 1).getValue());
    editedRange.uncheck();
  }
}

function rangesIntersect(r1, r2) {
  if (r1.getLastRow() < r2.getRow()) return false;
  if (r2.getLastRow() < r1.getRow()) return false;
  if (r1.getLastColumn() < r2.getColumn()) return false;
  if (r2.getLastColumn() < r1.getColumn()) return false;
  return true;
}

function logMedTaken(med) {
  var sheet = getSheet("Sheet1");
  var logRange = getNamedRange(sheet, "Log").getRange();
  var emptyRow = logRange.getNextDataCell(SpreadsheetApp.Direction.DOWN).offset(1, 0, 1, 2);
  emptyRow.setValues([[med, Utilities.formatDate(new Date(), "GMT-4", "MM/dd/yyyy HH:mm:ss")]]);
}

function getNamedRange(sheet, name) {
  var namedRanges = sheet.getNamedRanges();
  for (var namedRange of namedRanges) {
    if (namedRange.getName() == name)
      return namedRange;
  }
  throw new Error("Failed to find named range in sheet. [sheet=%s, name=%s]", sheet.getName(), name);
}

function getSheet(name) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name);
  if (sheet == null) {
    throw new Error("Failed to find sheet. [name=%s]", name);
  }
  return sheet;
}

Create the script at Extensions > Apps script

And now, finally, it all works on the web and the Sheets mobile app. When a box is checked, the onEdit function is called. The function only does things when the edit occurred in a particular range (this could be extended to be a sort of router) AND when the edit was checking a checkbox. This is important because the script will actually uncheck the box, which would cause the script to infinitely check and uncheck itself.

I'm curious to know: when have your professional skills unexpectedly come in handy in your personal life?