Excel One Drive Read

This article describes the Excel One Drive Read connector and its properties.

Who can use this feature?

All Squirrel365 plans include the Excel OneDrive Read connector.

The Free plan limits the connector to 50 rows.

Creator and Builder plans have no row limit.

For more details, see our pricing comparison matrix.

The Excel One Drive connector allows data to be imported from a spreadsheet stored in One Drive into your Squirrel project at runtime. It also gives options for filtering and refreshing that data.

Add an Excel One Drive connector to your project by opening the CONNECTIONS drawer in the object browser, selecting Excel One Drive from the dropdown box, and clicking the plus button:

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

Excel One Drive Connector Properties

EXCEL DOCUMENT

To connect to an Excel spreadsheet in your One Drive, you will need to connect to a One Drive account.

• Select a One Drive account - click the dropdown to connect a OneDrive account. After selecting New Account, a window will appear to enter your OneDrive account details.

After adding the account details, you will directed to a File Explorer modal that displays your OneDrive files. Navigate to locate the file you wish to import. Select the required Sheet Name and Table (if sheet contains tables). Then click Continue.

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

Once you have selected the sheet that 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.

• Where do you want to insert this data - select from this dropdown to determine how you want to insert the data into the spreadsheet.

Create a new datasheet

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:

Datasheets 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).

Select a range in an existing sheet

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

Ensure that the configuration of the destination cells is sufficient and suitable for the amount and format of data being imported e.g. If the intention is to import 50 rows of data from two columns, ensure the destination cells cover 50 rows and two columns.

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

It can take a few moments or more (depending on the volume of data in the Excel One Drive spreadsheet) for the data to appear in the Squirrel spreadsheet.

USAGE

The USAGE drawer of the properties panel allows for some control over how and when the data is refreshed. If the Excel One Drive connector is to an Excel One Drive 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 One Drive spreadsheet on project load. Unchecking it will mean that one of the other usage properties will need to be checked 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 One Drive 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 One Drive 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.