Excel One Drive Writeback

Who can use this feature?

The Excel Writeback functionality can be used by those with the Builder subscription plan.

For more details, see our pricing comparison matrix.

The Excel Writeback 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 Writeback connector to your project by opening the CONNECTIONS drawer in the object browser, selecting Excel Writeback from the dropdown box, and clicking the plus button:

The Excel Writeback 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 Writeback connector will then appear:

WRITEBACK

A radio with three options will appear under the WRITEBACK drawer.

• Insert - bind to a range that will be added to the bottom of an existing range in the Excel sheet

• Update - bind to a range that updates the first row that matches the condition set below. Insert manually or bind to a cell that contains the column letter reference and the value to match to determine the row.

• Upsert - bind to a range that updates the first matching row; if the row cannot be found then it adds the row to the bottom of the range.

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.