Tackling Medication Scheduling with a Developer’s Toolkit

Introduction: Mixing Software program Engineering with Private Healthcare

As a software program engineer at Google and beforehand AWS, I normally take into consideration my skilled abilities with regard to giant programs and plenty of layers of abstraction. It’s extremely fulfilling when I will use programming to resolve an on a regular basis downside for somebody. This time, it was about tackling a well being problem for crucial individual in my life – my spouse.

The Downside: Past Alarms and Handbook Logging

My spouse had two surgical procedures final yr. The primary time, we tracked the post-op medicines in a manually (with human reminiscence). That did not go effectively. Was it 2am or 3am after we woke as much as take a dose?

For the second surgical procedure we tried logging every time dose of every med. This labored, however was annoying as a result of figuring out when we have to get up within the center night time felt automatable.

A month in the past we discovered she’ll want one other. This go ‘spherical, I’ve automated it! Woohoo!

Her prescriptions for this surgical procedure:

1. Oxycodone each 4 hours (as wanted)
2. Tylenol each 6 hours
3. Ibuprofen each 8 hours
4. Docusate each 8 hours

Now, I’m not commenting on the precise medicines concerned. I am simply fixing the monitoring and scheduling downside. I’m tying to reply the 2-in-1 query when do I would like what subsequent?

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

Then I manually logged when she a med.

Collectively, these two datasets reply the query when do I take that subsequent? which solves half of the 2-in-1 query that I’m actually making an attempt to reply: when do I take what subsequent? To tug the datasets collectively, I used a VLOOKUP.

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

After whipping collectively a PIVOT desk, we’re simply capable of reply the query.

Set the Values to “can take once more after” summarizing by MAX. Set Rows too “med” and kind by “MAX of can take once more after” ascending.

Now that solutions the query when do I take what subsequent? and I fairly prefer it. However… I quickly realized how annoying it’s so as to add an entry to the Log. Particularly on my cellphone.

So, I added a button to do it for me by attaching a operate to a drawing of a button. Once I went to indicate it off to my spouse, although, the button did not work. Seems, cell Sheets would not help attaching features to buttons. To make it work on cell, I needed to hook into the onEdit occasion which fires each time the sheet is edited. so, I created a checkbox for every med. Checking a field counts as an edit, so the onEdit occasion is fired. The occasion contains details about the place the edit occurred, so I could make a desk of a checkbox per med after which I can examine a field and it will log the med with the present time.

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

  // solely proceed if the edited vary is throughout the MedActions vary.
  if (!rangesIntersect(actionsRange, editedRange)) {
    return;
  }

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

operate 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;
}

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

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

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

Create the script at Extensions > Apps script

And now, lastly, all of it works on the net and the Sheets cell app. When a field is checked, the onEdit operate is known as. The operate solely does issues when the edit occurred in a specific vary (this may very well be prolonged to be a type of router) AND when the edit was checking a checkbox. That is necessary as a result of the script will really uncheck the field, which might trigger the script to infinitely examine and uncheck itself.

I am curious to know: when have your skilled abilities unexpectedly come in useful in your private life?