DEV Community

Cal McLain
Cal McLain

Posted on • Edited on

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

Screenshot of the config table in a spreadsheet

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
...

Screenshot of the med log in a spreadsheet

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), "")
Enter fullscreen mode Exit fullscreen mode
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

Screenshot of the log with a third column "can take again after" and the formula within it

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

Screenshot of the PIVOT table in a spreadsheet

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

Screenshot of the Action table in a spreadsheet

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;
}
Enter fullscreen mode Exit fullscreen mode

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?

Top comments (0)