Data Mover

The data mover is a powerful Squirrel function that enables data to move around the spreadsheet; it underpins many of the key interactive features of Squirrel projects. The following video explains how to use the Data Mover:

Increase the performance of spreadsheets

If a spreadsheet is taking a long time to import or its performance seems slow, it may contain redundant information. You can take the following steps to increase the performance of your spreadsheet.

Excel saves the parts of each worksheet that contain data and/or formatting. However, empty cells that don’t contain data, but do contain formatting, bloat the saved file. When imported into Squirrel a bloated file will take longer to load and will perform more slowly.

To remedy this, open the file in Excel and use the key combination CTRL+END on each sheet. The cursor will move to the cell that Excel considers to be the last cell in the sheet. Usually, this will be a cell with data in it that is furthest down and to the right in the sheet, however, due to redundant formatting, this can sometimes be a cell beyond the range of the data.

Select the rows and columns outside of the data range that contain formatting information. Then, from the menu bar select:

Home > Editing > Clear > Clear All

Repeat this for all affected sheets, save the file, and then import the workbook into Squirrel.

For more details on the clearing process see the Microsoft documentation here.

Using colour values

In Squirrel you can set a colour property by binding it to a colour value in the spreadsheet. This article will describe the different colour formats that can be used.

Colour name

A descriptive colour name can be used e.g. red, green, blue, hotpink, mediumseagreen.

The advantage of using colour names is that you can see at a glance what the colour will be, in a project where several colour values are listed in the spreadsheet, this can be very useful. There are 140 colour names that you can use that are supported by modern browsers, click on the following links for a full list:

• https://www.rapidtables.com/web/css/css-color.html
• https://www.w3schools.com/cssref/css_colors.asp

Hex value

A Hex value is written as follows:

Starting with a hashtag (#) and then followed by pairs of letters or numbers that represent the red, green and blue values. Each colour is defined by a value between 00 and FF, where a number is used to represent a value of 1 to 9, and the letters A to F represent a value of 10 to 15 (where A=10, B=11 and so on).

If you use the colour picker in the property panel, the colour that you choose will be represented by a Hex value in the property field:

Hex colour values are supported by all browsers.

RGB value

An RGB (Red, Green, Blue) colour value should be written as follows:

Where the three red, green and blue values, each ranging from 0 to 255, combine to produce the desired colour. A value of 0 means that none of the colour is being used, whereas a value of 255 means that all of that colour is being used.

RGB colour values are entered into the spreadsheet as text not as an expression and therefore should not have an = before the value. If you want to generate a colour value from data in the spreadsheet, use a formula similar to:

RGB colour values are supported by all major browsers.

HSL value

A HSL (Hue, Saturation, Lightness) colour value should be written as follows:

The hue value is the representation of a degree on a colour wheel, ranging from 0 to 360, where 0 is red, 120 is green and 240 is blue. The saturation value is a percentage, where 0% is full grey, but 100% is full colour. The lightness value is also a percentage, where 0% would cause it to be black, 100% would cause it to be white, but 50% would mean that the colour was unaffected by lightness either way.

Percentage values can also be written as decimals e.g. 0.5 (50%)

HSL colour values are entered into the spreadsheet as text not as an expression and therefore should not have an = before the value. If you want to generate a colour value from data in the spreadsheet, use a formula similar to:

HSL colour values are supported by most major browsers.

More details on HSL colours can be found at https://www.w3schools.com/css/css_colors_hsl.asp.

Alternative formats

RGBA and HSLA colour value formats can also be used but the alpha/opacity value will be ignored as opacity is controlled by a separate property in Squirrel.

There are three other valid options (HWB, CMYK and NCOL) but these are much less widely used, more details on these, and all the above methods, can be found here.

Formatting Text Labels and Scorecard Entries

It is possible to format the text in text label and scorecard components by surrounding the text with formatting tags.

The table below lists the formatting options and how to use them:

 

Tags can be combined together for composite effects:

Deleting functions and connections

A function or connection can be deleted from a Squirrel project by selecting it in the object browser and then pressing the delete key or the backspace key.

Changing the size of a project’s canvas

The default canvas size of a new Squirrel project is 1280 x 768 pixels. To access the canvas properties, click on an empty area of the canvas where there are no components, the canvas properties will be displayed in the properties panel.

Enter new height and width values for the project along with other properties such as background colour, background image, and export options.

Renaming a component, function or connection

Rename components, functions, and connections simply by double-clicking on them in the object browser and typing the new name.

or by double-clicking on their name at the top of the properties panel and typing the new text.

Chart title and Checkbox label text case formatting

The default case format for chart titles, axis labels and some other text fields (e.g. Checkbox labels) is “initial caps”, where the initial letter of each word is capitalized, for example, “Sales and marketing results” is displayed as “Sales And Marketing Results”.

However, there are situations where this is not required or even correct, for example when “USA Sales” would be displayed as “Usa Sales”.

Case formatting behaviour is controlled in the properties panel:

 

Click on the currently selected option “Aa” to toggle it to off:

Case formatting will be removed from the displayed text.

To reapply case formatting to a chart title or text field, click the initial caps option again, or choose either of the uppercase or lowercase options.

Set a default selection for a Dropdown

The default behaviour of a dropdown component is to not have any item initially selected. To change this use the Selected Item property in the INTERACTIVITY drawer of the properties panel.

Select either Label or Position from the property options, then in the empty property field directly below, bind to an appropriate value. If you have chosen Label and the dropdown box contains five options e.g. Red, Green, Blue, Black, White, binding to a cell that contains the word Red, will ensure that Red is the default selection. If you have chosen Position, binding to a cell that contains a 2 will ensure that Green is the default selection.

In the example below, Position has been selected, the cell bound to the property contains a 2, therefore the second item in the list is the default selection:

Seeing Data from a Connector at Design time

The ability to load data dynamically from connected data sources such as Google Sheets or Excel on-line is one of the most powerful features of Squirrel365, but because the dynamic data does not load into the design-time view of the project, it can be tricky to wire the data up to components on the canvas.  However, cutting and pasting sample data from the underlying source, as outlined in the video below, can minimise this frustration.