Create a project from scratch

As this is one of the first projects that you’ll build in Squirrel, this How To guide will not only tell you what to do, but will also explain why it needs to be done. Once you’ve completed the project you will have a better idea of how Squirrel works and how you can use it to build your own amazing projects.

There are three core-concepts for building projects within Squirrel: data binding, data insertion and dynamic visibility. We’ll use each of these while building this temperature converter app, so look out for them as we go along.

There are 3 sections to this step-by-step tutorial:

Section 1: Add the components to the canvas

Section 2: Add data and logic to the spreadsheet

Section 3: Connect the components to the data

Subsection 3.1: Data Binding

Subsection 3.2: Data Insertion

Subsection 3.3: Dynamic Visibility

 

Temperature Converter

What we are aiming for

It’s handy to start with an idea of what our app is going to look like. So here is the finished version:

The app works by allowing the user to select the initial temperature scale using the radio buttons: Celsius or Fahrenheit. The user then adds a numerical temperature value into the grey box, this value will be converted to the opposite temperature scale and displayed to the right of the equals sign. The graphic on the right will change according to the temperature.

You can refer back to this as you add your components to the project canvas, but positioning values will be provided for each item if you want to be more precise.

Step-by-Step Instructions

This tutorial will take approximately 40 minutes to complete (or 20 minutes if you use the starter file linked below).

Section 1: Add the components to the canvas

Either follow the instructions in this section to add all of the required components to the canvas, OR download a starter file from here and jump straight to Section 2: Add data and logic to the spreadsheet.

Open Squirrel and start with a brand new project.

In case you haven’t seen the ‘Find your way around’ tutorial, the following image identifies the elements of the Squirrel interface:

To add components to your project, choose from the components library located on the left hand side of the Squirrel interface. Click on a category and a list of components will be shown. Choose a component and add it to the canvas in one of two ways:

1. Click and drag it onto the canvas
2. Click to select it, then click on the canvas to place the object

Every object added to a Squirrel project is then listed in the object browser which is located between the components library and the canvas. Here, objects are given a default name, but this can be changed by double clicking on the object’s name and typing in a new one.

Select an object either by clicking it on the canvas, or clicking on it in the object browser. When selected, a blue bounding box will appear around the object. Click and drag the object around the canvas to reposition it, or click and drag the blue, square handles around the bounding box to resize the object.

Every object in Squirrel has properties and these are shown in the properties panel located on the right hand side of the canvas. Select any object to view or edit its properties in the properties panel.

Now that you know how to add objects, select them and view or edit their properties, let’s start building the app!

 

Canvas:

At the moment, the canvas is larger than needed. Click on the empty canvas (the white rectangle in the centre of the screen) to select it. At the top of the properties panel it will say Main Canvas. In the properties panel:

1. Resize the canvas by changing the width and height values to the following:
   • Width: 730
   • Height: 300
2. In the GENERAL drawer set the Background property to CCCCCC or choose a colour by clicking on the colour square and use the colour picker instead.

 

Border:

This is a rectangle we’ll use as a border around the app.

1. From the Shapes category in the components library, select and drag a Rectangle to the canvas.
2. In the object browser, double click on the name area of the rectangle (where it says Rectangle 1), rename to Border.

Change the following properties in the properties panel:

3. Position it:
   • X Position: 28
   • Y Position: 20
4. Resize it:
   • Width: 675
   • Height: 260
5. Style it inside the STYLING section of the properties panel:
   • Fill: Switch this property on by checking the checkbox
   • Border: Change colour to 333333 or click the colour square and use the picker instead.
   • Weight: Use the spinner buttons to increase this value to 5, or type 5 into the property field.
   • Corners: Move the slider to 5 or type 5 into the property field.

 

Title:

Add a project title.

1. From the Text category in the components library, select and drag a Text Label to the canvas.
2. In the object browser, double click on the name area of the Text Label and rename it to Title.

Change the following properties in the properties panel:

3. Position it:
   • X Position: 60
   • Y Position: 42
4. Resize it:
   • Width: 300
   • Height: 40
5. Change the text inside the LABEL TEXT drawer of the properties panel:
   • Text To Display: Delete This is a text label, and instead type in Temperature Converter
6. Style it inside the TEXT STYLING drawer of the properties panel:
   • Size: 25
   • Text formatting: Select the B button to make the text bold (the B button will change to blue)
   • Horizonal Text Alignment: Select the left option

 

Instruction Text:

Add another text label.

1. From the Text category in the components library, select and drag a Text Label to the canvas.
2. In the object browser, double click on the name area of the Text Label and rename it to Convert instruction.

Change the following properties in the properties panel:

3. Position it:
   • X Position: 60
   • Y Position: 110
4. Change the text inside the LABEL TEXT drawer of the properties panel:
   • Text To Display: Delete This is a text label, and instead type in Convert from:
5. Style it inside the TEXT STYLING drawer of the properties panel:
   • Horizonal Text Alignment: Select the left option

 

Radio Button:

This is a control component. For this app it will be used to allow the user to toggle the temperature conversion between Celsius and Fahrenheit.

1. From the controls category in the component library, select a Radio Button and drag it to the canvas.
2. In the object browser, double click on the name area of the Radio Button and rename it to Select scale.

Change the following properties in the properties panel:

3. Position it:
   • X Position: 60
   • Y Position: 142
4. In the GENERAL drawer of the properties panel, change the Orientation property to Horizontal.

Although there is obviously more that we need to do to make this radio button look and behave as it should, that will all come later when we bind the component to the data and logic in the spreadsheet. So we’ll just leave it as it is for now.

 

Text Input:

The text input component allows the user to enter a value into the app.

1. From the Text category in the component library, select a Text Input component and drag it to the canvas.
2. In the object browser, double click on the name area of the Text Input and rename it to Enter value.

Change the following properties in the properties panel:

3. Position it:
   • X Position: 60
   • Y Position: 175
4. Resize it:
   • Width: 190
   • Height: 40

 

Equals:

This is another text label that sits between the Enter value field and the Results field.

1. From the Text category in the components library, select and drag a Text Label to the canvas.
2. In the object browser, double click on the name area of the Text Label and rename it to Equals.

Change the following properties in the properties panel:

3. Position it:
   • X Position: 254
   • Y Position: 178
4. Resize it:
   • Width: 32
   • Height: 32
5. Change the text inside the LABEL TEXT drawer of the properties panel:
   • Text To Display: Delete This is a text label, and instead type in =
6. Style it inside the TEXT STYLING drawer of the properties panel:
   • Size: 25
   • Text formatting: Select the B button to make the text bold (the B button will change to blue)

 

Result title:

This is another text label that sits above the Results field.

1. From the Text category in the components library, select and drag a Text Label to the canvas.
2. In the object browser, double click on the name area of the Text Label and rename it to Result title.

Change the following properties in the properties panel:

3. Position it:
   • X Position: 281
   • Y Position: 140

You’ll see that this text label overlaps the third option of the radio button, but don’t worry that will all be sorted out shortly!

 

Result:

This text label will display the results of the temperature conversion.

1. From the Text category in the components library, select and drag a Text Label to the canvas.
2. In the object browser, double click on the name area of the Text Label and rename it to Result.

Change the following properties in the properties panel:

3. Position it:
   • X Position: 290
   • Y Position: 175
4. Resize it:
   • Width: 190
   • Height: 40
5. Change the text inside the LABEL TEXT drawer of the properties panel:
   • Text To Display: Delete This is a text label, and instead type in 0 for now
6. Style it inside the TEXT STYLING drawer of the properties panel:
   • Size: 19
   • Number Format: Number
7. Inside the BACKGROUND drawer of the properties panel:
   • Check the Border property to show a border around the text field.
   • Change the Border color to EEEEEE or click the color square and use the color picker instead
   • In the Border Edges dropdown box, select Bottom

 

Images:

The final items that we need to add to the canvas are the images that will show as the temperature changes.

1. From the Media category in the components library, select and drag an Image component to the canvas.
2. In the object browser, double click on the name area of the image component and rename it to Cold.

Change the following properties in the properties panel:

3. Position it:
   • X Position: 510
   • Y Position: 75
4. Resize it:
   • Width: 150
   • Height: 150
5. In the GENERAL drawer of the properties panel:
   • In the Image URL field, paste in this URL: https://squirrel365.io/wp-content/uploads/2020/11/cold.png
   • Click the Resize image to component checkbox
6. Repeat steps 1 to 5 except:
   1. Rename this image to Warm.
   2. Use this URL: https://squirrel365.io/wp-content/uploads/2020/11/medium.png
7. Repeat steps 1 to 5 except:
   1. Rename this image to Hot.
   2. Use this URL: https://squirrel365.io/wp-content/uploads/2020/11/hot.png

 

Phew! After all of that you should have all of the components that you need added to the canvas. At this stage it should look something like this:

Section 2: Add data and logic to the spreadsheet

In this section we’ll add some logic and data to the spreadsheet, it is important that this is entered into the cell addresses given in the instructions; these addresses will be referred to when making connections to components in Section 3: Connect the components to the data.

Radio Button Labels:

Add a heading to the B column. Style the heading so that it can be distinguished from the data that will be placed in rows situated beneath it:

1. B2: Radio Btn Labels
2. Press the Enter key on your keyboard.
3. With the cell still selected, use the formatting options at the top of the spreadsheet to change the font size to 14 and also make the text bold.
4. Increase the size of the column so that the contents of cell B2 are fully visible, by clicking and dragging the border line between the B and C column headers in the spreadsheet.

Next add the data beneath the heading:

4. B3: Celsius
5. B4: Fahrenheit

As the heading suggests, the data in B3 and B4 is to be used as labels for the radio buttons, but we’ll come to that when we get to Section 3! 

 

Data Insertion:

Columns D and E are reserved for all data insertion activities.

Add the following:

1. D2: Data Insertion
2. Style the heading for column D: font size 14 and bold
3. D3: Insert Celsius/Fahrenheit selection here ==>
4. D4: Insert temp here ==>
5. D6: Conversion result goes here ==>
6. Increase the size of the column so that the contents of these cells are fully visible, by clicking and dragging the border line between the D and E column headers at the top of the spreadsheet.

 

It is good practice to change the colour of a cell that will have data inserted into it. This tells you that this cell has been reserved for this purpose and reminds you not to add other data to the cell.

Change the colour of cells E3, E4 and E6 to yellow by selecting them and using the paint options at the top of the spreadsheet.

Then add the following values to the same cells:

7. E3: 1
8. E4: 0
9. E6: 0

 

Calculations and Logic:

Columns G and H are reserved for all Calculation and Logic purposes.

Add the following:

1. G2: Calcs and Logic
2. Style the heading for column G: font size 14 and bold
3. Increase the size of the column so that the contents of cell G2 are fully visible, by clicking and dragging the border line between the G and H column headers in the spreadsheet.
4. G3: CtoF
5. G4: FtoC
6. G6: Converting to
7. G8: Image to display

Now we’ll start to add the logic and calculations into column H…

The conventional formula to convert Celsius to Fahrenheit is:

(0°C × 9/5) + 32 = 32°F>

To make this work for us we need to replace the 0°C with the temperature value that will eventually be inserted into cell E4 (we’ll set this up in Section 3) and also structure the formula so that it will work in the spreadsheet.

8. The formula that needs to be added to H3 is:

The conventional formula to convert Fahrenheit to Celsius is:

(32°F − 32) × 5/9 = 0°C

To make this work for us we need to replace the 32°F with the temperature value that will eventually be inserted into cell E4 and structure the formula so that it will work in the spreadsheet.

9. The formula that needs to be added to H4 is:

Format both cells:

10. Select cells H3 and H4.
11. Select Number from the format dropdown at the top of the spreadsheet.

This will limit the number of decimal places shown in the results of these formulas.

 

The next piece of logic determines the contents of the Results title text field. If the user has selected Celsius as the input temperature, then the Results title text field should display Fahrenheit and vice versa if the user selects Fahrenheit.

In order to do this, we’ll use an IF statement to check the value of cell E3. Currently there is a value of 1 in cell E3, but in Section 3 when we bind the radio button component to this cell, it will store whichever is the currently selected radio button option.

12. The formula that needs to be added to cell H6 is:

The syntax of an IF statement can be described as: If this is true, do this, otherwise do this.

This particular formula is essentially saying: if the value of E3 equals 1, then the content of this cell should be ‘Fahrenheit:’ otherwise the content of this cell should be ‘Celsius:’.

 

The final piece of logic that we’ll add, will calculate which of the three images should be shown e.g. when higher than 25°C (77°F) the hot image will show, below 25°C (77°F) but warmer than 10°C (49°F) warm will be shown, and then finally if the temperature is below 10°C (49°F) then the cold image will show.

13. The formula that needs to be added to cell H8 is:

Now this formula probably looks quite complex, but we can break it down:

=IF(E3=1,IF(E4>=25,'C',IF(E4>=10,'B','A')),IF(E4>=77,'C',IF(E4>=50,'B','A')))

The green section is the first IF statement which says, if the value of cell E3 is equal to 1 (Celsius), then calculate the value according to the Celsius temperature conditions in the yellow section, otherwise, calculate the value according to the Fahrenheit conditions in the pink section.

Breaking it down further, inside each of the yellow and pink sections is another set of IF statements that check the temperature value that’s been entered and output a letter according to that value.

Section 3: Connect the components to the data

Now that we have our components added to the canvas and the calculations and logic added to the spreadsheet, the final part of this build is to bring it all together. This is where the magic happens!

Now for the first of our core concept super moves – Data Binding

Data binding links the properties of a component to data in the spreadsheet.

 

Connect the Select scale radio button

Bind to List items:

In the following four steps, you will bind the Select scale radio button component to the two cells containing the radio button labels that were added to the spreadsheet in Section 2.

1. Select the Select scale radio button component
2. In the GENERAL drawer of the properties panel move your mouse over the List Items property field. As you do so, a data binding icon appears above the property field. Click it!

The spreadsheet will open in ‘Binding Mode’ – you can’t edit any of the spreadsheet data while it’s in this mode but you can select a cell or range of cells to bind to the List Items property of the radio button.

3. Select cells B3 and B4. You can do this in three ways:

• Click in cell B3 and drag your mouse down to also include cell B4.
• Click in cell B3 then while holding down the shift button, also click in cell B4
• Type Sheet1!B3:B4 into the Select your range field

4. Click the Confirm button.

Your radio button component should now contain only two items: the first ‘Celsius’, which is bound to the data in cell B3, and the second ‘Fahrenheit’, which is bound to the data in cell B4. Pretty good huh?

 

Set a default value/selection:

You can set a default selection for a radio button, this means that when the app is run, one of the radio button options is pre-selected rather than none.

1. Ensure the Select scale radio button component is still selected.
2. In the INTERACTIVITY drawer of the properties panel, expand the SELECTED ITEM section.
3. Select Position from the Selected Item dropdown box.
4. Click the data binding icon above the property field below and select cell E3.
5. Click the Confirm button.

Because the value in cell E3 is currently 1, this will ensure that the first option of the radio button is selected when the app is run (if the value in the cell was 2, then the second option would be selected).

BUT, cell E3 has a second purpose. Not only can it be used to tell the radio button which option should be initially selected, this cell can also be updated with a new value each time a radio button is selected…

Now for the second of our core concept super moves – Data Insertion

Data insertion allows us to add data to the spreadsheet or to copy data from one area of the spreadsheet into another at runtime. Data insertion can be triggered in a number of ways e.g. the clicking of a button, by a timer, or even by data values. In this instance, data insertion will be triggered each time the user selects one of the radio button options.

A data insertion action is called a series. Some components have the option to trigger multiple series, so that two or more sets of data can be inserted or moved around the spreadsheet following the same trigger.

Set the data insertion properties:

1. With the Select scale radio button component still selected, expand the DATA INSERTION drawer in the properties panel.

The radio button is one of the components that allows you to have more than one series. And we will need to do that in a moment, but for now we’ll finish setting the properties for the first series.

2. Series 1 should already be selected.
3. The Type property should already be Position.
4. Bind the Destination property to cell E3.

A Type property set to Position means that the radio button will insert a numerical position when a radio button is selected (1 for option 1 – Celsius, 2 for option 2 – Fahrenheit).

So now, cell E3 will initially store the default value for the radio button, but once the user selects a radio button option, it will then subsequently store the selected value thanks to this process of data insertion.

 

Now we’ll add that second data insertion series:

4. Click the + button above the Series property field.
5. A new Series (Series 2) will be added to the Series property.
6. Set the Series 2 Type to Value.
7. Bind the Source property to cells H3 and H4.
8. Bind the Destination property to cell E6.

So, how does this work? Setting the Type property to Value means that the radio button will return a numerical value when a radio button is selected (1 for option 1 – Celsius, 2 for option 2 – Fahrenheit). As the source property is pointing to a range of two cells, clicking option 1 will copy the value from the first cell in the source range (H3) and will paste it into the destination cell, but clicking option 2 will copy the value from the second cell in the source range (H4) and will instead paste that into the destination cell (E6).

Now, because of these two data insertion series, two things will happen when either radio button option is clicked:

• Firstly, 1 or 2 will be inserted into cell E3
• Secondly, the contents of cell H3 or H4 will be copied and inserted into cell E6

 

Connect the Enter value text input field

We want whatever value the user enters into the Enter value text input field to be added into the spreadsheet so that it can be used and converted to the opposite temperature scale. Again, we can add a data insertion function to do this.

1. Select the Enter value text input field
2. Expand the DATA INSERTION drawer in the properties panel.

The data insertion properties for the text input component only allow for one data insertion series, so it looks slightly different to a component that allows multiple series (such as the radio button above).

3. Click the data binding icon above the Value property field.
4. Select cell E4 or type Sheet1!E4 into the Select your range field
5. Click Confirm

Next we are going to ensure that only a numerical value is entered into the field by specifying that only numerical characters are allowed. We’re also going to include a – (minus) character:

6. Expand the INPUT OPTIONS section within the DATA INSERTION drawer. In the Characters Allowed property field type in -1234567890

Now when a user enters a value into the field, that value will be stored in cell E4.

 

Set the result title:

This will change the contents of the Results title text field so that it changes according to the input type. If the user is inputting a celsius temperature, we want the result title to say Fahrenheit: and vice versa.

1. Select the Result title text field
2. Expand the LABEL TEXT drawer in the properties panel
3. Click the data binding icon above the Text to Display property.
4. Select cell H6.
5. Click confirm.

 

Display the result:

When one of the Select scale radio buttons is clicked, the contents of H3 or H4 (depending on the option selected) is copied into cell E6. It’s this value that we want to display in the Result text field.

1. Select the Result text field.
2. Expand the LABEL TEXT drawer of the properties panel.
3. Click the data binding icon above the Text To Display property.
4. Select cell E6.
5. Click confirm.

 

Show the appropriate image:

The formula that we placed into cell H8 will return a value of A, B or C, depending on the temperature entered into the converter. We can use this returned value to show the appropriate graphic.

Now for the third of our core concept super moves – Dynamic Visibility:

Each component that we add to the canvas has dynamic visibility properties that work in exactly the same way.

1. Select the Cold image.
2. Expand the DYNAMIC VISIBILITY drawer of the properties panel.
3. Click the data binding icon above the Status property field.
4. Select cell H8.
5. In the Value property enter the letter A

If the value of cell H8 (bound to the Status property field) is equal to the Value property, the image will be visible and conversely will be invisible if they are not equal. In this case, if the value entered is less than 10°C (50°F) the cold image will be visible, otherwise it will be invisible.

If you leave these two properties empty, the component will always be visible because both properties are equally empty.

 

Follow the same process for the other two images but notice the different letters entered into the Value properties.

6. Select the Warm image.
7. Expand the DYNAMIC VISIBILITY drawer of the properties panel.
8. Click the data binding icon above the Status property field.
9. Select cell H8.
10. In the Value property enter the letter B

In this case, if the value entered is equal to or greater than 10°C (50°F) and less than 25°C (77°F) the warm image will be visible, otherwise it will be invisible.

 

 

11. Select the Hot image.
12. Expand the DYNAMIC VISIBILITY drawer of the properties panel.
13. Click the data binding icon above the Status property field.
14. Select cell H8.
15. In the Value property enter the letter C

In this case, if the value entered is equal to or greater than 25°C (77°F) the hot image will be visible, otherwise it will be invisible.

 

 

 

And that’s it! Switch to DEBUG mode or PREVIEW mode to view the temperature conversion app in action!

In this project you’ve used each of the core concepts for building projects within Squirrel – data binding, data insertion and dynamic visibility. Hopefully this has given you some ideas for other projects that you can build within Squirrel. Do share them with us, we’d love to see what you create!

Create a project from a spreadsheet

Sometimes when faced with a spreadsheet full of data it can be difficult to know where to start. The aim of this project is to give you some ideas about how you can pull out sections of data, display it in an easily understood way and even apply variables to that data to demonstrate the effect of differing conditions.

There are 3 sections to this step-by-step tutorial:

Section 1: Import the spreadsheet and add the components

Section 2: Working with the spreadsheet

Section 3: Connect the components to the data

 

Revenue Growth Model App

What we are aiming for

It’s handy to start with an idea of what our app is going to look like. So here is the finished version:

See how the data is initially displayed in the chart, but then if you move the sliders, watch how those factors affect the data in the chart and also the Annual Retention Rate percentage KPI at the top.

You can refer back to this as you add your components to the project canvas, but positioning values will be provided for each item if you want to be more precise.

 

 

Step-by-Step Instructions

This tutorial will take approximately 40 minutes to complete (or 25 minutes if you use the starter file linked below).

Section 1: Import the spreadsheet and add the components

Either follow the instructions in this section to import the spreadsheet and add all of the required components to the canvas, OR download a starter file* from here and jump straight to Section 2: Working with the spreadsheet.

* The starter file contains all of the components (configured ready for use) and the RevenueData.xls spreadsheet already imported.

 

Import the spreadsheet

To make sure that we’re all singing from the same spreadsheet, download a copy of the RevenueData.xls file that will be used in this project.

1. Open up a brand new Squirrel file.
2. In the File menu, select Spreadsheet > Import
3. Locate the RevenueData.xls file (if you haven’t already downloaded it, you can download it from here)

See that the Squirrel spreadsheet has now been populated with the data from the RevenueData.xls file.

 

Add the components

In case you haven’t seen the ‘Find your way around’ tutorial, the following image identifies the different sections of the Squirrel interface:

To add components to your project, choose from the component library located on the left hand side of the Squirrel interface. Click on a category and a list of components will be shown. Choose a component and add it to the canvas in one of two ways:

1. Click and drag it onto the canvas
2. Click to select it, then click on the canvas to place the object

Every object added to a Squirrel project is then listed in the object browser which is located between the components library and the canvas. Here, objects are given a default name, but this can be changed by double clicking on the object’s name and typing in a new one.

Select an object either by clicking it on the canvas, or clicking on it in the object browser. When selected, a blue bounding box will appear around the object. Click and drag the object around the canvas to reposition it, or click and drag the blue, square handles around the bounding box to resize the object.

Every object in Squirrel has properties and these are shown in the properties panel located on the right hand side of the canvas. Select any object to view or edit its properties in the properties panel.

Now that you know how to add objects, select them and view or edit their properties, let’s start building the app!

 

Canvas:

The default canvas size for a new Squirrel project is larger than we need. Click on the empty canvas (the white rectangle in the centre of the screen) to select it. At the top of the properties panel it will say Main Canvas.

1. In the properties panel, resize the canvas by changing the width and height values to the following:
   • Width: 550
   • Height: 600

 

Main title:

Add a project title.

1. From the Text category in the components library, select and drag a Text Label to the canvas.
2. In the object browser, double click on the name area of the Text Label and rename it to Main Title.

Change the following properties in the properties panel:

3. Position it:
   • X Position: 50
   • Y Position: 25
4. Resize it:
   • Width: 450
   • Height: 50
5. Change the text inside the LABEL TEXT drawer of the properties panel:
   • Text To Display: Delete This is a text label, and instead type in Revenue Growth Model
6. Style it inside the TEXT STYLING drawer of the properties panel:
   • Size: 30
   • Text formatting: Select the B button to make the text bold (the B button will change to blue)
7. Add a bottom border inside the BACKGROUND drawer of the properties panel:

1. • Border: Switch this property on by checking the checkbox
   • Border Color: Change color to CCCCCC
   • Weight: Use the spinner buttons to increase this value to 3, or type 3 into the property field
   • Border Edges: Select Bottom from the dropdown options

 

KPI tile:

Working our way down the project, the next item to add is a KPI tile. This is actually made up of three components: a background rectangle, a text label title and then a second text label to display the KPI.

KPI background:

1. From the Shapes category in the components library, select and drag a Rectangle to the canvas.
2. In the object browser, double click on the name area of the rectangle (where it says Rectangle 1), rename to KPI background.

Change the following properties in the properties panel:

3. Position it:
   • X Position: 175
   • Y Position: 110
4. Resize it:
   • Width: 200
   • Height: 80
5. Style it inside the STYLING drawer of the properties panel:
   • Fill: Switch this property on by checking the checkbox
   • Fill: Change to 333333 or click the colour square and use the picker instead.
   • Corners: Move the slider to 4 or type 4 into the property field.

 

KPI label:

1. From the Text category in the components library, select and drag a Text Label to the canvas.
2. In the object browser, double click on the name area of the Text Label and rename it to KPI label.

Change the following properties in the properties panel:

1. Position it:
   • X Position: 175
   • Y Position: 110
2. Resize it:
   • Width: 200
   • Height: 30
3. Style it inside the TEXT STYLING drawer of the properties panel:
   • Size: 12
   • Text Color: Change to FFFFFF or click the colour square and use the picker instead.

 

KPI output:

1. From the Text category in the components library, select and drag a Text Label to the canvas.
2. In the object browser, double click on the name area of the Text Label and rename it to KPI output.

Change the following properties in the properties panel:

1. Position it:
   • X Position: 175
   • Y Position: 140
2. Resize it:
   • Width: 200
   • Height: 40
3. Change the text inside the LABEL TEXT drawer of the properties panel:
   • Text To Display: Delete This is a text label, and instead type in 0%
4. Style it inside the TEXT STYLING drawer of the properties panel:
   • Size: 30
   • Text formatting: Bold (Select the B button)
   • Text Color: Change color to CCCCCC or click the color square and use the picker instead.
   • Number Format: Percentage
   • Decimal Places: 1

 

Revenue chart:

This chart will be the main focal point of this project. It will eventually display the recurring revenue data contained in the spreadsheet.

1. From the Charts category in the components library, select and drag a column chart to the canvas.
2. In the object browser, double click on the name area of the column chart and rename it to Revenue.

For now, we’ll just add it to the project and amend it’s properties to make it suitable for use in the project:

1. Position it:
   • X Position: 50
   • Y Position: 200
2. Resize it:
   • Width: 450
   • Height: 250
3. Change the chart title inside the TITLE section of the GENERAL drawer of the properties panel:
   • Size: 16
   • Horizonal Text Alignment: Select the centered option
   • Check the Auto size bars checkbox to switch that on
   • Turn off the subtitle by unchecking the SUBTITLE checkbox
4. Set the X-Axis properties in the X-AXIS drawer of the properties panel:
   • Size: 8
   • Label Orientation: 45°
   • Remove the x-Axis title by unchecking the checkbox on the TITLE section.
5. Set the Y-Axis properties in the Y-AXIS drawer of the properties panel:
   • Check the Manual Scale property to on
   • In the LABELS section:
   • Number Format: Currency
   • Currency Symbol: USD ($)
   • Remove the y-Axis title by unchecking the checkbox on the TITLE section.
6. In the GRIDLINES drawer of the properties panel:
   • Uncheck the Axis Lines property

There are some other properties that we’ll need to adjust on this chart, but we’ll only be able to do this once we’ve connected the chart to the data – which we will do in Section 3 of this tutorial.

 

 

 

 

Assumptions:

1. From the Text category in the components library, select and drag a Text Label to the canvas.
2. In the object browser, double click on the name area of the Text Label and rename it to Assumptions label.

Change the following properties in the properties panel:

1. Position it:
   • X Position: 150
   • Y Position: 450
2. Resize it:
   • Width: 250
   • Height: 30
3. Change the text inside the LABEL TEXT drawer of the properties panel:
   • Text To Display: Delete This is a text label, and instead type in Assumptions
4. Style it inside the TEXT STYLING drawer of the properties panel:
   • Size: 16
   • Text formatting: Select the B button to make the text bold
5. Add a bottom border inside the BACKGROUND drawer of the properties panel:
   • Border: Switch this property on by checking the checkbox
   • Border Color: Change color to CCCCCC
   • Weight: Use the spinner buttons to increase this value to 1, or type 1 into the property field
   • Border Edges: Select Bottom from the dropdown options

 

 

Factor 1 label:

1. From the Text category in the components library, select and drag a Text Label to the canvas.
2. In the object browser, double click on the name area of the Text Label and rename it to Factor 1 label.

Change the following properties in the properties panel:

1. Position it:
   • X Position: 150
   • Y Position: 485
2. Resize it:
   • Width: 250
3. Style it inside the TEXT STYLING drawer of the properties panel:
   • Horizonal Text Alignment: Select the left option

 

 

Factor 1 slider:

1. From the Controls category in the components library, select and drag a slider to the canvas.
2. In the object browser, double click on the name area of the slider and rename it to Factor 1.

Change the following properties in the properties panel:

1. Position it:
   • X Position: 150
   • Y Position: 500
2. Resize it:
   • Width: 235
   • Height: 55
3. Inside the GENERAL drawer of the properties panel:
   • Number Format: Percentage
   • Max: 20
4. Inside the STEPS section:
   • Increment: 1
5. Style it inside the STYLING drawer of the properties panel:
   • Track Color: FFFFFF
   • MIN MAX VALUES: uncheck

 

 

 

 

 

 

 

 

Factor 2 Label and Factor 2 Slider:

1. Select both Factor 1 Label and Factor 1 slider.
2. Copy and paste them.
3. Reposition the copied components beneath the first slider.
4. Rename to Factor 2 label and Factor 2 respectively.

 

Ok, so after all of that you should have all of the components that you need added to the canvas. At this stage it should look something like this:

Section 2: Working with the spreadsheet

 

Get to know the data

Whether you have chosen to use the starter file or have worked your way through section 1 and have imported the RevenueData.xls file yourself, this is a good opportunity to look at the data to see what we’ve got to work with.

In the Main model worksheet is a good amount of data relating to revenue over a period of time. Column A tells us the type of data that has been captured and columns B to MM lists that data on a monthly basis over years 2020 and 2021. The remaining content of the worksheet collates that data and/or produces more data from it.

The Factors worksheet contains a number of variables that affect the data in the Main model worksheet. In this project, we’ll use a couple of these factors to manipulate the data at runtime. All will become clear as we go along.

 

What we’ll be adding

In this section of the build, we’ll add some additional logic and data to the spreadsheet. It is important that this is entered into the cell addresses given in the instructions as these same cell addresses will be used for making connections to components in Section 3.

The additional data will be used to build and style the app. We’ll add colours for the KPI tile text that will indicate a good (green), average (orange), or bad result (red) and a calculation that will work out the appropriate colour to apply to the KPI tile text. We’ll also add a colour that can be used as a main colour accent throughout the app.

 

Add content to Sheet 3 of the Spreadsheet:

Firstly lets rename the Sheet 3 tab of the spreadsheet to something more appropriate:

1. Right click on the tab of the spreadsheet called Sheet 3.
2. Select Rename from the menu.
3. Type App into the name field instead.
4. Press Enter.

 

Now we’ll start adding some more content and data that we can use within the app. Add the following to the App sheet:

1. A1: KPI text colour
2. B1: Threshold 1
3. B2: 0.9
4. C1: Threshold 2
5. C2: 0.95
6. A4: Colours
7. A5: Good
8. B5: mediumseagreen
9. A6: Average
10. B6: orange
11. A7: Bad
12. B7: tomato
13. A9: Main colour
14. B9: 53C8CA
15. Finally paste the following formula into cell A2:

This formula checks to see what the KPI Annual Retention Rate value is in cell B4 of the Main Model tab of the spreadsheet. Then using a nested IF statement it will return one of the colours from B5, B6 or B7 accordingly. In section 3 we will bind this value to the text color property of the KPI output text field.

The spreadsheet should now look like this:

Section 3: Connect the components to the data

Now that we have our components added to the canvas and the calculations and logic added to the spreadsheet, the final part of this build is to bring it all together. So let’s start bringing this project to life!

 

Connect the KPI tile components

KPI label:

1. Select the KPI label component.
2. In the LABEL TEXT drawer of the properties panel, move your mouse over the Text To Display property field. As you do so, a data binding icon appears above the property field. Click it!
3. The spreadsheet will open in binding mode – you can’t edit any of the spreadsheet data while it’s in this mode but you can select a cell to bind to the Text To Display property of the KPI label component.
4. Click the Main model tab of the spreadsheet at the bottom.
5. Select cell A4 OR type ‘Main model’!A4 into the Select your range field at the top of the spreadsheet.
6. Click the Confirm button.

The KPI label component’s text should now say Annual Retention Rate.

 

KPI output:

1. Select the KPI output component.
2. Click the data binding icon above LABEL TEXT drawer of the properties panel. (As you did in step 2 above).
3. Ensure that the Main model tab of the spreadsheet is still selected.
4. Select cell B4 OR type ‘Main model’!B4 into the Select your range field at the top of the spreadsheet.
5. Click the Confirm button.

The KPI output component’s text should now display the same value as cell B4 of the Main model sheet. Next we’ll change the colour of the KPI text according to the value that it is displaying:

1. In the TEXT STYLING drawer of the properties panel, click the data binding icon above the Text Color property.
2. Click the App tab of the spreadsheet at the bottom.
3. Select cell A2 OR type App!A2 into the Select your range field at the top of the spreadsheet.
4. Click the Confirm button.

The KPI output’s text colour will now update according to the value that it is currently displaying.

 

Connect the Revenue Chart

1. Select the Revenue chart.
2. In the GENERAL drawer of the properties panel, click the data binding icon above the Chart Title property.
3. Select the Main model tab of the spreadsheet.
4. Select cell A20 OR type ‘Main model’!A20 into the Select your range field at the top of the spreadsheet.
5. Click the Confirm button.

See that the title of the chart has updated to say ‘Recurring Revenue’.

6. In the DATA drawer of the properties panel, select the radio button Bind individual series.
7. Delete Dummy Data from the Name property field and rename it to Series 1.
8. Click the data binding icon above the Values property field.
9. Select cells B20 to Y20 in the Main model sheet OR type ‘Main model’!B20:Y20 into the Select your range field at the top of the spreadsheet.
10. Click the Confirm button.

Well the data has been added to the chart, but we need to change some of the chart properties to make it legible to the user.

First let’s add some labels to the chart:

11. Still in the DATA drawer of the properties panel, click the data binding icon above the Category Labels property field.
12. Select cells B6 to Y6 in the Main model sheet OR type ‘Main model’!B6:Y6 into the Select your range field at the top of the spreadsheet.
13. Click the Confirm button.

Next we’ll change the color of the columns in the chart to the Main color that we added into the App sheet in the spreadsheet:

14. Next expand the STYLING – SERIES 1 section of the properties panel.
15. Click the data binding icon above the Line Color property.
16. Click the App tab of the spreadsheet.
17. Select cell B9 OR type App!B9 into the Select your range field at the top of the spreadsheet.
18. Click the Confirm button.

Currently the data being shown within the chart isn’t being displayed correctly. We need to change the Y-Axis scale to accommodate the data being shown in the chart, so we’ll bind it to the maximum revenue data in the spreadsheet:

19. Expand the Y-Axis drawer of the properties panel.
20. Click the data binding icon above the Max property field.
21. Select the Main model tab in the spreadsheet
22. Select cell Y21 OR type ‘Main model’!Y21 into the Select your range field at the top of the spreadsheet.
23. Click the confirm button.

Changing the Max value should have given a little more meaning to the data being displayed in the chart and it should now look something like this:

 

Connect the Assumptions sliders

The final components that we’ll connect to the data are the sliders at the bottom. The purpose of these is to manipulate data in the factors tab of the spreadsheet. The data in the factors tab is already linked to the data in the Main model tab because that is how this spreadsheet was built. By manipulating the factors data, we’ll be able to see the knock-on effect that has on the data in the chart.

Factor 1 label:

1. Select Factor 1 label.
2. In the LABEL TEXT drawer of the properties panel, click the data binding icon above the Text To Display property.
3. Click the Factors tab of the spreadsheet.
4. Select cell A8 (Monthly churn) OR type Factors!A8 into the Select your range field.
5. Click Confirm.

Factor 1 slider:

1. Select the Factor 1 slider component.
2. In the GENERAL drawer of the properties panel, click the data binding icon above the Value property.
3. Ensure that the Factors tab of the spreadsheet is still selected.
4. Select cell B8 OR type Factors!B8 into the Select your range field.
5. Click Confirm.
6. In the STYLING drawer of the properties panel, click the data binding icon above the Selected Range Color property.
7. Click the App tab of the spreadsheet.
8. Select cell B9 OR type App!B9 into the Select your range field at the top of the spreadsheet.
9. Click the Confirm button.

Factor 2 label:

1. Select Factor 2 label.
2. In the LABEL TEXT drawer of the properties panel, click the data binding icon above the Text To Display property.
3. Click the Factors tab of the spreadsheet.
4. Select cell A12 (Referral conversion rate) OR type Factors!A12 into the Select your range field.
5. Click Confirm.

Factor 2 slider:

1. Select the Factor 2 slider component.
2. In the GENERAL drawer of the properties panel, click the data binding icon above the Value property.
3. Ensure that the Factors tab of the spreadsheet is still visible.
4. Select cell B12 OR type Factors!B12 into the Select your range field.
5. Click Confirm.
6. In the STYLING drawer of the properties panel, click the data binding icon above the Selected Range Color property.
7. Click the App tab of the spreadsheet.
8. Select cell B9 OR type App!B9 into the Select your range field at the top of the spreadsheet.
9. Click the Confirm button.

 

And that’s it. With all of the components linked to the relevant data, switch to DEBUG or PREVIEW mode and see how you’ve changed a working spreadsheet into an interactive app!

 

Whack-A-Squirrel Game

In this ‘How-To’ you can learn how to make your own Whack-A-Mole style game. You’ll make use of the timer and the data mover functions, use data binding, dynamic visibility and data insertion, add a random number generating formula, set up a scoring system and much more. Check out the finished game below then scroll down to the step-by-step instructions and have a go!

 

Step-by-Step Instructions

This tutorial will take approximately 45 minutes to complete.

Section 1: Add the components to the canvas

Either follow the instructions in this section to add all of the required components to the canvas, OR download a starter file from here and jump straight to section 2.

Use the following as an approximate guide for the component positions. But don’t worry too much about the Squirrel position, we’ll be setting that later:

 

Add Squirrel image:

1. Add an image component to the canvas
2. In the GENERAL drawer of the property panel, Source image from property either point to the URL of a square squirrel image or import an image file. You can use our little blue buddy if you like?:
   • Right-click on the squirrel image below
   • Select copy image address
   • Set Source image from property to URL
   • Paste the image address into the Image URL property field

3. Check the Resize image to component property
4. In the property panel resize the width property to 64 and the height property also to 64
5. Rename to SquirrelImg by double-clicking on the current name of the image component in the object browser

 

Add a target button:

1. Add a pushbutton component to the canvas
2. Rename to SquirrelBtn

 

Format SquirrelImg and SquirrelBtn:

1. On the canvas, select SquirrelImg and press the shift button while also clicking the SquirrelBtn component, this will select both components
2. Use the Arrange menu, select Size and then Make same size. As SquirrelImg is the primary object (selected first) the SquirrelBtn will be resized to match SquirrelImg
3. Select just the SquirrelBtn in the Object Browser
4. In the BUTTON STYLING drawer of the properties panel, DEFAULT TEXT section delete the contents of the Button text property field
5. In the DEFAULT STYLE section, uncheck the Fill property
6. In the Effects dropdown, select None
7. Uncheck the HOVER STATE properties
8. Uncheck the PRESSED STATE properties

 

Add a start button:

1. Add another pushbutton component
2. Change the button name to startBtn
3. In the DEFAULT TEXT section change the Button Text property to WHACK-A-Sqrl!
4. In the DEFAULT STYLE section, change the Fill property to 0099FF

 

Add level text label:

1. Add a text label component
2. Rename to levelTxt
3. Select levelTxt
4. In the LABEL TEXT drawer of the properties panel, change the Text to display property to Level:
5. In the TEXT STYLING drawer, set to Left justified

 

Add a level selector:

1. Add a value input component
2. Change its name to levelSel

 

Add score text label:

1. Add a text label component
2. Rename to scoreTxt
3. Select scoreText
4. In the LABEL TEXT drawer of the properties panel, change the Text to display property to Score:
5. In the TEXT STYLING drawer, set to Left justified

 

Add a finish game text label:

1. Add a text label component
2. Change its name to finishTxt
3. Select finishTxt
4. Amend the X position to 0 and the Y position to 0
5. Amend the width to 232 and height to 232
6. In the LABEL TEXT section, change the Text to display property to Time’s Up!
7. In the TEXT STYLING section, style the text caption as follows:

• • Font: Arial Black
  • Size: 36
  • Text color: 606060
  • Paragraph alignment: centered horizontally and vertically

 

Add a rectangle to block mouse events:

1. Add a rectangle
2. Rename it to controlCover
3. Resize and reposition it to cover both the startBtn and levelSel
4. Uncheck the Border property
5. Check the Block Mouse Events property

 

Now all of the necessary components should be on the canvas. Your object browser and canvas should look similar to this:

Section 2: Add functions

Add a timer function:

1. In the Object Browser, open the FUNCTIONS drawer
2. Select Timer from the dropdown and click the + button
3. Double-click to rename to SquirrelTmr

 

Add a data mover function:

1. Also, in the Object Browser FUNCTIONS drawer
2. Select Data Mover from the dropdown and click the + button
3. Double-click to rename to SquirrelDataMov

Section 3: Add data and logic to the Spreadsheet

Randomise the SquirrelImg position:

There are 9 possible positions for the squirrel to move to. Think of these as a grid, with 3 x and 3 y positions, in the following grid they are 10, 84 and 158. We can use this information to set the x and y positions of the squirrel image (and the squirrelBtn).

1. Add the following data to the spreadsheet, ensuring that your data occupies the same cells shown:

 

2. In cell C1, type in ‘Random 1 to 9:‘
3. Copy the following formula into cell C2:

4. Type ‘xPos:’ into cell A6 and ‘yPos:’ into cell A8
5. Copy the following switch statement into cell A7:

6. Copy the following switch statement into cell A9:

 

Set the SquirrelImg and the SquirrelBtn position properties:

With the x and y positions generated, these can now be bound to the X and Y position properties of the SquirrelImg and SquirrelBtn.

1. Select SquirrelImg in the Object browser
2. Click the data binding icon above the X position property field
3. Select cell A7 and then the Confirm button
4. Click the data binding icon above the Y position property field
5. Select cell A9 and then the Confirm button
6. Repeat steps 1 to 5 for SquirrelBtn

Create a scoring mechanism:

To create the scoring mechanism, count the number of times the user successfully clicks the SquirrelBtn (positioned directly above the SquirrelImg).

1. In cell C3, type in ‘Score:’
2. Copy the following formula into cell D4:

3. Select SquirrelBtn and open the DATA INSERTION drawer in the properties panel
4. Click the data binding icon above the Source data property and select cell D4
5. Click the data binding icon above the Destination property and select cell C4

 

Display the score:

1. Select cell C3 and change it to the following formula:

2. Select scoreTxt
3. In the LABEL TEXT drawer, click on the data binding icon above the Text to display property
4. Select cell C3 and click the confirm button

 

Setting the game level:

The timer level is linked to the speed of the timer. The lower the level, the slower the timer, the higher the level, the faster the timer. The default level will be level 5, the minimum is 1 and the maximum is 10.

1. Type ‘Current level:’ into cell C5
2. In cell C6 add the number 5
3. Type ‘Level min:’ into cell C7
4. In cell C8 add the number 1
5. Type ‘Level max:’ into cell C9
6. In cell C10 add the number 10

7. Select the levelSel component
8. Open the DATA INSERTION drawer in the properties panel
9. Click the data binding icon above the Value property
10. Select cell C6 and click the Confirm button

Not only does the levelSel take its initial value from this cell, it will also write back a new value to this cell if the user interacts with the spinner buttons or types in a new numerical value.

11. Check the Enable limits property
12. Click the data binding icon above the Min property
13. Select cell C8 and click the confirm button
14. Click the data binding icon above the Max property
15. Select cell C10 and click the confirm button

 

Set the Timer properties:

1. Type ‘Timer Duration:’ into cell E1
2. Copy the following formula to cell E2:

3. Select SquirrelTmr in the FUNCTIONS drawer of the Object browser
4. Click the data binding icon above the Delay in milliseconds property
5. Select cell E2 and click the confirm button
6. Change the Trigger when property to Cell equals radio button
7. Type ‘Timer trigger:’ into cell E3
8. Type ‘startTimer’ into cell F4
9. Type ‘stopTimer’ into cell F5

10. Reselect SquirrelTmr
11. Click the data binding icon above the Trigger cell property field
12. Select cell E4 and click the confirm button
13. Click the data binding icon above the Equals property
14. Select cell F4 and click the confirm button

Set the end game conditions:

A counter to count how many times the timer has executed.

1. Type ‘Timer counter:’ into cell E6
2. Select SquirrelTmr
3. Select Loop from the Insertion type property drop down
4. Click the data binding icon above the Loop counter property field
5. Select cell E7 and click the confirm button

6. Type ‘Timer stop at:’ into cell E8
7. Type 20 into cell E9
8. Reselect SquirrelTmr
9. Click the data binding icon above the Stop after n loops property field
10. Select cell E9 and click the confirm button

Show Time’s Up notification when the game is finished:

1. Type ‘Time’s up:’ into cell E10
2. Copy the following formula into cell E11:

The spreadsheet should now look like this:

3. Select finishTxt
4. Open the DYNAMIC VISIBILITY drawer in the properties panel
5. Click the data binding icon above the Status property field
6. Select cell E11 and click the confirm button
7. Type TRUE into the Value property field

Make controls unavailable during game:

The data mover function will be used to change a set a timer status. This will determine when the controlCover rectangle is visible. When visible it will block mouse events to both the startBtn and the levelSel.

1. Select SquirrelDataMov in the FUNCTIONS drawer of the Object browser
2. In the Series property field, select Series 1
3. In the Name property field type in timerStopped
4. In the Type property select Block from the dropdown
5. Bind the Source property to cell F5
6. Bind the Destination property to E4
7. In the Move data block when… section, bind Status property to E7
8. Bind Value property to E9
9. Open the DYNAMIC VISIBILITY drawer in the properties panel
10. Click the data binding icon above the Status property field
11. Select cell E4 and click the confirm button
12. Click the data binding icon above the Value property field
13. Select cell F4 and click the confirm button

 

Add the start game functionality:

1. Select startBtn
2. Open the DATA INSERTION drawer in the properties panel
3. Click the data binding icon above the Source data property field
4. Select cell F4 and click confirm
5. Click the data binding icon above the Destination property field
6. Select cell E4 and click confirm

 

Although the timer is working, the random number generator is not being triggered each time the timer executes. For this to happen, change the formula in C2 to the following:

Reset the score when game is restarted:

1. Select SquirrelDataMov
2. Click the + button above the Series property in the properties panel
3. Change the Name property to resetScore
4. Change the Type property to Block
5. Type ‘Reset score:’ into cell G3
6. Copy the following formula into cell G4:

7. Make sure the SquirrelDataMov resetScore Refresh on data change property is checked
8. Bind the Source property to cell G4
9. Bind the Destination property to cell C4
10. In the Move data block when… section, bind Status property to G4
11. Add 0 to the Value property

 

Change to Debug or Preview mode to check that it all works. Then when you are happy with it, publish it for use outside of Squirrel!

 

_____________________________________

In this build we’ve used Squirrel’s three core concepts of data binding, data insertion and dynamic visibility. If you’d like more information on these or any of the components or functions that you’ve used, check out the other content in the Learn section of the website and also take a look at the tutorial videos on the Squirrel YouTube channel.