Spreadsheet widgets in Elementi 2016

This page is about the spreadsheet data-driven widgets included into Elementi 2016. For other versions, see Spreadsheet widgets page.

Introduction

Spreadsheets are commonly used to store data in tabular form (i.e data cells arranged into rows and columns), like sport scores, employee birthdays, production results, price lists, poll results, restaurant menus, etc. The spreadsheet widgets can be used to display the content of such files within digital signage projects.

The following spreadsheet data source types (further called "channels") are supported:

• CSV spreadsheet file

  This is a delimited text file that uses a comma (though other separators can be used as well) to separate values. Almost all spreadsheets and database management systems can export data as CSV file - some examples include: Microsoft Excel, Google Sheets, OpenOffice.org Calc, LibreOffice Calc, Numbers (spreadsheet) etc.

  The CSV file can either be imported into the Elementi project (total autonomy) or be located on a web server and retrieved from there by the HMP via Ethernet (requires a reliable network connection). The referenced CSV file cannot be located on your PC or on a network shared folder.

• Excel file

  This is part of the Microsoft Office suite of applications, servers, and services developed by Microsoft; a web-based version is also available. Both newer and older Excel file formats are supported as data source.

  The Excel file itself cannot be used by the HMP, thus it cannot be imported into the Elementi project, nor be referenced from a web server. Instead, the spreadsheet data is retrieved by Elementi from the Excel source file and included (in CSV format) with the content at the time of publish on the HMP; to synchronize further changes within the Excel file, you need to re-publish the project on the HMP.

• Google Sheets

  This is part of a free, web-based software office suite offered by Google within its Google Drive service. The suite allows users to create and edit documents online while collaborating with other users in real-time. See more about Google Sheets.

  The Google Sheet data is retrieved from Google servers by the HMP via Ethernet (requires a reliable network connection).

Spreadsheet widgets

Under the "Spreadsheets" folder of Elementi 2016's widget library, you can find eight ready-to-use widgets that automatically display data extracted from a spreadsheet data source:

Spreadsheet widgets in Elementi 2016

• Text Bar

  Displays text content using a fixed number of lines and applying a bottom-to-top & line-by-line scrolling effect whenever the entire text doesn't fit into the widget area.

• Text Ticker

  Displays text content using a continuous horizontal scrolling effect. The font size is adjusted automatically for the text to fit into the widget area.

• Text Roll

  Displays text content using a continuous vertical scrolling effect. The font size is adjusted automatically for the selected number of lines to fit into the predefined area.

• Slideshow

  Displays slides composed of text & media layers, which are automatically filled out with data retrieved from the CSV spreadsheet. The slides are displayed one after another, with or without a transition effect in between, similarly to a playlist.

• Horizontal Table, Vertical Table, Table, and Grouped Table

  Displays slides composed of multiple cells of text & media layers, which are automatically filled out with data retrieved from the CSV spreadsheet. The slides are displayed one after another, with or without a transition effect in between, similarly to a playlist.

Notes:

• For proper display of the data, make sure that the spreadsheet file is encoded using UTF-8 - otherwise, non-Latin characters might not display correctly.
• The current version of these widgets is not compatible with multiscreen content.

Configuration

Each of the widgets above can be customized using the options provided under "Properties" tab - for more details go to the dedicated page of each widget type, linked above.

To edit the properties related to the data source, click on the button. This opens the "Data Properties" dialog, which offers different parameters depending on the selected channel:

• CSV File (default)
• Excel File
• Google Sheets

Note:

At any point, the data source configuration can be tested by clicking on the "Test..." button.

CSV file channel

When the channel option is set to "CSV File", the "Data Properties" dialog offers the following parameters:

"Data Properties" dialog for spreadsheet widgets - CVS file channel

• Channel

  Select the spreadsheet data source type between "Excel File", "CSV File" (default), and "Google Sheets".

• URI

  Enter the URI to the CSV file - this can be a local path into the project or a remote HTTP(S) address.

• Refresh

  Specify the period after which the widget should refresh the spreadsheet data (this is useful especially when the widget is running in a loop); either select one of the provided options ("When document opens", 5m, 10m, ... , 24h) or enter a number, optionally followed by s (implicit), m or h; the minimum accepted value is 60 seconds. Using "When document opens" will refresh the data only when the document is re-opened.

• Separator

  Select the column separator used within the CSV data, from the following: comma (,), semicolon (;), pipe (|) and tab.

• CSV fields are not quoted

  Enable this when the data fields inside the CSV file are not quoted. When not checked, automatic quote detection is done.

• Cell range

  Enable this option and specify the range of cells (e.g., A1:B3) to be kept from the data set.

• Transpose

  Enable this to convert a vertical range of cells to a horizontal range, or vice versa.

• The table has a header

  Specify whether the first row of data contains the column headers.

• Specify output

  Enable this when the spreadsheet file does not have column headers on the first row, and enter the column names and their index in the table below.

Excel file channel

 

Using this channel requires that the Excel application is installed on the PC running Elementi 2016.

"Data Properties" dialog for spreadsheet widgets - Excel file channel

When the channel option is set to "Excel File", the "Data Properties" dialog offers the following parameters:

• Channel

  Select the spreadsheet data source type between "Excel File", "CSV File" (default), and "Google Sheets".

• Workbook

  Click on the browse button and select the Excel file to import.

• Sheet

  Select the sheet containing the data to import.

• Refresh

  This is set to "On publish" and cannot be changed. For more details, see the note below.

• Cell range, Transpose, The table has a header, Specify output

  Same as above.

Note:

The local changes to the Excel file cannot be automatically synchronized on the HMP - the spreadsheet data is retrieved from the Excel source file and included with the content at the time of publish on the HMP; to synchronize further changes of the Excel file, you need to re-publish the project on the HMP.

Google sheet channel

Using this channel requires that having your Google account connected with the Cockpit account.

Moreover, your Elementi license must be registered into Cockpit and your target HMP must be added into Cockpit.

"Data Properties" dialog for spreadsheet widgets - Google sheet channel

When the channel option is set to "Google Sheets", the "Data Properties" dialog offers the following parameters:

• Channel

  Select the spreadsheet data source type between "Excel File", "CSV File" (default), and "Google Sheets".

• Account

  Select one of the connected Google accounts or click "Connect account..." if none was previously connected.

• Workbook

  Click on the browse button and select the Google sheet to use.

• Sheet

  Select the sheet containing the data to import.

• Refresh

  Specify the period after which the widget should refresh the spreadsheet data (this is useful especially when the widget is running in a loop); either select one of the provided options ("When document opens", 5m, 10m, ... , 24h) or enter a number, optionally followed by s (implicit), m or h; the minimum accepted value is 60 seconds. Using "When document opens" will refresh the data only when the document is re-opened.

• Cell range, Transpose, The table has a header, Specify output

  Same as above.

Google channel connection

Using the Google Sheets channel within the spreadsheet widgets requires having your Google account connected with the Cockpit account where your Elementi license is registered. For that, follow these steps:

1. Select the "Channel" option as "Google Sheets".
2. Select the "Account" option as "Connect account...".
3. Click the URL displayed within the "Connect Account..." dialog.
   • If you are not signed into your Cockpit account, you are asked to do that.
   • If the your Elementi license is not registered into Cockpit, you are asked to do that.
4. The Google sign-in page is opened;
   • Follow the steps on that page to sign into your Google account.
5. Allow the SpinetiX app to access the specified information of your Google account.
6. Return to Elementi and click the "OK" button within the "Connect Account..." dialog.
7. At this point, your Google account appears in the "Account" drop-down box and your Google sheets automatically appear in the "Workbook" drop-down box.

Note:

The Google account can be disconnected at any time from Cockpit from the "Channels" page.

See also

• Data feeds
• Data-driven widgets
• Elementi 2016 Widgets.
• Display the content of a spreadsheet file
• Spreadsheet widgets in Elementi 2015
• Feed widgets in Elementi 3.x
• Birthday widgets
• Get data from Excel file
• CSV data feed projects