Ninox Connections

The Ninox connections allow data to be read from and written to a Ninox cloud database.

There are three different Ninox connections:

• Ninox Create – allows a new row to be inserted into a Ninox database table from data in the Squirrel spreadsheet
• Ninox Read – allows data to be read from a Ninox table to a range in the Squirrel spreadsheet
• Ninox Update – allows one or more rows in a Ninox database table to have one or more of its column values updated

To add a connector to your project open the CONNECTIONS drawer in the object browser, select the appropriate entry from the dropdown box and click the plus button:

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

Common Ninox Properties – Credentials

Security credentials are required in order to use the Ninox connectors. Obtaining these will depend on whether you are accessing a database in the Ninox public cloud or you have a private Ninox instance.

Ninox Cloud Credentials

To access a Ninox cloud database you will need to obtain an API key for your account. Details on how to do this can be found in the Ninox documentation at:

Ninox Database – Manual / API / Get started

Private Ninox Instance Credentials

To access a private Ninox instance please contact us at support@squirrel365.io for further information

Connecting to the Ninox cloud database

The top section of the property sheet for the Ninox connectors is the common across all of them and provides an area to enter your API and to select the particular table you want to work with .

Enter your API key in the top text box and press the “Validate API” button:

This will expand the properties to show three further dropdown boxes which allow you to choose the Team, Database and Table you want the connector to work with:

Once you have made your selections the remaining options in the property sheet will depend on the specific type of connection and are documented below.

Common Ninox Properties – USAGE

The USAGE drawer of the properties panel allows control over how and when the data is refreshed.

• Refresh on load – This is the default setting and means that the Squirrel project imports the data when the project starts. 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 after each interval. The interval value is measured in seconds, setting it to 30 will ensure that the Squirrel project will pull the data every 30 seconds.
• Refresh on cell change – Bind to a cell that will have its content updated when the project is running. This could be caused by the user interacting with a control, input from a data mover function, or the result of a calculation etc.

Ninox Read Properties

The Ninox read connection allows data to be read from a Ninox table to a range in the Squirrel spreadsheet. Once it has been configured to access a particular database table the property sheet will expand to show the following additional properties:

• Limit rows returned – limits the number of rows which can be returned to the Squirrel spreadsheet from the database. For performance  this property is set at 1000 and cannot be changed (if you have a use which requires a return of more than 1000 rows please get in touch with us at support@squirrel365.io.)
• Filter Rows – this property is covered later in the article.
• Add column headers – selecting this option will add column headers to the returned data
• Insert data here – this property should be bound to the range of cells in the spreadsheet where the data from the Ninox table will be loaded.
• Sort data – this property is covered later in the article.

Once you have specified a range for the Ninox data to be read into, switch the designer to DEBUG mode, this will run your project and retrieve the data from the database table into the appropriate location in the spreadsheet

Data Filtering properties

If you check the Filter Rows property the property sheet will expand to show the following options:

• Retrieve rows WHERE – this property should be bound to a two row range in the spreadsheet which gives filter values for each column you wish to filter by. For example the sample data given in the property sheet will filter the data to those rows where the column “Invoice_No” has the value “1234” and the column “Product” has the value “Shoes”.

Data Sorting properties

If you check the Sort data property the property sheet will expand to show the following options:

• Select the column – use the dropdown to select the column whose data you want to use to sort the results
• Direction – select the appropriate option to specify how you want the results to be sorted

Ninox Create Properties

The Ninox create connection allows a new row to be inserted into a Ninox database table. Once it has been configured to access a particular database table the property sheet will expand to show the following additional properties:

• Insert – this property should be bound to a two row range in the spreadsheet which gives the values for each column to be inserted from the spreadsheet into the Ninox database table. For example the sample data given in the property sheet will insert a new where the column “Invoice_No” has the value “1234” and the column “Product” has the value “Shoes”. Any column which is not specified will be given a blank value. It is currently only possible to create one row at a time.
• Insert update response here – this property should be bound to a 2×2 (two row / two column) range in the spreadsheet. When the create is attempted the result is registered in this range in the following form:

The timestamp can be converted to an Spreadsheet date using the formula of the form:  <timestamp>/(1000*60*60*24)+25569, so if the timestamp was in cell H5 then the formula would be =H5/(1000*60*60*24)+25569

Ninox Update Properties

The Ninox update connection allows existing rows in the Ninox database table to be updated from data in the Squirrel spreadsheet. Once it has been configured to access a particular database table the property sheet will expand to show the following additional properties:

• Find rows WHERE – is property is used to identify the rows to be updated and should be bound to a two row range in the spreadsheet which gives values for each column to be used to identify the update rows. For example the sample data given in the property sheet will update all those rows where the column “Invoice_No” has the value “1234” and the column “Product” has the value “Shoes”.
• UPDATE with – this property should be bound to a two row range in the spreadsheet which gives the values for each column to be updated in the Ninox database table. For example the sample data given in the property sheet will update the column “gross” to a value of 100 and the column “net” to a value of “12”0″ for all rows which match the “Find rows WHERE” property. Column which are not specified are left with their existing values.
• Insert update response here – this property should be bound to a 2×2 (two row / two column) range in the spreadsheet. When the create is attempted the result is registered in this range in the following form:

The timestamp can be converted to an Spreadsheet date using the formula of the form:  <timestamp>/(1000*60*60*24)+25569, so if the timestamp was in cell H5 then the formula would be =H5/(1000*60*60*24)+25569

Zapier Connection

Zapier is a no-code tool that integrates web applications.  Within Zapier you create “Zaps” which connect your applications together. There are two main components of a Zap.  A trigger and an Action.

The Trigger is what starts the Zap.  This tells the Zap to run, and provides data to the Zap. The Action is what Zapier does with the data inside the Zap. For example, sending the data to another web application or system.

The Squirrel Zapier trigger connection allows you to trigger a Zap from your Squirrel project.

For example if you have a calculator in Squirrel where a prospective customer has entered some values to generate a quote.  Squirrel could send this data to Zapier where it automatically updates your CRM and sends you an email, or a text message.

To add a Zapier trigger connection to your project open the CONNECTIONS drawer in the object browser, select the “Zapier Trigger” entry from the dropdown box and click the plus button:

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

DEFINING THE TRIGGER

DEFINITION

To start the definition of the connection the data which will be sent to the Zap must be specified:

This will be a range with either two rows and a number of columns or 2 columns and a number of rows. For example if the data to be sent was about two new orders and included for each one the order number, the product ordered, the number of units and the total sale value then either these could be sent in either of the following two ranges:

When the trigger fires the data will be passed to Zapier which will trigger the Zap once for each row/column of data.

Once the data has been specified more properties appear:

• Data is in …  – this specifies whether the data specified is in rows or columns, choose the one which matches the data specified
• Includes header row – it is strongly recommended to have a header row/column in your data (as shown in the data example above) as this will make specifying the details of your Zap in Zapier significantly easier. If there is no header row/column in the data then this option should be unchecked and Squirrel will add headers of Item1, Item2, etc.
• Ignore blank rows – when this option is checked, Squirrel will remove blank data from the end of the range when sending the data to Zapier.
• Give your trigger a name – this property is used to give your trigger a meaningful name, this name is passed to Zapier and can be used to manage your triggers via the Squirrel 365 cloud.

Once all these properties are specified, click the “Register Trigger” button to register the trigger with the Squirrel365 cloud.

Once the trigger has been registered a Zap key is displayed. This key will be used to uniquely identify the trigger with Zapier. The copy button will place the Zap Key in your clipboard, you will need it later when setting up your Zap in Zapier.

TRIGGER ZAP WHEN …

• Project loads – If this option if checked the Zap will be triggered as soon as the Squirrel project has been loaded.
• Cell changes – If this option is checked then a cell in the spreadsheet needs to be specified and the Zap will trigger when the value in that cell changes.
• Cell Equals  – If this option is checked when the value in a cell equals a particular value
  • When cell – this property must be bound to a single cell in the spreadsheet
  • Equals – this gives the value against which the cell value is tested, it can either be bound to a single cell in the spreadsheet or specified directly in the property box.

CONFIGURING THE TRIGGER IN ZAPIER

In Zapier create a new Zap and select the Squirrel365 app and trigger event.

This is an “instant” trigger which means that as soon as Squirrel sends the data to Zapier, the Zap workflow is instantly executed.

Click Continue and in the next screen paste in the Zap key copied from the trigger connection’s property sheet (above) and click continue

The next step will be to test the trigger. When you press the “Test trigger” button, Zapier will communicate with the Squirrel365 cloud and get the sample data saved as part of the registration process above.

Once you’ve got the sample data you will be able to continue configuring Zapier to connect to any of the many actions available across a wide variety of web apps.

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: