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 my professional skills with regard to large systems and many layers of abstraction. It's very fulfilling when I'm able to use programming to solve an everyday problem for someone. 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 in a 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.

Then I manually logged when she a med.

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), "")

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

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.

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?