Excel Online Connector

 

Overview
The Excel Online connector allows you to import tables of data from files stored in your Microsoft 365 account into a SharpCloud story. It supports creating, updating, and deleting items based on Excel data.

Authentication Methods
You will be prompted to authenticate your Microsoft account using OAuth and grant permissions. Your organization’s IT team may need to approve access.

You can optionally enter a SharePoint site URL where the Excel file is stored or leave the field blank to connect to OneDrive. Select Authenticate and follow the prompts to sign in.

When entering a SharePoint URL, use the site URL rather than the direct workbook link. You can copy this from the SharePoint site homepage. A confirmation popup will appear once authentication is successful.

Use Cases

• Import business data managed in Excel into SharpCloud
• Keep story data aligned with Excel workbooks
• Create, update, and delete items from Excel tables
• Use multiple connectors to bring in multiple datasets

When using the connector, selected workbooks are added to the story and can be accessed later via Data Grid > Resources.

How to Use the Connector

1. Connect
   After creating the connector, go to the Connect tab and select a workbook from the dropdown list.
   1. Only workbooks directly shared with you will appear.
   2. If a workbook is missing, open it and share it with yourself.

 

2. Map Data
   Click Next to access the mapping tab. Select the worksheet (table), choose the target category, and map Excel columns to SharpCloud attributes.
   1. The Table dropdown lists worksheets in the workbook.
   2. The Categories dropdown lists existing story categories.
   3. Use View Source Data to preview how the data is interpreted.

3. Set External ID
   You must define an External ID to uniquely identify each item.
   1. A GUID is generated automatically for each row, but it is recommended to use a unique field from your Excel data, especially if rows are added or removed, to avoid incorrect mapping or data loss.

 

4. Additional Configuration
   You can apply filters to incoming data, for example importing only rows where Priority equals High.

Tag Groups and Categories must exist before mapping and can be created in Story Setup.

5. Advanced SQL (Optional)
   Enable Show Advanced SQL to customize queries and perform advanced operations such as JOINs.
   1. Once SQL is edited, you cannot return to the standard mapping interface.

6. Preview and Synchronize
   Click Next to preview the changes.
   1. Blue rows indicate new items.
   2. Orange cells indicate modified values.
   3. Click Synchronize to apply changes and view a summary of updates.

7. Deletion Behavior
   Delete Records from Story is enabled by default.
   1. If a record is removed from the Excel source, it will also be removed from SharpCloud. 
      1. Example: if the source changes from A, B, C to A and C, item B will be deleted.

8. Publish and Automation
   Once configured, click Publish to finalize the connector.
   1. Published connectors cannot be edited and can be run by any story admin.
   2. The credentials of the publishing user are used for data access and updates. 
   3. After publishing, the connector can be scheduled to run automatically on an hourly or daily basis.