Excel Online Connector

The Excel Online connector allows data to be imported from an Excel Online spreadsheet into your Squirrel project at runtime. It also gives options for filtering and refreshing that data.

Add an Excel Online connector to your project by opening the CONNECTIONS drawer in the object browser, select Excel Online from the dropdown box and click the plus button:

The Excel Online connection will appear in the CONNECTIONS drawer of the object browser. Select it from there to access its properties in the property panel.

Excel Online Share Settings

Prior to linking the Excel Online connector in Squirrel to the Excel Online spreadsheet, you will need to ensure that the sharing option of the Excel Online spreadsheet is set to grant access to anyone using the share link.

1. Access the share options from the Share button at the top of the Excel Online spreadsheet, or from the File menu.
2. Set to ‘Anyone with link can view’:

3. Click ‘Apply’.
4. Click ‘Copy link’.

Excel Online Connector Properties

DEFINITION

• Excel Online share link – This property can be set in two ways:
  • Paste the share link (copied to clipboard in step 4 above) of the Excel Online spreadsheet into this property field.
  • Bind to a cell in the Squirrel spreadsheet that contains the link to the Excel Online spreadsheet.

Click the Check File button. This will check that Squirrel is able to connect to the Excel Online spreadsheet via the share link. This process will take a few seconds to complete.

• Sheet to use – This property is shown once a successful connection has been made between Squirrel and the Excel Online spreadsheet. Squirrel extracts the names of the worksheets from the Excel Online spreadsheet and lists them in the dropdown box. Select a worksheet to import data from.

The remainder of the properties for this Excel Online connector will then appear:

Once you have selected the sheet which contains the data you want to load a new dropdown will become visible which allows you to select how you want the data to be inserted into your project.

Select a range in an existing sheet

If you select the option “Select a range in an existing sheet” then a destination propoerty box will appear

Switch to DEBUG mode and open the spreadsheet to see the data populate the destination cells.

Create a new data sheet

If you select the option “Create new data sheet” then a new data sheet will be created in your spreadsheet and the following will be added to the property sheet:

Data sheets appear in the spreadsheet alongside standard sheets but are specifically designed to hold dynamic data read in from connectors.

You can rename data sheets using the right-click menu just as you can for standard sheets.

Cells and ranges in data sheets can be used as inputs into formulas on standard sheets and as the source for property sheet bindings, however it is not possible to type data or formulas directly into them. Data sheets are slightly greyed out to serve as a reminder of this.

Notice that you don’t have to select a fixed range for the data, the full dataset is always read into the sheet

Viewing the data from the connector at design time is now as simple as pushing the “Sync data sheet” button in the connector’s property sheet:

This will load the data sheet with the current data from the connector which makes it much easier to work with the data at design time (e.g. bind it to visual components).

FILTERING

This drawer of the properties panel allows for filtering to be performed on the data before it is placed into the destination cells.

• Filters – Bind this property to cells in the Squirrel spreadsheet that contain filter conditions for the data. It’s helpful to include a column/row title that corresponds to the Excel Online spreadsheet data titles. Add filter conditions in a cell beneath/adjacent to the header for clarity, for example:

The above example will import data entries that have a Product ID of 7 with a condition of all (*) for the remaining columns, into the yellow destination cells. See the result below:

• Enforce case sensitivity – Check this to ensure that data is also filtered according to case formatting.
• Exclude first row when filtering – Squirrel will assume that the data in the Excel Online spreadsheet has headings in the top row and will exclude these from the filtering conditions. The headings will therefore still be displayed followed by the filtered data.
• Selected columns – In a separate spreadsheet cell, enter a numerical value for the columns that should be imported. Bind the Selected columns property to that cell. In the example above four columns of data have been imported, but this may not always be necessary; use a comma separated list to indicate which columns are required e.g. 2,4 will display only columns 2 and 4 of the filtered data:

USAGE

The USAGE drawer of the properties panel allows for some control over how and when the data is refreshed. If the Excel Online connector is to an Excel Online spreadsheet that is constantly being updated, then it may be useful to have the Squirrel project update the imported data at regular intervals or in response to triggers in the Squirrel project.

• Refresh on load – This is the default setting and means that the Squirrel project imports the data from the Excel Online spreadsheet on project load. Unchecking it will mean that one of the other usage properties will need to be checked in order for the data to be imported.
• Refresh on interval – Check this file to set a recurring interval. The Squirrel project will import the data from the Excel Online spreadsheet after each interval. The interval value is measured in seconds, setting it to 30 will ensure that the Squirrel project will pull the data from the Excel Online spreadsheet every 30 seconds.
• Refresh on cell change – Bind to a cell that will have its content updated during runtime. This could be caused by the user interacting with a control, input from a data mover function, or the result of a calculation etc. The following example shows a text input label where the user can enter the number of the Product ID that they want to see results for. The value is inserted into cell H2, which is linked to the filter conditions, the change of value in this cell triggers the Squirrel project to update the data from the Excel Online spreadsheet: