IoT Pill Reminders with SAP Cloud Foundry, Google Sheets and micro:bit

In this post, we’ll learn how to build a simple IoT “Pill Reminders” device to remind the patient to take the pill and as soon as he/she took the pill, we will get the update status in Google Sheets.

Hardware Components

BBC micro:bit
Edge connector breakout board for micro:bit
LED (or a buzzer)
Magnetic door contact switch
Mini breadboard
Mini PC with bluetooth connection
Pill container

Software Components

SAP Cloud Connector
SAP Cloud Platform: SAP Cloud Foundry
NodeJS App
Google Sheets

How it works ?

We define the time schedules in Google Sheets column “InputTime” when the patient is supposes to take the pill. Google Scripts will run every 5 minutes to check the entered time schedules and status, and then send the message to SAP Cloud Foundry via a webhook HTTP Post request
SAP Cloud Foundry routes the message to pill reminder server
Pill Reminder server receives the message and send it to pill reminder device via bluetooth connection
The LED light (we also can add sound) on pill reminder device will blink to alert the patient to take the pill.The LED Light will stop blinking only when the patient opens the pill container (presumably that he or she will take the pill) and the timestamp will be recorded in Google Sheets column “Timestamp“. We will get the timestamp from this column when the patient has taken the pill.
The process will keep continue.

High Level Diagram

Hardware Setup

Google Sheets Setup

Create a new Sheets called PillReminder and populate the following columns:

InputTime
This is the time schedule that we will enter to remind the patient to take the pill at a certain time.
Timestamp
This field will be auto populated with the timestamp when the patient open the pill container (to take the pill)
Message
This is the message that will be sent to pill reminder device. At this moment is not implemented, in the future we can send the voice message or text message displayed on the LED screen on pill reminder device
Send Notification
This field will be populated with status “y” when the message is send out.

We will create Google Script, Code.gs to check the InputTime and perform the HTTP Post message to SAP Cloud Foundry.

To insert Google Script, select Tools and Script Editor.

Update the following information with yours:

var URL= 'SAP_CLOUD_FOUNDRY_APP_URL';
var username = 'SAP_CLOUD_FOUNDRY_USER_ID';
var password = 'SAP_CLOUD_FOUNDRY_USER_PASSWORD';

Select Edit and Current project’s triggers to define the time interval when the script will be executed.

Click Add Trigger.

Select the following configuration to run CheckStatus script every 5 minutes and click Save.

Choose which function to run: CheckStatus
Choose which deployment should run: Head
Select event source: Time-driven
Select type of time based trigger: Minutes timer
Select minute interval: Every 5 minutes

NodeJS “Pill Reminder Server” Setup

Install the following libs that are required by our app:

npm install express body-parser request edit-google-spreadsheet bbc-microbit

We need to create a project and create the oauth2 credential in order to load and modify the Google Sheets from NodeJS app.

Go to https://console.developers.google.com/ and create a new project PillReminder.
Within the project, click Enable APIs and Services.
Search for Google Sheets API.
Click Enable.
Go back to project page and click Create Credentials. Then select OAuth client ID.
Select Application Type Other, enter name Pill Reminder and click Create.
Copy the client ID and client secret.
Download get_oauth2_permissions.js and paste the client ID and client secret.
Run get_oauth2_permissions.js. The program will ask you to open the URL and enter the code generated from that URL
Open the URL and copy the code.
Enter the generated code and if there is no error we will get the oauth2 information
Copy that oauth2 information together with spreadsheetId into UpdateGoogleSheet function in index.js.
Before we can run the app, we need to flash (update the firmware) the micro:bit. Plugin your micro:bit to your USB PC, download the firmware and drag it onto micro:bit. You will see the LED light on micro:bit is blinking. Once is done, you will see the scrolling message “DC”.
Finally we can run the app with command:
```
sudo node index.js
```