Crutches and wheels (not from a bike) – how I structured my pill intake
It just so happened that lately I have been sick for a long time.
My standard method of treatment “if you treat a cold, it will go away in 7 days, and if you do not treat it, in a week” stopped working (oh, this old age).
I had to see a doctor and resort to special treatment.
By special treatment – I mean a gigantic list of pills, injections and other drugs with all sorts of peculiarities: some medications need to be drunk before meals, some after, some only at night, and some should be replaced after a certain time or under certain conditions. In this treatment regimen, in one morning intake alone, there can be more than 10 tablets 🤪
The doctor’s prescriptions look something like this (forgive me, let me gloss over the names 😅):
As you can imagine, remembering all this is impossible.
Even if you look at this list every time, it is still not very obvious what and when to do.
Therefore, the first times I wrote everything by hand on a piece of paper, and then I had to connect heavy artillery and start drawing up signs of this kind – this is the only way I was able to comply with all the assignments and not miss anything:
It is worth mentioning that the coronavirus has not spared my family – such signs were needed not only for me, but also for my parents and my sister’s family.
At some point, I noticed that every time I draw up the tablets, I perform similar actions: if I take the pills 3 times a day, then it’s morning, afternoon and evening; if the pills are taken before meals – I write them down on top, and if after – on the bottom, etc.
That is, some kind of algorithm has formed in my head, and in theory, it can be automated 🤔
In practice, as it turned out, it is also possible 🙂
I used all the same Google Sheets and, in addition to them, the reports from Google Data Studio, I got the following:
First, you need to fill in a small part of Google Sheets:
The name of the medicine;
How many days should you drink it;
How many times a day you need to take it (check-boxes do not have to be filled in – I will explain why below);
At what dosage;
What is his dependence on food;
Is there any food-related time;
Are there any substitutions (if yes, fill in the title, dosage, comment).
Based on the information filled in – in the second hidden part of Google Sheets, all sorts of things will be calculated, and more specifically:
From the names – you get the list itself in Google Data Studio;
From the number of days of admission – the final date will be calculated;
From the number of admissions per day – medicines will be distributed between Morning, Afternoon, Evening and Night (automatically).
Formula (Y – morning, D – day, etc.):
In this case, the distribution can be adjusted manually using check-boxes (they will overwrite the automatically calculated reception time):
At this point, I use a simple formula and put down values in separate fields for each time of day (or leave the field blank if there is nothing to write down):
And then I compose from the received updated data for specific tablets:
For the output in the report, I use the final column, where I combine the automatically calculated data with manual adjustment:
It was important to refine this for cases where, for example, a pill needs to be taken only once a day and strictly at night or in the morning (although I automatically attribute a one-time dose to Daytime).
From the dosage and food information, a simple text comment will be formed:
Formula (in which no comment is prescribed if the drug intake does not depend on food intake):
=ЕСЛИ(I3="независимо от приема пищи";"";СЦЕПИТЬ(J3;" ";I3))
And also the order of intake will be calculated, on the basis of which the tablets are sorted in the correct order: before meals they will be at the beginning of the list, during – in the middle, after – even lower, and at the very end those that can be taken regardless of food;
=ЕСЛИ(I2="независимо от приема пищи";"4";ЕСЛИ(I2="до еды";"1";ЕСЛИ(I2="во время еды";"2";ЕСЛИ(I2="после еды";"4"))))
Based on information about substitutions, an additional table will be generated in Google Data Studio, which will contain information about replacing drugs / dosages, etc.
Here, without formulas, just by the same sorting, I display updated data opposite the drugs.
All this information will eventually go to Google Data Studio, and on its basis a report will be generated, which can be downloaded in PDF format:
Since I undertook to mention incredibly stupid formulas here, it is also worth at least briefly clarifying how to connect Google Sheets to Google Data Studio and configure the display of tables:
To begin with, in any case, you need to create a table with all the formulas, otherwise there will be nothing to connect;
Then we go to Google Data Studio and create a report there (you can empty it, you can select a template) and select our ready-made table in the data sources:
After that, the first crooked plate will appear in the report, which can be customized for yourself.
Actually, we create 5 of these and set them up (in fact, we just select a set of parameters displayed in the table in the required order):
And we also set up such an output filter by reception time for each of the tables for morning-day-evening-night:
Decline of action
About labor costs
Everything about everything took me 3.5 hours.
I could have thrown a couple more hours on the design, but I’m too lazy – and so it will do.
And it could also be done faster if I had worked with Google Sheets and Google Data Studio more often before (but this is the first time I got to this tool).
Adjusting the reception time
It would be great to somehow modify the check-boxes to replace the time of medication intake. Now they are filled only manually and it is not immediately obvious when to touch them.
At first I tried to check the checkboxes based on formulas, but the presence of formulas in cells with check-boxes prevents them from being edited (that is, the reception time cannot be adjusted) – so I would like some kind of macro that will tick off the check-boxes itself when the number changes in the “How many times a day” column. Presumably, with a macro – they can then be edited, unlike formulas.
This would save the table from unnecessary overwrite columns and make the interface clearer.
Reception table by day
It would be cool to display a general table of medication intake for each date – for sure there are cases when pills do not need to be taken every day, but, for example, every other day.
Surely half of what I did with formulas in tables could have been done using regular Google Data Studio tools or more culturally and briefly, but this is for the smart and advanced. So far, past the question.
In my opinion, a cool thing came out for people who are often ill.
Or people like me: who can’t remember which of the pills in his box is for nerves, and which is for diarrhea 😂 (I solve the problem with the help of “maaam, what is this from?”)
And in general, there are many mobile applications on the market that allow you to create reminders for taking medications, but I have not met such a one to help sort out a doctor’s prescription.
In addition, in applications it is difficult to keep track of drug / dosage changes, as well as to clog information about atypical procedures: inhalations and injections. This is clearly seen in the same table.
I like the idea itself – it will at least make life easier for me and my relatives, so I want to develop it.
I would like to ask you some advice: what do you all think about this and what other bundles of tools can be suitable for implementing such a solution? Desirable no-code.
PS I have already tried Tilda, but it turned out that the built-in functionality (even the paid version) does not allow making complex forms (where the number of fields = the number of drugs is unknown in advance).
Plus, I do not know how to then pull out the data that arrived in the admin panel and process it.
I’m not really a tyzh programmer (just a seasoned project manager).