As my son has Type 1 Diabetes (T1D), we use Nightscout to keep track of his blood glucose levels. With T1D it’s important to keep those levels in quite a tight range, so we use a few different apps to alert us when it’s going out of range. As I’ve started looking into Microsoft Flow, I thought it would be a good scenario to work through – take the readings that are stored in Nightscout and generate alerts on my phone when something’s going wrong.

Getting Started with Microsoft Flow

I did look to see if I could get Nightscout to trigger my Flow as it was needed. It has an IFTTT connector, but not Microsoft Flow, so to start with I built a flow using a Recurrence trigger to poll Nightscout every few minutes to get the latest data. This would cause us a problem in the real world as running it every 5 minutes would use 30 x 24 x 60 / 5 = 8,640 runs per month. Looking at the licensing page today, that would require a P2 license costing $15 per month, which isn’t ideal. Checking every 10 minutes brings us into the realm of a P1 license for $5 per month. Even so, it looks like a better long-term option is to get Nightscout to work out if the flow needs to run and use an HTTP trigger instead.

Extracting the Data from Nightscout

I used the “HTTP” action to get the latest 2 SGV readings from the Nightscout API

Fetch data from Nightscout

Note that I’ve used the Queries setting to specify I want to get 2 readings back. The Nightscout API will automatically apply a reverse date ordering for me, so these will be the most recent 2 without me having to add my own sort order.

Parsing the Data

The previous step gets me a block of text like:

    "_id": "5cab4051dcbac10d8021a4d0",
    "device": "AndroidAPS-DexcomG5",
    "date": 1554726975000,
    "dateString": "2019-04-08T12:36:15Z",
    "sgv": 126,
    "direction": "Flat",
    "type": "sgv",
    "NSCLIENT_ID": 1554727079041,
    "created_at": "2019-04-08T12:36:33.713Z"
    "_id": "5cab3f22dcbac10d8021a4cd",
    "device": "AndroidAPS-DexcomG5",
    "date": 1554726675000,
    "dateString": "2019-04-08T12:31:15Z",
    "sgv": 130,
    "direction": "Flat",
    "type": "sgv",
    "NSCLIENT_ID": 1554726775551,
    "created_at": "2019-04-08T12:31:30.234Z"

This isn’t very usable, so the first thing is to parse it using the “Parse JSON” action. Although Nightscout is supposed to expose a Swagger endpoint I couldn’t get it to work, so I used the option to generate a schema based on a sample payload. This tells the action what structure to expect to see in the JSON text, and therefore what data subsequent steps in the flow will have.

Parsing JSON

The one critical part of the data we have got is the sgv value – this tells us the “skin glucose value” measured by the CGM device my son is wearing. However, this value is reported in units of mg/dL, while in the UK we tend to work in units of mmol/L. The next step I added then is to create a simple data set of two mmol/L values using the “Select” action. This process should be familiar to anyone used to working with databases – it transforms each row in the input to a new row in the output by applying a series of mappings. In my case I created a new data set with one column called mmol.

Converting mg/dL to mmol/L

The calculation used to do the mapping is simply to divide the mg/dL value by 18:

Converting mg/dL to mmol/L

Because Flow expressions are based entirely around functions, I had to use div(x, y) instead of x / y. I find this a difficult habit to get into, and caused me lots of frustrating “The expression is invalid” errors! The other thing to note here is how I used 18.0 instead of simply 18. This should be familiar to anyone from a development background – dividing integer values will give an integer result, but dividing floating point values will give a floating point result. So div(5, 2) = 2, but div(5 / 2.0) = 2.5

Using Variables

Now I’ve got all the data I need, in the format I need it. However, I’ve got it all in one table, but what I really need is two separate values. I need the latest value and the previous value separately in order to compare them later. To save me having to write some repetitive expressions later to keep extracting the right one, I used a couple of variables to extract them once and store them for later reuse using the “Initialize Variable” action.

The expression I used to get the latest value was first(body('Convert_SGV_to_mmol'))['mmol']. Because I gave my previous steps helpful names instead of the default “HTTP”, “Parse JSON”, “Select” etc., I now get a much easier to read and understand expression.

After repeating this with another “Initialize Variable” action and changing first to last to get the previous reading, I added one final variable to format the latest reading so a human can read it. Because we have been dealing with floating point numbers, they can appear as helpful things like 7.19999999999999 instead of 7.2, so I want to do a bit of tidying up of that value before anyone is going to see it. Unfortunately Flow doesn’t expose any functions to control this formatting itself, and firing it out to an Azure Function or similar seems like overkill, so I just used an expression to trim the string after the first decimal place: substring(string(variables('Latest SGV')), 0, add(indexOf(string(variables('Latest SGV')), '.'), 2))

Triggering Alerts

I’ve got all the data I need to hand and nicely formatted, so now I need to fire some alerts!

The basic criteria I wanted to try were:

  1. Rising over 7.0 mmol/L
  2. Falling below 5.0 mmol/L

We can work out whether the data matches these conditions by:

  1. Comparing the latest and previous values to check if the values are rising and falling
  2. Check if the latest value is above 7.0 and the previous value is below (or equal to) 7.0. This checks if the level has just gone over the boundary, rather than having passed it some time ago
Check for BG dropping below a threshold

If this condition is matched, I want to trigger a mobile notification. In the message of the notification I can use the formatted value stored in a variable earlier:

Send mobile notification

Once the notification has been sent, I used the “Terminate” action to stop the flow. Following the advice on Jonas Rapp’s blog, this helps stop the problem of exponentially-nesting conditional statements and makes the flow much easier to read.

First Thoughts on Flow

This was an interesting first use of Flow for me, and helped me start making sense of the platform. It’s probably not really a great use for Flow in the end though, for a few reasons:

  1. As there’s no appropriate Microsoft Flow trigger in Nightscout at the moment, it needs to poll regularly to get real time alerts
  2. The regular polling and use of HTTP action needs access to premium licensing
  3. There are already various other apps that give alerts based on the sort of criteria I’ve implemented here

I was also looking at Flow on the basis of it being a “citizen developer” way of producing bespoke alerts. I’m not sure I saw that aspect of the system through this process however. Although I wrote substantially less code than I might otherwise have done, it still required a lot of developer-y knowledge:

  • How to access data from a REST API
  • An understanding of JSON and the concept of parsing
  • Data manipulation concepts
  • Integer vs. floating point calculations
  • String manipulation functions

That’s not to say that you need a degree in computer science to build this, but it does seem rather more complex than the hype might suggest. Maybe I just picked the wrong example to see the citizen developer experience at its fullest.

What’s Next?

I’ll hopefully have plenty more opportunities to play with Flow in a business context as we look at moving our D365 implementation to Online. As for looking at Nightscout data, I’m next going to have a look at using Azure Stream Analytics. Hopefully that’ll make it possible to perform the sort of queries I’d like against the data. In addition, I’ll be attending D365UG Manchester tomorrow to start learning about Power BI with Matt Collins, which I hope to put into practise with this data.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.