Selfmade IOT Timeseries platform with LibreOffice

Two years ago, I made a special project for trainees https://www.mathieupassenaud.fr/use-google-spreadsheet-as-a-timeseries-storage-iot-initiation/ with a mobile web page and Google Spreadsheet as a database/dashboard tool.

Two years later, I discovered that was a good idea not only for educational purposes. I receive many requests about building IOT platforms. Some tools exists, from big cloud providers such as AWS IOT, Google IOT, OVH Timeseries etc... They have some essential elements such as a MQTT broker or a timeseries DB. Building dashboards, alerting or other specific usage note provided by those products need some development. Sometimes, big platforms are developped and deployed only for tests or prototypes. Do they really need such advanced software ? Most of time : noway !

That's exactly why I modified the first version for my trainees. I changed the source protocol my a MQTT Client and the tool to libreoffice due to API limitations from Google spreadsheet.

Libreoffice APIs

First question is : how to fill some cells directly by an API exposed by LibreOffice ?

You can run LibreOffice in listening mode (port 2002) with option :

			
			-accept=socket,host=localhost,port=2002;urp;
			
		

By launching LibreOffice from a command line (or edit desktop or menu entries) :

			
			$ libreoffice "-accept=socket,host=localhost,port=2002;urp;"
			
		

It opens some APIs we will use with a python script

Install Python Uno Bridge from https://pypi.org/project/unotools/.

			localContext = uno.getComponentContext()
			resolver = localContext.ServiceManager.createInstanceWithContext("com.sun.star.bridge.UnoUrlResolver", localContext )
			ctx = resolver.resolve( "uno:socket,host=localhost,port=2002;urp;StarOffice.ComponentContext" )
			smgr = ctx.ServiceManager
			desktop = smgr.createInstanceWithContext( "com.sun.star.frame.Desktop",ctx)
			doc = desktop.getCurrentComponent()
			sheet = doc.Sheets.getByName("logs")
        		sheet.getCellByPosition(0, 1).setString(str(datetime.datetime.now()))
		

First step is done, we have now a solution to fill some data has we need.

External program or macro ?

I tryied many many many times to run a such code as a macro in LibreOffice. It works, for a single work. What we need have to run in a thread and LibreOffice hates threads... It runs only once and then crashes. And after that ? Libreoffice Calc will never launch again...

Advanced features : multi sheets, lastrow

The goal of this script is adding data in a sheet, at the end. So I need a special function to get the last row :

			def detect_last_used_row(sheet_name):
			global doc
			oSheet = doc.Sheets.getByName(sheet_name)
			oCursor = oSheet.createCursor()
			oCursor.gotoEndOfUsedArea(False)
			return oCursor.getRangeAddress().EndRow 
		

I made also the same code for the last column.

Note I use getByName function on Sheets object. In this case it has a hudge impact for user interface.

			
			oSheet = doc.Sheets.getByName(sheet_name)
			
		

Mapping data

Messages from an IOT platform are (most of time) in a json format after decoding. First line of "data" sheet contains column title. This title is a json field name. By adding column with our custom json fields, data will be automatically mapped (except for column name starting with #).

			for i in range(0, last_column):
			  index=sheet.getCellByPosition(i, 0).getString()
			  if index.startswith("#") == False:
			    if index in dict_data.keys():
			      sheet.getCellByPosition(i, last_row).setString(dict_data[index])
			    else:
			      sheet.getCellByPosition(i, last_row).setString("null")
		

Parameters

A sheet named "parameters" has 4 parameters (for MQTT broker). I use this simple method for an impossible goal : no code, no script, no config file for final user.

MQTT Client

Python has a great MQTT client named Paho : https://pypi.org/project/paho-mqtt/

Connection seems simple while using a parameter sheet containing URI, Topic name, username and password :

			client= paho.Client()
			client.on_connect = on_connect
			client.on_subscribe = on_subscribe
			client.on_log = on_log
			client.on_message=fill_data
			sheet=doc.Sheets.getByName("parameters")
			broker=sheet.getCellByPosition(1, 1).getString()
			topic=sheet.getCellByPosition(1, 2).getString()
			login=sheet.getCellByPosition(1, 3).getString()
			password=sheet.getCellByPosition(1, 4).getString()
			client.username_pw_set(login, password)
			client.connect(broker)
			client.subscribe(topic)
			client.loop_forever()
		

We saw previously how to fill data with mapping.

Usage

Dependencies

You need python3, libreoffice (or openoffice), uno libs, paho.mqtt.client.

			
			$ sudo aptitude install -y libreoffice libreoffice-script-provider-python uno-libs3 python3-uno python3
			$ pip3 install paho.mqtt.client
			
		

Running libreoffice

Run Libreoffice with socket :

			
			$ libreoffice "-accept=socket,host=localhost,port=2002;urp;"
			
		

Note : you can edit a desktop shortcut and add those parameters

Configure

Open iot_platform_calc.ods file. It has everything you need to start. A sheet named "documentation" is a start guide to install and run all components.

Go to "parameters" sheet :

Fill all fields with your MQTT provider infos. You can run your own MQTT server with mosquitto. It has no authentication, so leave login and password cells blank.

Connect to AWS : https://docs.aws.amazon.com/iot/latest/developerguide/iot-message-broker.html

Connect to Google Cloud : https://cloud.google.com/iot/docs/how-tos/mqtt-bridge

Prepare your data structure

Open "data" sheet and put your own field titles, depending on your json data structure. Note that you can change it without restarting anything.

Python script

Run python script :

			
			$ python3 mqtt_client.py
			
		

Have fun !

Without writing any code, make your own dashboard, copy/paste datas, save on your local disk, print... A spreadsheet has lot of advanced tools !

Sources

All source code (ods file + python script) available here : https://github.com/mathieupassenaud/micro-iot-platform-libreoffice