Stream IoT data into Excel

 

There is a whole world of IoT services out there that allow you to connect your device and upload data to the cloud. From there you can make decisions on your data, run it through AI models, or visualize it with powerful dashboards.

 

But what if that's just too much? What if you just want to see the data in Excel?

 

This is a common case - especially in schools that are introducing physical computing to CS students. They often don't have the money, expertise, or firewall access to spin up cloud services to ingest data, and visualization services to show it. What they do often have is Excel - either running on PCs, Macs, Chromebooks or iPads, and usually want to connect a simple device to something, and plot data from it so students can work on projects.

 

 

One example is digital agriculture, which in schools is often a temperature sensor used to calculate growing degree days, or a soil moisture sensor, maybe combined with a relay and pump, to track if a plant needs water and if so automatically water it.

 

There are many devices and kits that can provide the physical computing side – such as Raspberry Pis, Arduinos, and sensor kits, but how can data be sent from these devices to Excel?

 

This post shows you how to stream data from an IoT device to Excel running on the web!

 

This post contains links to sample code that you can use with the FarmBeats for students kit, available from Seeed Studios.

 

Architecture

 

 

 

The architecture you can use for this is not that complex. As long as your IoT device has WiFi, you can expose a REST API over HTTPS that can be called by an Excel add-in. This API can then be used to read sensor data, or control actuators.

 

Let’s dive in a bit deeper. You can also find all the sample code for this in my GitHub repo.

 

IoT device with a REST API

 

The Excel add-in JavaScript API can make calls to REST endpoints, including those running on your local network. On your IoT device you can expose a REST API, and from this API return sensor data, or control actuators.

 

For example, if you are using a Raspberry Pi with a soil moisture sensor and a relay, you can create a Python/Flask app, and expose a REST API with 2 endpoints:

temperature/ - make a GET request to this endpoint to get the current reading for the temperature sensor

relay/ - make a POST request to this endpoint to turn the relay on or off, passing the desired state as a boolean in a JSON request body.

 

As a tip – reading from sensors can take time, and you probably want to avoid multiple concurrent reads to a sensor. A good way to do this is to have a thread that reads and caches sensor values every second or so. When a REST request comes in, read from the cache. This would also allow you to cache historic data, should you want to add an endpoint to get historic data captured when Excel isn’t connected.

 

The Excel add-in architecture also supports web sockets, so you can even expose streaming data over a web socket if you wish.

 

One thing you MUST do though, is use HTTPS. Excel add-ins will not work with HTTP, they require a secure endpoint using HTTPS. You can use ad-hoc self-signed certificates, such as by setting the SSL context in a Flask app to adhoc as described in this blog post. You also need to run using sudo to use SSL with a flask app on a Raspberry Pi.

 

You can find example code for a RaspberryPi that hosts a Flask app with REST endpoints, using the sensors from the FarmBeats for students kit in the farmbeats-server folder in my GitHub repo.

 

Excel add-ins

 

Excel has an add-in model that allows you to build add-ins using JavaScript or TypeScript. This allows you to write code that not only uses the Excel JavaScript API to interact with worksheets, but you can also use standard JavaScript code to do other things - such as interact with the REST endpoint on your IoT device.

 

The first step is to create an add-in. There is a handy generator tool to help with this, available on the Office Developer GitHub.

 

This tool is installed using NPM, then can be used to create add-ins for any Office app. For Excel, you can create 2 types of add-ins – Task Panes and Excel Functions.

 

 

 

Task Panes are panels that pop out when you select a button on the ribbon. These show HTML pages, with JavaScript or TypeScript code behind the scenes to respond to buttons being selected or other input.

 

 

Excel Functions are functions in Excel, as the name suggests. You can call these in your spreadsheet the same way that you would call the built-in functions such as SUM and VLOOKUP. Under the hood, it calls functions in your add-in, and these functions can either return a single value, or start a background timer that returns values that update on a regular basis, updating the value in the cell.

 

Whichever one you use, your JavaScript or TypeScript code runs inside Excel, so if you are running Excel on the Web via Microsoft 365 for example, this code runs inside your browser locally, not on a server somewhere. This means it can access resources on your local network, such as your IoT device. Inside your button or function code you can then use the JavaScript fetch API to get or set values on your IoT device.

 

You can find example code in the excel-addin folder in my GitHub repo.

 

Wiring it all up

 

Once you have your add-in written, you need to test it all out. Instructions for running and testing your add-in are in the generator documentation.

 

One thing to be aware of is you need to be using Excel on the web, not the local app. This is due to the fun of HTTPS certificates. Unfortunately, there is no way for your IoT device to have a fully valid certificate – certificates are based on the URL of your REST API, and your device is running locally, so won’t have a proper public URL.

 

Excel running locally will refuse to access your REST API without a valid certificate, but on the web, you can get your browser to allow the ad-hoc certificate running on your IoT device. What you need to do is to first load one of the REST endpoints in your browser, then when you get a certificate warning, select the option to allow access. You only need to do this once per certificate and after this Excel in the browser will be able to access your IoT device. If you are using Flask for example, every time your Flask app restarts it generates a new certificate, so you will need to re-approve it after every restart.

 

 

Start by loading the REST API in a browser and approving the certificate.

 

Once done, open an Excel workbook in your browser, then load the manifest.xml file for your add-in. You will then be able to access any Task Panes or Excel Functions from your add-in.

 

 

You can access from any browser - running on a PC, Mac, Chromebook or iPad!

 

If you want to then publish your add-in rather than have it running locally, you will need to host the content somewhere, such as Azure storage. You can find the instructions in the Excel add-in documentation.

 

Where next?

 

You can check out sample code to do this in my GitHub repo. Also check out the Office add-ins documentation.