Tackling Medication Scheduling with a Developer's Toolkit
Tags: sheets, productivity, programming, showdev
Published: 2023-11-18 15:21:03

**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](https://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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ysz96shcbun65hlj9bcr.png)

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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cr8c4w607zkf8kewj8en.png)

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     |

![Screenshot of the log with a third column "can take again after" and the formula within it](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3k6egj9ggplfnp8amhnt.png)


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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hjwvfs90lkubkdyxrq6s.png)

*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|<input type="checkbox"/>|
|ibuprofen|<input type="checkbox"/>|
|Tylenol|<input type="checkbox"/>|
|docusate|<input type="checkbox"/>|
|methocarbamol|<input type="checkbox"/>|
|gas-x|<input type="checkbox"/>|

![Screenshot of the Action table in a spreadsheet](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ub8o6fztwg493oa23s99.png)


```js
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](https://divpusher.com/glossary/routing/)) 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?