Excel Online Data Source

SharpCloud has the capability of connecting to Excel Online Tables directly to import data and create items/relationships.

To be able to use Data Sources, you must authenticate via O365/Azure AD when logging in to SharpCloud.

Users must remain in the browser to bring in data stored in Excel tables in their Office 365 Accounts (Cloud).

Note that you can also connect to Excel Online using our Excel Online Connector, discussed in another article.

Creating Items from Excel Online Tables

SharpCloud uses your Office 365 / Azure Active Directory (AD) account to authenticate with your SharePoint library. If you do not login to SharpCloud using this method, then you will not be able to access the SharePoint features.

Access data sources (Spreadsheets, Lists etc.) from directly within your story via the Data Dropdown > Data Sources.

 

 

1. Click on Data Sources and the Manage Data Sources dialog box will appear. Click “Add Data Source” as shown below.

2. A new dialog box will appear, giving you the options to choose between SharePoint Lists or Excel Table (Online) and given the choice to create either Items or Relationships.

3. Give your data source a name (required) and description (optional). Once that has been done, the data source can be added.
4. Search available sites associated with the O365/Azure AD that was used to authenticate into SharpCloud and select the document that holds your data table.

5. If prompted, select the specific workbook tab which holds your data table.

Note: It is required that the data exists in a table. If the search is returning no results after finding the specific Excel Sheet, ensure that the data is stored in a table otherwise it will not return any options.

The legend for what each colour means when syncing your data can be found in the top corner of the table. The legend is as follows:

Blue – New – Any row that is highlighted blue means that SharpCloud will create a new item/relationship per row.

Orange – Updated – Any row that is highlighted orange means that SharpCloud will update that item/relationship as it has changed in Excel but not yet updated in SharpCloud.

Red – Deleted – Any row that is highlighted red means that SharpCloud will delete this item/relationship as the data no longer exists in Excel.

 

6. Once the correct data has been located and selected to be imported, you can configure which columns are being used for the Categories and how to match your data using the selection tools on the right hand-side.

Item Name – Use this column – This is where you specify which column in your data set to use as the item name.

Category – This is where you specify which category to move items into during the data import. The options are:

            Use this Category - Pick an existing category (in the story) to move all the items into. One category for all items

            Use this Column – Use a certain column in the data as the category. SharpCloud will create each category (if it doesn’t already exist) and move every item in to the relevant category.

7. Once the settings have been decided and confirmed, pressing “Create Items” will create the items in the specific categories that have been chosen.

Once the items have been created, anytime the Data Sources are revisited in the Data Sources dialog, it will provide you a set of options of just creating new items, updating existing items or deleting items. You can perform all of them at once or have a selection in any combination.

Note: If bringing data into your story for the first time (before your categories and attributes have been created), you may wish to paste it in from your Excel table first. By doing this, SharpCloud will verify which attribute type should be used for each column’s data and allow you to clean things up a bit as your items are imported. Once your structure is set, you can then create the connection to your Excel table and use this to make updates in the future.

Creating Relationships using Data sources will follow the same process, apart from Step 5, where instead of choosing the category, you can select what you want to match on for the relationships as shown below.