**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|| |ibuprofen|| |Tylenol|| |docusate|| |methocarbamol|| |gas-x|| ![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?

calcodes

Tackling Medication Scheduling with a Developer's Toolkit

Join Our Newsletter. No Spam, Only the good stuff.