Track daily medicine tablet for elderly

Description

I was looking for a relatively simple and reliable way of tracking whether my mother had taken her medication.

She has medication in two locations, some in the fridge, and non-refrigerated medication.

When I speak to her about her medication she tells me she has taken it however when a nurse visits she tells me she has not.

So I was looking for a solution that at 8pm every day would alert me if she had not taken her medication.

By using two small door-sensors to detect whether two boxes are opened (one box in the fridge, and one outside). The sensors triggered IFTTT to update a Google spreadsheet, and a cell monitor applet which fires at 8pm to send alert.

I designed this so you could have multiple sensors. And the notification uses the native IFTTT app on my mobile.

I am assuming for those with elderly parents this may be a common requirement so thought sharing how this works would be helpful.

Details

What you will need:

IFTTT account (Free for 1 sensor, £2.10/month for up to 19 sensors)
Google account (Free)
Door sensors compatible with IFTTT (range of cost from £30-£60 or by AliExpress for £10!)
Hinged box (lots on Amazon)

Although nothing is too complicated here, for the purposes of this guide I will assume you have some basic IT skills (e.g. familiar with using a spreadsheet)

Description how it works:

When a plastic box is opened it triggers the door sensor, which then triggers the IFTTT applet which adds the event time and sensor name into a Google Sheet (date, name of sensor, sensor state).

The Google Sheet checks whether events have arrived today (and updates every hour). It also prepares the alert message.

You will need an IFTTT applet for each sensor.

Then another applet watches a specific cell within the spreadsheet for changes (this is the cell with the alert message). If its changed it will alert you at 8pm (can be changed). If no alert message exists in the spreadsheet cell, no notification is sent.

I shifted most of the logic into a spreadsheet which helped make this solution relatively cheap to run. (1 sensor is free, multiple sensors its £2.10/month).

And ultimately, this lets me know whether or not the medication box has been opened. And although this does not 100% demonstrate the medication has been taken - it gives me a good indication and then I can provide suitable encouragement to my mother.

Physical parts:
- Hinged plastic box - to store medication. You can purchase multiple boxes if medication is stored in multiple locations (e.g. some in fridge, some outside).
- Small door sensors - one for each plastic box to detect when it is opened. If some medication is stored in the fridge ensure the sensor operates within fridge temperatures.

Online services:
- IFTTT - I used this service to update Google Sheets when a sensor is open, and to send me a notification at 8pm based on the contents of the Google spreadsheet.
- Google Sheets - I used this as a basic database to store events and perform some basic logic.

Possible future enhancements:

Change a lightbulb in my parent's house to flash or change colour, so when my mother decides to go to bed she notices the bulb flashing and realises she has forgotten her medication. The second she opens and closes the medication box, the light stops flashing.

Aqara has a vast range of other sensors which are all relatively cheap (and even cheaper if you buy the sensors via AliExpress direct from china - deliveries take 1-2 weeks although do not try to buy the hub from china as it is locked to China and won't work in IFTTT but all sensors are fine). But you could add to this solution temperature sensors, to ensure your parent's house is at a suitable temperature. Add presence sensors to ensure someone is moving about. Add sensors onto the front door to ensure it's been closed and alert if someone leaves at an unusual time. There is even a fall detection sensor (not sure how accurate it is).

Files

medslog.ods

Used as the main data store for events and formats messages. Upload this to Google Sheets (follow instructions in this project)

spreadsheet - 12.23 kB - 11/24/2023 at 09:00

Download

Components

1 × Aqara Door and Window Sensor Small, reliable and relatively inexpensive door sensor which can work in or outside the fridge.

1 × The Aqara Hub M2 Receives messages from sensors

1 × Hinged plastic box A box to store medication

Build Instructions

Collapse

1
Copy medslog file.
If you do not have an account already ensure you have a Google account (Free).

https://accounts.google.com/

Once you have created the account you need to upload the Spreadsheet (which I've copied here as a file).
1. The easiest way is go into Google Drive https://drive.google.com/
Setup the medslog.ods spreadsheet which will be used to store updates.
1. Create a folder called Medslog
2. Download the medslog.ods file from this project, and drag this file into your newly created folder.
3. Double-click the newly copied medslog.ods in your Google Drive.
4. In the top drop-down choose to Open in Google Sheets (this will now create another file in the same folder - if you wish you can now delete the original medslog.ods file)
5. When you first open this document some of the calculations break. To fix this ensure cell G2 is empty. (click on G2 and hit delete). You should see the calculations automatically update. I've put some demo data in here so you can see the calculations working. You should see that cell I8 has the alert message (we will monitor this cell). Leave it for the time being to help us test the solution but before you go live you need to clear the demo data within only columns A to C (simply click the A at the top of the column, and drag your mouse across to the C, now hit delete)
6. Because the spreadsheet uses time formulae it's important it automatically updates. Click on File, Settings, Calculation and choose "On change every minute". Also, turn on "Iterative calculation" (this is needed if you also want to link this to LED lights), And make sure the Locale is set to your timezone (under the general tab).
7. Copy and make a note of the URL at the top of the web browser (it will look something like this https://docs.google.com/spreadsheets/d/1Z4xJAbbC-kigvrdF0arSEr7PoDVo-E-t8/edit#gid=0 )
Note: I've made this solution support up to 7 different door sensors. But feel free to customise if you wish. You could have sensors on house doors, add presence sensors or camera movement sensors, so you can check if someone is moving around at home. Aqara has a good selection.
2

Setup Aqara account

In this solution, I chose to use Aqara door sensors and hub. These had a good rating on Amazon and in my tests have proved reliable and with a long battery-life of 2 years.

However, there are other door sensors that also link to IFTTT which could possibly be used.

Install the Aqara app on your mobile (registering for an Aqara account - make a note of these details)

Install the Aqara sensors by following their instructions. When you install the sensor give it a name that relates to the medication box (e.g. "fridge medication"). If you have multiple boxes to monitor, ensure each related sensor has a unique name.

The alerts will look like this:

[fridge medication] not opened, last opened at 11/11/2023 17:50
3
Setup an IFTTT
Setup an IFTTT account. (free if you only need 1 sensor, and £2/month if you need more)

Get Started - IFTTT (click sign up)

The solution requires two applets to be created on IFTTT. The applets are simple programs which are a simple activity associated with an event. For instance "IF a door opens, THEN write to an online spreadsheet.". I've tried to keep the IFTTT solution very basic to keep it in the cheapest plan.

Create applet to update Google Sheets from door sensor events
1. Create your first applet. Within IFTTT click "Create".
2. Now add you first "If This" trigger by clicking "Add"
3. Search for your door sensor manufacturer. If you also bought Aqara, search for "aqara" and click on the relevant location.
4. Now choose the "Door and window sensor is open" trigger.
5. Add your Aqara account
6. Choose the sensor device and your done with the "IF" part.
7. Now to setup the "Then" (this is what happens when the event occurs)
8. Seach for Google Sheets
9. You may need to add your Google account using the "Add new account" button.
10. Then enter the following information
11. For the "Formatted row" you can add the DeviceName and DeviceAction via the "Add ingredient" button. You will need to carefully type the lambda function (and in case you are wondering why this is necessary - there is no Time function variable I can use in IFTTT without paying for a higher tier, and if I use the NOW() or TODAY() Sheet functions they will keep automatically updating to the current time in Sheets, so this was my solution)
12. Click Update action
13. Note: You need to ensure you have copied the medslog file within this project into the above location - this file has a lot of the logic for the project (instructions above under the Google Account section)
14. Your final applet should look like this:
If you have multiple sensors you will need to perform the above steps for each sensor you own (with 1 sensor you qualify for the free plan, for any more you will need the IFTTT pro plan which is £2.10/month)

Create an applet to notify you if none of the sensors have been triggered today.
1. Click on "Create" to create a new applet.
2. Click on "Add" (for the "If This")
3. Search for "Google Sheets" (click on Google Sheets)
4. Now find "Cell updated in spreadsheet" (and click on this)
5. Now populate the fields like this
6. Click Update
7. Now to setup the Then (this one is easy!)
8. Search for "Notification" (click this)
9. And choose "Send a notification from the IFTTT app"
10. Now click "Add ingredient" and add "Value" like this
11. There are other notification types you can use too (if you want to enhance this). For instance, if you use the "Send a rich notification from the IFTTT app" you can add a title and a link to the Google Sheet. So if you receive an alert, you click on the notification on your phone and it takes you to the Google Sheet so you can see what has happened!
12. That's it!
Note: If you have any issues with Lamda function in Sheet, try this for the "formatted row" in IFTTT
```
=DATEVALUE(left("{{Date}}", find(",", "{{Date}}")-1)) + TIMEVALUE(mid("{{Date}}",find(" at ", "{{Date}}")+4,10)) ||| {{DeviceName}}||| {{DeviceAction}}
```