BigClown to Excel Online integration

Excel Online integration

This article describes how to store data from BigClown to Microsoft Excel Online.

Prerequsities

• MQTT broker listening to BigClown dongle.
• Microsoft Id
• Python 3

Setup online environment

Excel table

Login to Onedrive using your Microsoft Id.

Open folder or create new one where you want to place new Excel file to store data from BigClown.

Click new on left up corner and choose New - Excel Workbook

It opens new Excel file in online editor.

Click name of Excel workbook on top of screen and rename it.

Create table header device, sensor, sensorInfo, measurement, value, time.

Create Table from header. Click to eny cell with table header and click Format as Table.

Check My table has header.

• Excel is ready.
• Close browser tab with Excel.

Flow

On top left corner click List of Microsoft Services and open Flow.

On top bar on left click My Flows and click Create from blank. Click button Create from blank.

• Now you see empty Flow.

Search for Request action.

Click Request - When a HTTP request is received.

• Now we need to describe JSON object we will send to Flow. Click Use sample payload to generate schema bellow text box.
• Paste sample JSON to text box.

{"device": "temperature-button:0", "sensor": "thermometer", "sensorInfo": "0:0", "measurement": "temperature", "value": "27.44", "time": "2018-10-06 20:08:38"}

Click Done.

Click + New Step and Add an Action. Search Excel.

Click Excel - Insert row.

• Choose Excel file you created.
• When you create table it gets name. Choose name of created table.
• Map columns to properties of JSON.

Click Save on top right.Click My flows.

Local application

• Clone BC2JsonPostPy

git clone https://github.com/bechynsky/BC2JsonPostPy.git

Go back to Flow dashboard.Click Edit flow symbol Click Request action and copy HTTP POST URL

Edit config.ini. In URL you must doubled % symbol.

[DEFAULT]
URL = https://prod-26.westeurope.logic.azure.com:443/workflows/7bc74b9fce644126a478ae047d835a7e/triggers/manual/paths/invoke?api-version=2016-06-01&sp=%%2Ftriggers%%2Fmanual%%2Frun&sv=1.0&sig=ba_mcMZYbxxxxxxxxxD2TnfQie1KFM5o_7eQRKm0A
MQTT_SERVER = localhost
MQTT_PORT = 1883

• Run application.

python3 main.py

Sample output. Answer from server 202 means everything is OK. If you get 404 you have wrong URL.

user@user-pc:~/BC2JsonPostPy$ python3 main.py 
Connected with result code 0
{'device': 'temperature-button:0', 'sensor': 'thermometer', 'sensorInfo': '0:0', 'measurement': 'temperature', 'value': b'26.44', 'time': '2018-10-06 20:55:56'}
202
{'device': 'temperature-button:0', 'sensor': 'thermometer', 'sensorInfo': '0:0', 'measurement': 'temperature', 'value': b'26.44', 'time': '2018-10-06 20:55:59'}
202
{'device': 'temperature-button:0', 'sensor': 'thermometer', 'sensorInfo': '0:0', 'measurement': 'temperature', 'value': b'26.44', 'time': '2018-10-06 20:56:03'}
202
{'device': 'kit-lcd-thermostat:0', 'sensor': 'thermometer', 'sensorInfo': 'set-point', 'measurement': 'temperature', 'value': b'20.00', 'time': '2018-10-06 20:59:06'}
202 

Open Excel

• Open Excel to see data.