Skip to main content

Stream Functions

Streams are the heart of a Streamsheet. They transfer information in and out and give the applications you are building their purpose. Add stream functions to connect the Streamsheet to the outside world. If you don´t know yet how to create streams click here.

Stream Function Wizard#

Usually Functions are created by entering “=” followed by the name of the function in a cell. This process is supported by the function inline help, which prompts suggestions and parameter support.

To further simplify the creation of functions the "Stream Function Wizard" was added. The Fuction Wizard supports mainly the creation of so called Stream Functions. These are functions which use a Stream (Producer/Consumer) to communicate with entities outside of an App and usually are of a more complex structure than regular functions.

To open the "Stream Function Wizard" select the cell, where the function should be inserted and click in the top bar on the Function Wizard Icon. The Wizard will open on the right side of your screen. Now first select the Stream Function followed by the Stream. Depending on the chosen Stream Function a set of different other parameter is required.

When finished click “Ok” to eventually create the function.

Note: In version 1.5 the icon for the stream functions was changed StreamF

FWDemo
Create a MQTT.PUBLISH function with the Function Wizard

HTTP Request#

This tutorial covers two different kind of HTTP/REST Calls, one is POST and the other is GET. (In older versions of Streamsheets we use the term REST, which is changed to HTTP starting v1.6) The differences are that with a "POST" information is transmitted from the HTTP Client to a server and a "GET" transmits information to the HTTP Client from the server. To add a HTTP function to a Streamsheet you will need a Connector and a Producer, based on the HTTP Client Provider. Start by creating the Connector in the Stream Tab of your Dashboard. You can leave all fields empty to be able to utilize this Connector for any URL. If you want to create specific Connectors for every webpage, type in the Base URL. Same goes for the Producer. Create a Producer based on the just created Connector and leave it empty. We can now select the Producer within the Function Wizard.

1. Method "Post": As an example, for a HTTP Request with the method "POST", we will create an alert service for Slack. Slack is a messenger, which offers the possibility to add self-made applications. This option will be used to add an Alert Application to a Slack channel, posting status updates.

To begin create such an App in Slack and add an Incoming Webhook. This generates the HTTP.Request URL we will be posting information to. Choose a Channel on Slack to add your Application to. This is where we will post the information to. Now we can start building an App in Streamsheets. Create a new App and create a JSON Range with the key Value being “text” and the Value being any message you would like to send. Create a HTTP Request function over the Function Wizard and select "POST" as the method. Add the created URL and select the JSON Range as the body. Choose a Target (e.g. a cell range) for the response message of the server. Now with every new Step, the App will send your message to Slack 😊

2. Method "GET": The "GET" method is very similar to the "POST" method, but in this case we do not need to define any message, because we will be consuming messages. A good example is the “API of Ice and Fire” https://anapioficeandfire.com/. It is an HTTP Request based online wiki for Game of Thrones related information. Depending on the URL, different Information can be accessed and displayed. Lets choose the following URL: https://anapioficeandfire.com/api/characters/583 It displays all information regarding Jon Snow in a JSON-Object. So lets again create a new App. Add a HTTP Request Function with the help of the Function Wizard. Enter the URL, select "GET" as the method and chose the Target, where the information should be displayed (e.g. INBOX). Confirm your input by clicking “OK”. After starting the App the requested information will now appear in the Target Range.

OPC UA#

star This is a Streamsheets Professional feature.

This simple Tutorial shows how to read information of an OPC UA Server. To begin with, we have to create Streams, which are connected the OPC UA Server of our choice.

Open up the Stream Tab of your Dashboard and create a new OPC UA Connector. Enter the URL of the Server you want to connect to. In this tutorial the OPC UA Server is in the local network. You will need your own OPC UA Server or go to http://opcuaserver.com/ where you can find a series of open OPC UA Server.

info

To connect to the Streamsheets internal OPC UA server use streamsheets-service-opcua as host name and api/v1.0/OPCUA/cedalo/machineserver as the resource path.

The now following producer and consumer need the specific node ids of your opc ua parameter. These consist out of a namespace indicator and the parameter id. (e.g. ns=2; s="Parameter") For more information see the OPC UA documentation

OPC UA Consumer & Producer: Create a new Consumer, based on the created Connector. Enter the Node ID you want receive and save your changes (the polling interval defines the interval where the Consumer requests new data from the server). Create a Producer, again based on the newly created Connector.

Now we are ready to poll and send information to the specified server. Create a new App and select the newly created Consumer. Just start the App and the Consumer will instantly forward messages into the Inbox.

OPCUA.READ: A second way to recieve OPC UA Messages in a Streamsheet is over the OPCUA.READ function. Create a new Streamsheet by clicking the plus sign in the bottom right corner and select a cell. Open up the Function Wizard, select the OPCUA.READ function and the created Producer. Enter the Node ID and the Target (e.g. INBOX) and confirm your input by clicking “OK”.

With every new calculation step an OPCUA.READ will prompt a new message in the Inbox.

OPCUA.WRITE: It is also possible to update existing OPC UA Variables with the OPCUA.WRITE formular. Similiar to before, use the Function Wizard and select OPCUA.WRITE. Choose the right Producer, add the NODE Id and Target Range.

TimescaleDB#

star This is a Streamsheets Professional feature.

The Streamsheets Professional version comes with a TimescaleDB installation. There is no need to setup any streams, the Timescale functions automatically connect to the database. It is a time series database (TSDB), which is a software system that is optimized for storing and serving time series through associated pairs of time(s) and value(s). It is based on PostgreSQL. To learn more check their website .

To work with Timescale, Streamsheets uses a bunch of different fuctions. TIMESCALE.INSERT, TIMESCALE.SELECT, TIMESCALE.CREATE_TABLE, TIMESCALE.SHOW_TABLES, TIMESCALE.DROP_TABLE and TIMESCALE.EXPORT.

The two most important functions are the inserting and selecting of data:

TIMESCALE.INSERT():

=TIMESCALE.INSERT(, TableName, ValuesJSON [, TableSchemaJSON])

TI

To fill a data table with information you can use the insert function.

Stream: Leave empty to connect to integrated TimescaleDB

TableName: Enter the table where the data should be saved.

ValuesJSON: Enter a JSON with the data to be saved. E.g. JSON(A1:B4)

TableSchemaJSON: If you want to write data in a new table, you don´t neccessarly need to use TIMESCALE.CREATE_TABLE. Adding your schema to the function will automatically create a new table, while inserting data.

The schema describes the structure of your table. It refers to the organisation of your table. Here you have to set the parameter names you want to save within your table and assign their data types. (E.g. "value1";"NUMERIC") Accepted data types are NUMERIC, TEXT, TIMESTAMP, TIMESTAMPTZ, DATE, INTEGER etc.

TIMESCALE.SELECT():

=TIMESCALE.SELECT(Stream, SelectJSON [, Target, XValue,])

TSA
Select all from table "TimescaleDbTable", but limit response to 10.

To query data use the select function. The select function uses the postgreSQL commands to query your data. Use a data range on your sheet to set your query command. These use the parameter value format. Use parameter like "select", "from", "where", "limit", "order by" etc. and define the comand within your value cell.

Examples:

select

selecting "time" and "Sensor1" values for all "Sensor1" data over 40


select

selecting max "time" and renaming the parameter in the response using the "as" command.


select

selecting all saved parameter for a certain message id and limiting the amount of responses to 10.

The select has to be a JSON, so use the JSON() function around the statement range.

Chart range:

You are able to base a chart range on TIMESCALE.SELECT() and display the response in e.g. a XY Chart. To do so, add the X axis parameter name for the chart in the TIMESCALE.SELECT() "XValue" parameter. E.g.: If you want to display the time in the x axis of your chart choose this function:

=TIMESCALE.SELECT(,JSON(A1:B3),,"time")

Now the time parameter will be displayed in the x axis of your chart. The chart only needs reference the select function as data source.

MongoDB#

To store data and retrieve information Streamsheets are able to connect to a MongoDB. Here you can learn how to add a MongoDB to your current Streamsheets installation.

Start connecting the MongoDB to your Streamsheets by setting up a MongoDB Connector. Enter the URL (or if working with Mongo for Docker the container name) in the Host(s) field. All other settings are optional. Streamsheets offers five Mongo specific functions within the Function Wizard: OPCUA.READ, MONGO.COUNT, MONGO.DELETE, MONGO.QUERY and MONGO.AGGREGATE. Setup a Producer on top of the just created Connector and you are ready to go.

In a Streamsheet open the “Function Wizard” to gain convenient access to the MongoDB functions.

MONGO.STORE():

=MONGO.STORE("Stream","Collection","Document")

MongoS

As soon as your MongoDB Connector and Producer is set up you can start storing data. Click on a cell, open the function wizard and select MONGO.STORE. Remember the calculation order of a Streamsheet (top to bottom, left to right), this might play a role in the structure of your logic.

Stream: The “Function Wizard” automatically selects a stream. Make sure the right one is selected.

Collection: MongoDB has different places to store data at. These are called collections. Type in a name of a collection. The collection will automatically be created, if not already existing.

Document: The document contains your data. It is a simple cell range of key and value pairs. It is also possible to order them in a hierarchy.

With every calculation one document will be stored in the selected collection.

MONGO.QUERY():

=MONGO.QUERY("Stream","Collection","Query","Target","ResultKeys","PageSize","Page","Sort","Timeout")

MongoQ

To lookup data from a MongoDB collection use the function MONGO.QUERY. It is possible to access either all stored documents or a subset of them.

Stream: Again select the right Producer to connect to the right database.

Collection: Type in the collection you used in the MONGO.STORE function.

Query: The query filters the data and only gives back documents, which are match or are composed of the query. Define one or more key value pairs the documents you are searching for have to contain. E.g. you have saved a lot of customer information and now need the information for all customer with the name “Julia”, define a horizontal cell range containing “name” on the left and “Julia” on the right. To access all data just leave this section empty.

Target: Define where the response of the database should be displayed. Choose a cell range or =INBOX(); =OUTBOX() as a target. Be careful, the cell range maybe to small to display all information.

Result Keys: The Query always gives back all the information saved in a document. If you are only interested in a subset write each key in a cell and enter the cell/ cell range in the Result Key field.

Page Size: The Page Size defines how many results will be shown in the response of your query per page. As a default, page 1 will be returned.

Page: Select the page you want to get back. Example: Enter page size = 5 and page = 2. The query will return entry 6-10.

Sort: You can either sort by time using 1 and -1 or you can sort alphabetically by using a cell range with the key to sort with and a 1 or -1. 1 = descending; -1 = ascending.

Timeout: Tells the function how long to wait for a response from the database(in ms).

MONGO.DELETE():

=MONGO.DELETE("Stream","Collection","Query","Target","Timeout")

MongoD

Delete documents in a collection.

Stream: Select the Stream connected to your MongoDB.

Collection: Enter the collection you want to adjust entries in.

Query: All documents with the defined key value pair/s will be deleted from the collection.

Target: MONGO.DELETE returns the amount of objects that have been deleted. To get this information define either a cell range of at least 2x2 or enter =INBOX()/=OUTBOX().

Timeout: Tells the function how long to wait for a response from the database(in ms).

MONGO.COUNT():

=MONGO.COUNT("Stream","Collection","Query","Target","Timeout")

MongoC

If you are interested in the amount of documents stored in a collection use MONGO.COUNT().

Stream: Select the Stream connected to your MongoDB.

Collection: Enter the collection you want to count entries in.

Query: Enter a cell range of key value pairs. All documents with the defined key value pair/s will be counted.

Target: MONGO.COUNT returns the amount of objects that have been counted. To get this information define either a cell range of at least 1x2 or enter =INBOX()/=OUTBOX().

Timeout: Tells the function how long to wait for a response from the database(in ms).

MONGO.REPLACE():

=MONGO.REPLACE("Stream","Collection","Query","Document","Upsert")

MongoR

To replace data in a collection, use the MONGO.REPLACE() function.

Stream: Select the Stream connected to your MongoDB.

Collection: Enter the collection you want to replace entries in.

Query: The query searches for documents within a collection to replace.

Document: Define a document to replace existing data with and enter the cell range here.

Upsert: TRUE or FALSE. Upsert decides, if, when a query can´t be found, the document is added to the collection or dismissed.

Here is a small example:

MongoDemo
All five Mongo functions used in one GIF.
Note: In version 1.5 the icon for the stream functions was changed StreamF

InfluxDB#

To store data and retrieve information Streamsheets are able to connect to a InfluxDB.

First of all we start by providing a HTTP Client Connector and the corresponding Producer. If these are not yet available, go to the Administration menu and create a new Connector and Producer. If you have any questions we recommend to have a look at the HTTP Request Tutorial.

Create a Database:
Create a database using the REST.REQUEST function. Click on the functionwizard, select the right Producer and use the POST Method. Add the desired database name at the end of the URL.

URL: http://influxdb:8086/query?q=CREATE DATABASE <Database name>
Example URL: http://influxdb:8086/query?q=CREATE DATABASE mydb

To create a database it is sufficient to execute the REST.REQUEST once.

Write Data:
To write data into the InfluxDB, we have to use the REQUEST function. The ParameterJSON range of the function contains 4 parameters. The first is the URL which must contain the name of the database. It`s also possible to add a password, precision etc. The second one is the method which is POST when writing the data into the InfluxDB. Third is the body in which the data is located. To successfully parse and write Data in InfluxDB you have to use the line protocol! The InfluxDB line protocol is a text based format for writing points to the database. A single line of text in line protocol format represents one data point in InfluxDB. It informs InfluxDB of the point’s measurement, tag set, field set, and timestamp. Tag set and timestamp are optional. It is important that the spaces and commas are set correctly!

Syntax: <measurement>[,<tag_key>=<tag_value>[,<tag_key>=<tag_value>]]<field_key>=<field_value>[,<field_key>=<field_value>][<timestamp>]
Example: h2o_feet,location=freiburg water_level=4

In our example we use a measurement called "h20_feet". The measurement has one tag key (location) which has one tag values: freiburg. "water_level" is our field_key which stores float field values in the "mydb" database.
Finally you have to add the "Json" Parameter with FALSE as value. That`s it!

influx

Write Data in InfluxDB with the REQUEST function

Query Data:
To Query Data we use the HTTP.REQUEST function with the GET Method and write the query into the URL. There are plenty ways to query your Data. You can have a look at them here. In our simple example we query all data of the last minute from our h2o_feet measurement.

URL: http://influxdb:8086/query?db=<Database name>&q=<your query>
Example URL: http://influxdb:8086/query?db=mydb&q=SELECT FROM "h2o_feet" WHERE time > now() - 1m*

Thats it! Now you receive the Data in your payload. It`s time to process the Data in this or another Streamsheet!

MAIL.SEND#

In This Tutorial we will send E-Mails to a Mail Account from our Streamsheet, using an SMTP Client.

First of all, create an SMTP_Connector. Therefore click on the Administration Menu and navigate to Connectors to create a new Connector, with a “SMTP Provider”. To be able to use the SMTP Connector, the Host-address, the Port and the Security Protocoll of the Mail Service that is used, needs to be typed in. For example the host-address for Gmail is “smtp.gmail.com”, the Port is 465 and the Security is SSL/TLS. (This may differ from other Mail-Services, their access data can be find on the internet)

For the next Step change to Producer and create a new Producer, using the previously created Connector. Now enter the sender mail address as User name and add the password below.

Even if the whole Setup is correct, there is a high chance that it will fail! For instance Gmail is blocking the access of this app, because it’s using a “low” security level. To give Streamsheets access to Gmail, enable “allow low security applications” manually on the Gmail website. After that the Connector and the Producer should be connected!

Now we are ready to switch to a Streamsheet. To insert the MAIL.SEND function in the Streamsheet, use the Function Wizard and select “MAIL.SEND” as the function and the previously created Producer as Stream. For “Text, Subject, TO” etc. either type it in directly or refer to a cell on the sheet. That`s it!

To prevent spam we recommend adding a condition to the MAIL.SEND function 😄

SMTP2
Sending mail`s with a condition
Note: In version 1.5 the icon for the stream functions was changed StreamF