SharpCloud provides complete integration with common Microsoft SharePoint data sources, such as Excel Online, where you can pull in data stored in a table from a spreadsheet, and constantly sync your stories to remain up-to-date with the latest data, maximizing collaboration between teams.
This functionality removes the manual operation of copying and pasting between SharpCloud and the desktop version of Excel, fully incorporating data entry seamlessly into the SharpCloud interface to provide greater transparency across your business process.
Users must remain in the browser to bring in data stored in spreadsheets in their Office 365 accounts (Cloud).
Note: You will need to sign into SharpCloud with an Azure AD / O365 account to be able to use this feature.
Creating Items from Excel Online using Data Sources -
1. 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.
As soon as you login, you will not be prompted to enter your credentials to SharePoint again and you can access your data sources (Spreadsheets, Lists etc.) from directly within your story via the Settings Menu > Admin > Data sources.
2. Click on 'Data sources' and you'll see the following box appear. Click on 'Add Data Source'.
3. The following dialog box will appear, and you'll have the option to choose between a SharePoint List (Online) or an Excel Table (Online). Secondly, you'll be asked if you want to sync Items or Relationships.
Select what type of data you want to bring in (Items or Relationships) and then pick a Name of the data connection as well as a Description.
4. After naming the connection, click OK and then SharpCloud will present you with a list of all the sites available to pull data from. These sites are associated with your O365/Azure AD login so you will see all the sites that you have created in this Microsoft account.
In this example, we only have one site called Development. Click on the site to continue through the process.
After confirming what site your data is stored in, click the relevant Excel spreadsheet you want to pull data from to create items in your story. SharpCloud will capture this information automatically.
Note: Please remember to convert all your data from the standard Excel formatting into a Table. This can be done by going into the original Excel file, selecting all the data and then click Convert to Table from the formatting bar.
5. You’ll then be prompted to pick what sheet (from your spreadsheet) you want to pull the data from.
6. After confirming this, you'll then be prompted to pick a table from within your sheet. You should only have one as you would have previously converted all your data to a table formatting.
7. Next you'll see a preview of the data you’re bringing in. To begin with, all this data will be highlighted Blue to let you know that all the rows will create new items.
After going through this process a few more times, you’ll notice that rows will be highlighted Orange after you’ve edited any aspect of this row (e.g. any attribute value). The orange colour informs you that this item is going to be updated in your SharpCloud story.
The same applies to when items are deleted from your original data source, you’ll notice that rows are highlighted Red to inform you that the following items are going to be deleted from the story.
8. To define how you want your rows to be interpreted in your story, you can use the settings on the right-hand side to pick what column you want to use for the Category you'd like the items to be put in.
Category - This is where you specify what category you want to put your items in during the data entry. The options are:
'Use this category' - Pick an existing category (in your story) to move all your items into. One category for all items.
'Use this column' - Use a certain column as your category. SharpCloud will create each category (if it doesn't already exist) and move every item into the relevant category.
When you are happy with your selection and know what columns to include, click Create Items and you'll see the progress bubble pop up.
9. Once completed, you will see your story populate with the items (rows) sorted into their assigned category. You may have some default categories remaining, but you can simply delete these in Story Setup.
Done! This is the process of how to connect to an Excel (Online) spreadsheet stored within a SharePoint site and create items in a blank story.
Creating Relationships from Excel Online using Data Sources -
Creating relationships between items from an Excel Online document can be done by following the same steps as how you would go about to create items, apart from Step 3 and Step 8 (See above). The columns required to create relationships from
Excel are "Item 1" and "Item 2", you can see examples of this in the "Paste data in from Excel" item in the help story.
At the point of Step 3 (above), with the drop down menu that appears, you just have to select that you wish to create Relationships rather than Items just as shown below.
At the point of Step 8 (above), when creating relationships a different box will appear asking you to define how you want your rows to be interpreted in your story, you can use the settings on the right-hand side to pick what column you want to use as Item Name to chose between what items to draw the relationships between.
When you are happy with your selection and know what columns to include for your relationships, click Create Relationships and you'll see the progress bubble pop up.
Once completed, you will see your story populate with relationships linking the specified items together. You may have to turn the relationships on via the Relationships icon.
Done! This is the process of how to connect to an Excel (Online) spreadsheet stored within a SharePoint site and create relationships between items.