Want to Contribute to us or want to have 15k+ Audience read your Article ? Or Just want to make a strong Backlink?

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.

med hours between dose
oxy 4hr
Tylenol 6hr
ibuprofen 8hr
docusate 8hr
methocarbamol 8hr
gas-x 12hr

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

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

Exit fullscreen mode

med taken time can take once more after
oxy 4PM on Tue Oct 31 8PM on Tue Oct 31
ibuprofen 4PM on Tue Oct 31 ==IFNA(H3+VLOOKUP(G3, Sheet1!Okay:L, 2, false), "")
Tylenol 7PM on Tue Oct 31 3AM on Wed Nov 1

Screenshot of the log with a third column

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

med MAX of can take once more 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 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.

Motion: Log med taken

Screenshot of the Action table in a spreadsheet

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)) {

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

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

Exit fullscreen mode

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?

Add a Comment

Your email address will not be published. Required fields are marked *

Want to Contribute to us or want to have 15k+ Audience read your Article ? Or Just want to make a strong Backlink?