Supported devices:
NETIO AN55 shows how to use Integromat to connect all NETIO devices to Google Sheets. At the input, there is a NETIO device measuring the electricity consumption at its 4 outputs. At the output, there is a Google Sheet with hourly Wh consumption data (24 columns per day).
Integromat
Integromat is a cloud service for automating processes that would otherwise have to be done manually. Integromat can connect applications as well as pass and modify data. The Integromat cloud service runs 24/7 without a need for user interventions. An advantage of Integromat is its graphical UI which partially replaces the need for coding.
Struktura AN55
- First-time setup of NETIO AN55
- Possible customizations the Integromat example
- FAQ
Download AN55 configuration for Integromat
- Integromat_import.json – file for importing the scenario
- Integromat_data_structure.json – file for creating the data structure in Integromat
1) First setup of NETIO AN55
Setting up the Data store
After creating an account and logging in, your account dashboard appears. Go to the Data stores page, where we will set up a Data store to use.
Integromat cannot keep a value in between successive runs of a scenario in a variable. For that purpose, we can use a Data store block which acts as a database. However, instead of adding new records to the database, a single record will be continuously updated to simulate a variable.
In order to use Data store blocks, a Data store (database) needs to be set up first.
A new data store can be added by clicking the Add data store button in the top right corner.
This opens a window where you can specify the name of your data store and its data structure. The data structure in a Data store specifies what kind of data can be stored in your Data store.
Click Add to add a new data structure. Another window to set up the data structure appears. The data structure can be set up in two ways:
- Manually, by adding and configuring individual elements using the Add item button
- In an automated way, by using the Generator button to automatically create a data structure from sample data
Let’s use the automated way. After clicking the Generator button, another window appears and we can paste our sample data there. Select the JSON content type and copy the contents of the following JSON file to the Sample data field. Then click Save.
Integromat_data_structure.json
Now you should see the automatically generated data structure. Finally, save the data structure and then save the Data store. (Caution: If you click outside of the current window, the window closes without saving. It is therefore recommended to keep the correct sequence).
If the data store was successfully created and set up, it is shown now.
Creating and setting up a scenario
Your scenarios (both active and inactive) are on the Scenarios page.
At the Scenarios page with all your scenarios (empty at this moment), click Create a new scenario in the top right corner.
A page is shown with all the applications that Integromat can integrate. Skip this step.
If the scenario was correctly imported, you should now see this:
Nastavení Webhook bloku
The Webhook block connects your scenario with your NETIO device. The NETIO device periodically sends data using the NETIO Push protocol to the address generated by the Webhook. The Webhook in Integromat receives and processes the data.
First, the schedule for processing incoming data needs to be configured in the Webhook block. In this example, the period for pushing data from the NETIO power socket device is configured in the device itself (once every X minutes), so the schedule in the Webhook block can be set to Immediately so that data are processed as soon as they are received.
Then, generate an address to which your NETIO smart socket device should send the data.
Note: The schedule is configured by clicking the clock next to the block. The configuration of the block itself is displayed by clicking the block.
Now we need to generate a new webhook (an address to which the smart socket sends data).
Here, you can set a name for the webhook to improve clarity and restrict the IP addresses from which data can be received.
After saving the changes, a new webhook (an address) is generated. Click Copy address to clipboard.
Now open the web interface of your NETIO smart socket device that supports the NETIO Push protocol.
Paste the webhook address to the Target host http server: field. Set the rest of the parameters as shown below.
Push Period [s] defines how frequently the NETIO device sends data to Integromat.
Save the changes and click Push NOW to upload the data manually to the Webhook block so that the block can determine the incoming data structure.
When the data structure is successfully determined, save the configred Webhook block.
Note: It may happen that the Webhook block does not receive the data at the first try. In that case, try again after a few moments.
Setting up the Data store blocks
Data store blocks are used to access your database. AN55 uses two types of Data store blocks:
- Update a record – This type is used to update the energy reading for the given hour as read from the NETIO smart socket device
- Get a record – This type is used to read the energy readings for the entire day
Update a record
The only thing to select in Data store blocks is the Data store configured in the previous part of this AN55.
Simply select your Data store.
Then, the configured Data store block opens. Values can be written to the fields in line with the data structure defined for the Data store.
Note: The configuration of all blocks is imported as a json file. However, for some blocks, it is necessary to first select an element in the background (Data store blocks = Data store, Google Sheets blocks = Google connection and sheet)
- Key – This value is used to find the record to work with in the database (Data store). In AN55, the values in this record are updated later on. It is important that all Data store blocks (Update a record and Get a record) in the same scenario (will be explained later) contain the same key value.
- Insert missing record – This parameter determines whether a new record is created in the database (Data store) if there is no record yet with the given key. Here, it is used to automatically create the record at the first run. Later on, this record is updated only.
Get a record
Just as with the “Update a record” Data store block, simply select your Data store.
Note: As a reminder, the Key value must be identical in all Data store blocks within a single scenario. The value is imported together with the whole scenario but it can be changed.
What the Data store blocks do
The main purpose of the Data store blocks is to update the energy reading in the database (Data store) every hour with the energy reading from the NETIO smart socket device for the given hour. The if() function is used to achieve that.
For example, this works as follows: The formatDate and now functions are used to get the current hour (12 or 23 and so on). If the current hour matches the hour stored in the block, the current energy reading from the smart socket is stored to the database for the respective hour. If the current hour does not match, nothing happens.
Configuring the Google sheets block
First, create a new sheet in Google sheets with the following on the first line:
Note: Give the Google sheet a suitable name, you will need it later.
Then, copy the following formula to cell AB2:
=ARRAYFORMULA(IF(ISBLANK($A3:$A);"";MINUS($AA3:$AA; $D3:$D)))
Note: Depending on your region and language, you may need to use commas instead of semicolons.
This formula calculates the consumption at the socket output per day (hour 24 – hour 1). This will be calculated for every new row added by Integromat.
Now, in both Google sheets blocks, configure the Google connection and the Google sheet that you have created.
First, add your Google account.
Another window opens where you can give a name to your Google connection. Click Continue to open the standard Google login dialog. Log in and allow Integromat to access your Google Disk and Google Sheets.
After a successful login, the configuration of the Google sheets block opens. In the Spreadsheet field, select the Google Sheet that you have created. The other fields should be the same as in the screenshot.
The resulting table should look like this:
Note:
The data source in this example is a NETIO PowerDIN 4PZ device and two of its metered outputs (defined by the Output column). Nothing was connected to output 2. The purpose was to show in AN55 how to connect a NETIO device with multiple outputs.
This sheet can be connected to other sheets that further process the data and extend AN55 according to your needs.
AN55 is now fully set up.
2) Possible customization
Using one output only
AN55 is ready for a device with 4 metered outputs. To push the data from one output only, the AN55 Integromat example needs to be modified at two places:
1) Data structures
Open the Data structures tab.
Then, click Edit to edit the data structure you are using.
Finally, delete the unused outputs (output_2, output_3, output_4) from the data structure. This also deletes the corresponding fields from the Data store blocks in your scenario.
2) Scenarios
Delete the second Google Sheets block. Each block handles one output.
Using 3 to 4 outputs
In order to push data from 3 or 4 outputs, AN55 needs to be modified at two places:
1) Data store blocks (Update a record only)
Open the settings of the Data store block and copy the following code to all fields corresponding to the output you want to use.
{{if(formatDate(now; "HH") = 00; 1.Status.Outputs[3].Energy; ignore)}}
1.Status.Outputs[3].Energy
This refers to the energy reading at the third output (output indexes start from 1) in block No. 1.
That block should be your Webhooks block; however, if it was for instance deleted and then inserted again, the block number may be different and the code needs to be modified.
After pasting the code, the value of the hour that is to be compared (0 in the code) needs to be modified to the hour corresponding to the field. For example, for the hour_14 field, the hour value should be 13 because hour_14 refers to 13:00 to 13:59 (the first field is hour_1, referring to 00:00 to 00:59).
Finally, the output index needs to be correct. Here, it is Outputs[3] (the third output is [3] and the fourth one is [4]). It is best to modify this number in a text editor before copying, instead of changing it 24 times.
We recommend to modify one of the Data store blocks and then clone it in place of the second one (to clone a block, right-click it and select Clone).
The index can be modified manually after clicking the number in the square brackets ([3]).
Note: Whenever a portion of your code turns black, it means that there is a syntax error.
2) Google sheets blocks
One Google Sheets block handles one output; therefore, more blocks need to be added.
Right-click the block to clone and select Clone. Then, connect this block after the last Google Sheets block.
In the settings of the cloned block, change the output number (this is for clarity only) and all the values in the fields (delete the old value first) so that they match the output.
For example, this is a cloned Google Sheets block for output 2 that we want to modify for output 3. So, the output number must be changed from 2 to 3, and then the values from output 2 need to be changed to values from output 3 (always make sure to read the value from the correct output).
Using multiple NETIO smart socket devices
In order to use multiple NETIO devices in parallel and store the data in a single Google Sheet, first clone the scenario for each of the devices. (For example, 3 scenarios are needed to read data from three smart socket devices).
In the new scenario, set a new Key value in all three Data store blocks so that the new device has its own record in the database (Data store).
In each scenario, create a new webhook and connect it to the respective device.
In the end, each additional scenario will have its own device, webhook and Key (within each scenario, the Key value is the same in all Data store blocks)
FAQ
- Is it possible to test AN55 with the online demos of NETIO products?
No, NETIO products in the Online demo mode do not support the http push protocol (the target address cannot be configured). Hence, there is no way to access the Integromat webhook.
- How long do the operations in the free-tier Integromat account last for?
With a single NETIO smart socket device, the free operations will last for about 20 days. If you want the 1000 operations (included with free Integromat accounts) last for the whole month (28-31 days), the period for sending data from the smart socket device needs to be increased from 1 hour to 2 or more hours
- Is it possible to use NETIO AN55 with NETIO PowerPDU 4C?
No, because PowerPDU 4C does not support the “Http Push” protocol (and neither does NETIO 4 / 4All).
- Is it possible to use NETIO PowerBOX 3Px?
No, because NETIO PowerBOX 3Px does not support electrical measurements (and neither does PowerPDU 4PS).
It will be possible to use the upcoming PowerPDU 8QS as it supports measurements at outputs 1 and 0 (entire device).
- What should I do if a part of the code in Integromat block settings turns black?
If any part of a code in an Integromat block settings is black, it means that there is a syntax error.
Supported FW versions for NETIO 4x:
3.1.0 and higher (Archiv firmware)
Supported FW versions for PowerCable xxx:
2.0.2 and higher (Archiv firmware)