General

This section covers general elements and tips & tricks around using Eclipse Streamsheets more efficiently.

JSON Range

Streamsheets are using the JSON format to transfer and process data. Creating a JSON-Range in a Streamsheet helps you to easly structure your data keys in a hierarchical order. This is needed when you are about to transmit data and need it structured in a certain way. In a Streamsheet this is possible by writing all your key value pairs below each other. After (or before) you are done, select all cells and right-click. Choose “Define JSON-Range” on the bottom of the menu. Now you can start structuring your data using TAB and Shift+TAB to change the hierarchy of a key value pair.

Create a JSON Range

Now the function JSON(C5:D11) will automatically include the implemented hirarchy, which prestructures e.g. message contents.

{
“Interesting Data 1”:12,“Interesting Data 2”:15,“More Data”:
{“Data from A”:17,“Data from B”:1234,“Data from C”:625, “Data from D”:892}
}

IF Column

The IF-Column is on the left side of your Streamsheet next to column A and offers one individual IF-Cell per row.
IF-Cells are a different version of the IF function, but instead of influencing only one cell they influence all cells in their row! When the given condition is met, the IF-Cell states “TRUE” (green background), if not it states “FALSE” (red background), either" turning on" or “off” the affiliated row.

Add a condition to a whole row

In this scenario the IF-Cell is checking if the “Interesting Data” from the Inbox is over a value of 200 (see edit row). As long as this condition is not met, the whole row is not calculated. If at some point the value exceeds the set condition, the row would be set active again and the MQTT.PUBLISH function in B14 would send a message.
This is just one of many example cases the IF-Cell could be used.

Data History/Table

Often it is important to not only work with single real-time data points, but to build a history and work with a series of values. Besides using databases you are able to store a finit amount of information within your running application. Check out database examples in the stream function tutorials.

References:

A very easy way to not only create a data history, but a full blown data table, which updates overtime can be done by understanding the very core principle the Streamsheet calculation process. Every sheet is calculated from left to right and from top to bottom. Consequently, cell A1 is calculated first and then B1, C1 etc. before the next rows follow. Thanks to this concept, we can use references to achieve our goal. Create a table and reference the bottom of the table to the incoming data. Now add in every cell above one cell down facing references. Since cell above another cell are calculated before the other, the reference one step in the past. this way the table will slowly but surley fill up with information.

Stack:

The same result can be achieved by using the function STACKADD(). STACKADD() will add a value with every step to the stack. When the created table is full, it automatically deletes the oldest entry. There are further stack functions, which could also help to keep your table up to date. (STACKDROP(), STACKFIND(), STACKUPSERT(), STACKSORT() & STACKROTATE()))

Timeseries:

Another greate way would also to use tieseries functions. Those have their own tutorial section.

Loop

Inbox messages can consist out of different objects. There are the regular parameters like boolean, number or string elements, which are holding the actual information, and there are structural elements like "dictioniaries" and "arrays". The structural elements are ordering the informations in different hirarchies. While "dictionaries" only serve as folder elements to organize message data in different level , "arrays" usually follow a certain style and represent an ordererd list of values. They repeat the same form for a certain amount of times. For example, if a message should hold different information for the same parameters an array would be the perfect choice.

Simple example: You could structure multiple sensor information from the same parameter in one message. Lets say you have three sensors, who all measure temperature: Sensor 1 measured 10°C, Sensor 2 measured 20°C and Sensor 3 measured 12°C.

A JSON object capturing this information would look like this:

[    {
"sensor_id":"S1";"
"temp":10
},
{
"sensor_id":"S2";"
"temp":20
},
{
"sensor_id":"S3";"
"temp":12
}    ]

In a Streamsheet Inbox this would look like this:

JSON Array in Streamsheets

In Streamsheets it is now possible to loop these arrays. When looping an array, every array object gets treated as it would be part of a single message. Usually one message takes one step to be processed. Adding a loop to our example message, would now result in three steps. In every step only one of the three information blocks would be read. There are basically two main reasons, why this seems practical.

1. The array is filled with messages you want to process one after another.
2. The array varies in size and you never know how big an array could get. So adding simple READ() functions does not cut it.

Adding a loop element is quit simple. Wait for one message to arrive in the inbox, right click on the top array element and select "Set Loop Element". It is also possible to set the loop element in the Inboxsettings. There you can also add the option "recursivly", if you not just want to loop through an aray, but want to loop through more then just one level of an "array" or even "dictionary" object.

Payload:

right click on "Data" to set loop element

Inboxsettings:

add [Data] to the Inbox settings to add loop element below "Data"

Now the incoming message will be treated in three singular steps. You may notice, that dragging and dropping the Payload items now creates a different function then usually.

In a loop:

=READ(INBOXDATA(,,,"sensor_id"),G8,"String",,TRUE)

No loop:

=READ(INBOXDATA(,,"0","sensor_id"),C17,"String",,TRUE)

The loop function is not fixed upon the "0" element and therefore is reading the payload information for every loop step. This way one READ() function covers all loop steps.

EXECUTE()

=EXECUTE(Streamsheet [, Repeat, JSON, Pace])

EXECUTE() is a function which when triggered executes one or multiple steps on another sheet. In order to properly work, the other sheets calculation mode has to be set to "on execute".

While a sheet is being executed, the EXECUTE() function waits for an answer and pauses the calculation. The answer can simply be the calculation step of the other sheet being finished (empty answer) or a triggered message through the RETURN() function.

EXECUTE() has different parameter to work with. The simplest way to execute another sheet is just one time. You can set the amount of executions in the "repeat" parameter. E.g.

If you are in need of multiple execution steps think about the pace you want the sheet to execute. You are able to set it in milliseconds in the "speed" parameter or you can leave it blank to set it to the cycletime. (starting v2.3)

info

Setting the right speed is important, because an EXECUTE() function is waiting until all executions are done. While this process is ongoing, another message may arrive in the inbox of the sheet with the EXECUTE() function, creating a queue which will never grow smaller.

PDF

It is possible to save a Streamsheet as a pdf file. While in a App open the left side menu and select "Export PDF". Set a filename and click on "export". The file will be saved in your downloads.

DOT Notation

In version 2.5 we introduced the DOT notation, which allows a new kind of reference system. Usually, references only reference to a cell or cell range. The DOT notation goes one step further. It allows breaking down hierarchies and reference to objects, which usually are not directly reachable. These hierarchies can be part of a JSON object, messages in the inbox or values from shapes.

Usage

Using the DOT notation is very simple.

Syntax:

=Cell/Shape/Inbox/Outbox.”level1”.”level2”.”parameter”

First, point to the location of the object you want to reference to. Then, descend the hierarchy until you reach the parameter you wish to display the value of. Between every level, insert a DOT. Make sure to use double quotes.

Example:

In cell A1:

{"level1":{"level2":{"parameter1":12,"parameter2":31}}}

To get the value 12 as a result use:

=A1.”level1”.”level2”.”parameter1”

Dynamic references:

Besides using static DOT notation, it is possible to use references for each level.

=A1.A2.A3

This way, it is possible to dynamically adjust the output of a reference. It is further still possible to use distinguish between relative and absolute references by using the $ sign.

=A1.$A2.$A$3

Cell level

To work with the DOT notation on cell level, it is necessary to have a JSON object inserted in a cell. There are multiple ways this could happen.

Via a query: Use the target parameter of a query on only one cell and the response will be paste in the cell as a JSON object. Via function: JSON() is a function, which creates JSON objects.

Reference the cell with the JSON object in position one. Now add the hierarchy level until you reach the parameter.

Inbox & outbox

Besides using JSON objects in a cell, it is possible to directly link the in- & outbox to a cell.

Example:

In the inbox, the following message arrived:

=INBOXDATA.”level1”.”level2”.”parameter1”

This DOT notation will again result in 12. Other syntax in this context:

INBOX INBOXDATA INBOXMETADATA

OUTBOX OUTBOXDATA OUTBOXMETADATA

Shapes

Another very powerful application of the DOT notation is using it to identify shape content. Use the name set in the submenu “General” of your shape properties and enter:

=Name.”VALUE”