Skip to main content
Version: Streamsheets 3.0

Streamsheets

Streamsheets is a powerful application used to build data driven applications. Streamsheets allow you to send, publish, store or provide data. Hence, they not only consume data streams but produce them as well. Similarly, as Excel worksheets form part of a workbook, Streamsheets sheets form an App.

Streamsheets can be used in all sorts of use cases. Combine different transport protocols, APIs, and databases to create your applications. Create applications that run in the background, add user interfaces, and create dashboards.

If you are new to Eclipse Streamsheets, this section offers a quick introduction to help you understand some basic principles. In a step-by-step guide, we then will build an App, which is meant to quickly illustrate how to generate, send and receive data with Streamsheets. More specifically, we will create data in one Streamsheet, continuously send it to the internal MQTT Broker and this way publish a data stream, which we will then receive in another Streamsheet.

Key Features​

Connectivity​

Streamsheets supports a number of protocols that you need to connect to sensors, databases, APIs and machines like MQTT, REST, MongoDB, Web sockets, and OPC UA. Create Connections in your Apps to and define your connection details. After the creation, use the connection in your inbox or reference them in dedicated functions on the sheet.

Persistence and database access​

You can store any value pair or more extensive data array in different databases. Streamsheets come with integrated time-series databases TimescaleDB, but also, the connection to MongoDB or other DBs is possible. You can also use HTTP requests to persist data.

Inbox​

The inbox receives and buffers incoming data. Each incoming message contains a payload, that can be used within Streamsheets. You can directly link into the inbox message and analyze the content of a message (payload). When ever a message comes in or defined intervals the incoming data is updated as the Streamsheet is recalculated from bottom top.

Sheet​

The sheet allows using a large set of formulas to analyze and visualize the incoming data. It also allows to transfer the data to other recipients like another app, a mqtt broker or other target using REST APIs. The sheet provides special stream related formulas to intermediately store and retrieve data. Besides recalculating, filtering and transforming data you can visualize data using various chart types and shapes. A dashboard allows to present data like a web page would.

Streamsheets Application​

Have a look at the Overview or the Guides to get a deeper understanding of all the different components that make up Streamsheets. They give an overview, how to manage, import or export Apps, organize user, groups and security and manage accounts to preconfigure data access.

But now let´s get started with a first real app!

First Steps​

Step 1: Create an App​

After logging in, go to the App Dashboard which lists all your Apps (you have access to). To create your first App, look to the top-right corner and identify the “Add” button (it is the “plus”). Click it and choose a meaningful name for your App, e.g. “Getting Started”. If you are working with Streamsheets Pro and have access to more than one workspace, you can also select in which the App should be placed.

Step1

This is how a blank application looks like

Step 2: Generate Data​

Since we do not have real data streams connected to our application yet, we simulate one to work with. A simple way to do this is to use the function RANDBETWEEN. It creates random values between a minimum and a maximum. Let´s create two values, one beneath the other, and name them. Write the names left off the values, e.g. “Temperature” and “Humidity”. Then, select all four cells, right-click and define them as a JSON Range. JSON is a data format, which allows to organize several key-value pairs in which the keys can be thought of as the parameter names for the respective values. Step2

Add RANDBETWEEN() functions for data simulation

Now, this cell range is not only a range in the sheet but also a JSON object which is continuously built with every calculation step of the sheet. Many protocols require the JSON format for their messages, but it is also good for structuring your message.

You can start the calculation process, by pressing the play button in the top bar. Step2

To create a data stream out of the created data simulation, we have to set up a connection.

Step 3: Create a Connection​

To allow data to either be streamed to or from the App it is necessary to define which connection details for each App. Without a connection an App only exits by its own (with the exception of HTTP functions).

The Connection menu can be found in the top right corner. Click the add icon to see a list of possible connection types. In our example, we will choose MQTT.

Step2

Here you can configure details for the connection to an MQTT Broker. Streamsheets comes with an integrated Mosquitto MQTT Broker, which we will use. In Streamsheets Professional you can use the existing account for the Internal Broker. The OS version does not come with accounts.

A connection can either be used as a reference in sheet functions, or used to assign an Inbox of a sheet as a target (or both).

Step 4: Produce a Message stream​

Now, we switch back to our sheet. To send the created JSON, we select an empty cell beneath the range and enter =MQTT.PUBLISH() in a cell. Choose the just created connection from the inline help. As message parameter select the JSON Range above. To complete the MQTT.PUBLISH function enter a MQTT topic. A topic in MQTT can be either published or subscribed to. We want to add information to this topic, so we use a MQTT.PUBLISH() function. The topic name can be any kind of UTF-8 string like "gettingstarted" or "example".

note

Streamsheets calculate from left to right and from top to bottom. This means, first, row 1 is calculated from left to right, then, row 2 followed by row 3 … until the end of the sheet is reached. As a consequence, you typically place a stream function behind the cells you want to send/produce. Placing the stream function before these cells will send the values of the previous calculation step.

Step 5: Subscribe to a data stream​

As described above, a Connection can not only be used to send, but also to receive messages. In MQTT Connections, it is possible to assign topics to inboxes of different sheets (Every sheet is assigned its own inbox).

First, create another Streamsheet in this App by clicking the “plus” icon in the bottom-left corner of the grid and select "add Streamsheet". In the new Streamsheet look to the top-left where it says “Inbox” and click it.

The inbox configuration opens on your right-hand side. Switch from the "Settings" tab to the "Connections" tab. Select your Connection and type in the topic name you chose to publish to above.

Done?

Great! You just set up connections to the local MQTT broker and created an App that produces as well as consumes MQTT data streams. Messages should be arriving in the Inbox of the second Streamsheet. If that is not the case, check whether your App is running (on “Play”), that your first Streamsheet has the calculation mode “Continuously” and that the topic is in MQTT.PUBLISH() is the same as in the Consumer.

Optional Step: Ideas on what to do next​

As mentioned in the beginning, this example serves as a quick introduction.

Hence, why not extend this Streamsheet model a little? By dragging and dropping data points that you receive in the Inbox into the grid, you create a direct link between the data stream and the spreadsheet world. Consequently, you can start processing data and build logic with spreadsheet formulas, e.g. to calculate KPIs, create dynamic dashboards and build decision engines.

In the example, we calculate the sum of the two values.

You will notice that you are working “live” on the message stream as incoming data is immediately transferred to the grid and formulas change their results accordingly. Additionally, you always see what the current result in each cell is. Remember: with every calculation step the whole logic is recalculated and, therefore, all conditions are reconsidered, and based upon these, decisions can be taken. For instance, you could send messages to an additional MQTT topic or a Slack channel but only if a certain threshold is met.

Further, sending data from one Streamsheet or App to another is useful when building larger models, however, typically you want to set up connections (Streams) to something outside of Streamsheets, too. Hence, why not set up a new Connection to get your Streamsheets communicating with the outside world?