There are multiple tools and elements in Streamsheets to help you build your apps into a fully functional dashboard. Compose your own UI out of different shapes, interactive elements and charts.
Charts are already covered in this section.
In some scenarios it is necessary, to not only control data streams and implement certain logics, but create dashboards to present the data to an end user. Dashboards are popular visual displays of data, mostly comprising charts / graphs with striking attention seeking components. If you want to learn more about how to setup Charts in Streamsheets click here. In this tutorial we cover another very useful tool Streamsheets offers, which is adding shapes. Shapes can be found next to the Chart icon in the App toolbar.
There are a ton of different shapes, which can be colored and sized the way you want. As everything else in Streamsheets, Shapes use functions as a base. A function can be used to add dynamic settings. For example, it is possible to dynamically set the size, coordinates or displayed information of a shape, just by referencing cells from a sheet. All shapes have a very similar function structure:
=DRAW.RECTANGLE(UnqiueId, Container, Name, X, Y, Width, Height,LineFormat, FillFormat, Attributes, Events, Angle, RotCenter)
Format changes can be done using simple inputs like hexadecimal codes for colors or more complex inputs using functions like FILLPATTERN() (add pictures via urls) FILLLINEGRADIANT() FILLRADIALGRADIANT(), (add color transitions) LINEFORMAT().
One important distinction is that only lable are able to display information as they have a "Text" parameter. Lable can be created over the shape menu or by double clicking a shape.
=DRAW.LABEL(UnqiueId, Container, Name, X, Y, Width, Height, LineFormat,
FillFormat, Attributes, Events, Angle, RotCenter, Text\[, Font\])
Example use case: The dashboard should be able to represent different information in different tiles and give visual feedback. For each information you need representation create a shape e.g. rectangle and place it on the sheet. To display information double click to create a lable within the rectangle and link the "Text" parameter to the cell with the required information. To further visually support your given information, let´s switch back to the rectangle shape. In the "Fillformat" parameter we will now enter: IF("cell reference"<300,"green","red"). Now a threshold of 300 is set to decide, wether the rectangle color is red or green.
If your use case does require direct user input, there are multiple ways to allow these in Streamsheets. Before we dive into details, here a short reminder:
Always hide your logic from the end user. They will likely get confused and/or change it.
Only display the Streamsheet you want the end user to see. Open the App Settings and select it in the “Maximize Sheet on load” field
Only share your App with end user over an URL that prohibits them from changing your logic.
There are multiple ways you can allow an end user to adjust the visualization of your dashboard.
A very fast and easy way to allow user interaction is the SELECT() function. It allows the end user to select a value from a list of options. This selection then can be integrated in your sheet logic.
Example use case: A dashboard is created to gather information on different type of animals in a zoo. The user is allowed to select from a list of animals. Depending on which animal is selected, the represented information change. Select “tiger” and the amount of tigers, their names etc. will be displayed or select “koala” to see all respective information about koalas.
In the shape menu, in the controls category, you can find interactive elements. In a stopped or paused app, these elements can be adjusted as usual, by selecting the shape and changing the formula parameter. In a running app "control" shapes become interactive and are not selectable anymore.
Checkboxes are very helpful in use cases, where switching between two states is necessary. They switch their value parameter depending on the user interaction to FALSE or TRUE. To work with these states use a cell reference in the value parameter. Now you only have to use the referenced cell in your sheet logic to react on state changes.
Example use case: You want to only show certain information, when a check box is selected. FALSE represents that the information will not be shown. If the information you would like to hide is represented in an e.g. rectangle shape you could use the attributes parameter of the DRAW.RECTANGLE function to switch between visible and not visible. Find more about the attributes' parameter here.
Slider allow the selection of values between a given minimum and maximum. Again referencing the value parameter to a cell gives you the power to integrate the selected value into your sheet logic.
Example use case: An End User should be able to regulate a thermostat between the values of 15°C and 25°C. With the slider the user sets the wished temperature and the value gets set in the referenced cell of the value parameter. The value will be then published to the thermostat over e.g. an MQTT Stream. Now the user directly impacts the temperature by moving the slider.
The Knob works exactly like the slider, but instead of sliding from left to right the end user animation is turning a knob.
Button, like the checkboxes, set TRUE or FALSE values. The difference is, that they only set the value parameter to TRUE, as long as the user is pressing the button. After the release, the value turns back to FALSE. Again using a cell reference for the value parameter is the key to integrate the interaction in your sheet logic. Tip: Try to use EDGE.DETECT() if you only need a one time switch to TRUE.
Example use case: An end user should be able to open a valve by pushing a button on the screen. The valve only knows two states “open” and “close” and is connected to a MQTT broker. The payload of the message will now change dynamically and depending on the state of the button either “open” or “close” will be published.
The EVENTS() function adds another layer to end user interactions. In every shape formula there is one parameter called "Events", which can be filled with a number of event functions:
Integrating one or more events in the EVENT() function, adds the
possibility to define actions. For example:
EVENTS(ONCLICK(COPYVALUES(A1:B11,G10:H10)) triggers the COPYVALUES
function after an onclick action by the user.
Other functions which could be used in this context:
- MQTT.PUBLISH (or any other stream function)
- STACKADD (or any other stack function)
It is possible to add images to your Streamsheet.
Every object has different fill styles. Create an object and open the format menu of the "Object Properties".
Fill style three correspondes to images. You are able to set up images as the fill input. You are also able to connect the fill input directly to the sheet by using a reference.
Add images, by dropping them on the sheet of your choice.The maximum amount of pixel for an image is 250.000 (e.g. 500*500px). Simply drop an image on a sheet to add it. This automatically creates a DRAW.RECTANGLE() function containing your image file. Check the properties menu. The drag and drop interaction automatically converted your image into e.g.: `dataimaged1db85a8-4445-4126-9377-9dc74aa36d74.
You can also load your images into Streamsheets by using (Data)URLs. This makes sense, if you dynamically want to change pictures. Get a new (Data)URL in your sheet and display it directly.
URL Syntax: Has to be a direct link to an image e.g.: https://cedalo.com/wp-content/uploads/2021/05/cropped-Cedalo-red-logo-1.png
Data URL Syntax: Has to be base64 encoded. Use the following prefix:
data:image/jpeg;base64, ("jpeg" can also be a different file format) & add the base64 values behind the comma.