This post is not about solving problems. Or business real life case-studies. This post is about what can you do with tools from Office 365 suite. I am documenting here the solution I built to make my home a little smart and to make my life easier.

Smart house 365

It all started with the wi-fi bulb. I purchased it from the myStrom company – a Swiss startup, that is focused on producing smart devices. I was thinking how it can be used besides just playing around with colors. The reason why I chose their product was that it had an open REST API, so I was hoping to be able to control it using… of course – Microsoft Flow.

I purchased it as well because I was preparing my session about the “Smart House 365” and I decided to use it as the smart device, that can be remotely controlled.

The idea for the demo was built a solution covering the flow described below:

1. I hit the Flic Button, to trigger the Flow.
2. Flic App gets my location’s coordinates and triggers Flow.
3. Flow gets weather forecast for the location.
4. Sends me an e-mail with the weather details.
5. Saves the data to the SharePoint so that I can later build a Power BI report on top of it.
6. Turns on the wi-fi myStrom bulb with the color, related to current weather conditions, e.g. Cloudy – Green, Rainy – Blue, Sunny – Yellow, Storm – Red and so on…

Simple, working solution showing, that Microsoft Flow can be used not only for business solutions, but as well to control the real world devices.

But the idea has evolved…

One of the biggest issues in the area I am living is the air. To be more specific – its cleanliness. It has become a habit, that every day in the morning I was turning on Airly (it’s Polish startup) app to check what are air conditions. And then I thought – why not to build myself a solution that will do it for me?

So I have slightly rebuild my flow:

1. Every day at 8am it is being triggered.
2. After that it gets data from Airly REST API and from Microsoft Weather.
3. It sends me a toast message to my smart phone about the current air conditions.
4. It saves data to SharePoint lists.
5. It triggers the “on-demand” refresh of the dataset in Power BI
6. Then turns on wi-fi myStrom bulb with the color that is provided by Airly to describe current air conditions.

The whole solution is installed by the doors, so before I leave flat, I know already what is the weather and air conditions. Moreover I have installed a tablet with Power BI report showing current conditions, that is being automatically refreshed every 15 minutes. Anyway, the working solution looks like below:

Brief solution description

Here I want you to get more familiar with specific parts of the solution.

Airly

I decided to use their services as they are Polish startup that is installing devices to measure air conditions in very many places across Poland. They deliver very precise data though. And integration is very easy. I just needed to register to obtain apikey and then use HTTP Request action to call their REST API (documentation here):

I am then parsing the response using the “Parse JSON” action. I got the sample payload from test calls using Postman.

SharePoint

I have built 4 lists:

1. Airly Data – the main list, where a header record is being created
2. Airly Indexes – descriptive information about the current condition, plus the hash-code of the color
3. Airly Standards – limits and percents telling how much thresholds are exceeded.
4. Weather Forecast – list holding information from Microsoft Weather

Although response data from Airly is a “table” for Standards and Indexes, however during my tests I realized that there is always just a single item in each table, therefore I decided to make it 1:1 relations and also I am only saving the first item from the tables to SharePoint, e.g. to get the Color:

body('Parse_current_air_details')?['current']?['indexes']?[0]?['color']

Or to get the percentage value of how the limit was exceeded:

body('Parse_current_air_details')?['current']?['standards']?[0]?['percent']

Refreshing Power BI dataset

I have to admit this was one of the hard nut to crack. How to make my report to be refreshed right after new data is saved. I was trying with the “Scheduled refresh”, to set it to 8am for example, whereas the Flow was set to trigger at 7.58am. But I was searching for something more like push message. And I found it.

I followed the steps described by Konstantinos here: https://medium.com/@Konstantinos_Ioannou/refresh-powerbi-dataset-with-microsoft-flow-73836c727c33 supporting myself with Power BI documentation: https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/refreshdataset. I have created myself a custom connector. And you know what? It works like a charm!

Converting hash color to HSV

myStrom light bulb requires information about the color to be switched to to be passed using HSV values, not RGB or hash. Therefore I had to convert color information I was receiving as hash to hsv. I decided to use Flow for that purpose, but an Azure Function could be used as well and possibly much easier.

To create a converting flow, I decided to follow algorithm described here: http://www.javascripter.net/faq/rgb2hsv.htm and to build a Flow that is triggered by the HTTP request and then returns response:

The algorithm part is build on comparisons:

Lighting up the bulb

When I bought the bulb and it arrived I read on a box, that indeed it can be managed by custom HTTP calls, but only in local network. After a while a realized, that if I am able to control it using my mobile phone and an app, even when I am not in my local network, then there has to be some sort of an API that I can use 🙂

I started digging and searching and landed up on this page: https://mystrom.ch/mobile/#device_switch_request. To get the authtoken I had to create myself an account on the myStrom page and then call this URL: https://mystrom.ch/mobile/authemail=[MY_MAIL]&password=[MY_PASS]. Piece of cake!

To get its MAC address I simply opened bulb’s technical details page inside myStrom mobile application used to control the device.

In the end, part of the Flow, to convert color, parse response and then control the bulb looks like below. Note, that for some reason it is not possible to set bulb’s color and turn it on in the same request. There have to be two:

Power BI

Last thing – the Power BI report that is being displayed on a tablet. It’s quite simple. It is showing me information from Airly and Microsoft Weather together:

The shown information is always the last record from a specific table, sometimes a calculation or a measure. There are two visualizations from the market that I am using here:

1. htmlViewer (for the current air conditions and day forecast)
2. Enlighten Data Story (for the detailed information about wind)

Summary

I have to say, that working on this “smart home 365” project was very much fun for me. I learnt couple of new things, but what is the most important – I have built a really working solution that is now making my life somehow easier. Moreover I proved myself, that “power apps” from Office 365 are really not limited to be used only for web-based business apps, but can be successfully used to build everyday life solutions.