System Guides | Sharepoint

Overview

SharePoint Online is a cloud-based service, hosted by Microsoft, for businesses of all sizes. Instead of installing and deploying SharePoint Server on-premises, any business can subscribe to a Microsoft 365 plan or to the standalone SharePoint Online service. Their employees can create sites to share documents and information with colleagues, partners, and customers.

Many Microsoft 365 customers also utilize Microsoft automation or IpaaS products as well, namely Power Automate and Azure, as part of their overall automation strategy. Microsoft SharePoint is best leveraged as a datastore for non-sensitive data lookups. The key benefits with leveraging Sharepoint is that non-technical users can update the data easily through a user-friendly interface, and that there are no APIs or developer experience required to build any custom APIs.

Products Leveraged

The Reference architecture below leverages the following Azure / SharePoint components:

  • SharePoint Lists: A datastore for non-sensitive data lookups, a list is a collection of data that you can share with your team members and people who you've provided access to.
  • SharePoint App-only API Access: SharePoint App-Only is the older, but still very relevant, model of setting up app-principals. This model works for both SharePoint Online and SharePoint 2013/2016/2019 on-premises and is a precursor to the SharePoint MS Graph API, currently in beta.

Integration Patterns & Best Practices

Paths Workspace

Since Sharepoint is mainly used as a datastore, rather than an iPaaS tool, it does not apply to the Paths workspace.

Query Workspace

Query Workspace use cases typically would leverage the Request-Response approach, using APIs and connectors to retrieve structured data to be returned by the bot. Here is a design pattern to easily query a spreadsheet via SharePoint’s Lists API:

  1. Create an Azure App Registration and grant it access to the SharePoint Site
    • Using App-only API Access
  2. Create a SharePoint List from a spreadsheet
  3. Retrieve the List of all Lists on a Site
    • https://{{sharepoint_tenant_name}}.sharepoint.com/sites/{{site_name}}/_api/Web/List
  4. Extract the desired list via its GUID or via its title
    • https://{site_url}/_api/web/lists(guid'{list_guid}')
    • https://{site_url}/_api/web/lists/GetByTitle('List Title')
  5. Use OData filtering to extract the value of a column by its column name
    • https://{site_url}/api/Web/Lists(guid'3d851631-ea7b-4494-85fd-0befa1d6da61')/Items?$filter=CustomerID eq 'acmecorp123'&$select=ID,CustomerName,ClientName,Flag,ARR

In some cases, you may not have this data in Sharepoint to start with. If you have data as an Excel sheet today, you can follow the instructions here to import them into

Event Workspace

The way Sharepoint and events workspace work together, is using Sharepoint as the datastore that backs messages that get sent to a user. Remember that Event Workspace use cases typically fall under one of 2 patterns: Event-Driven or Scheduled Tasks.

  1. Event-driven architecture: With Sharepoint, you can set up Sharepoint Webhooks , so that when an event is triggered (e.g: new item added to a Sharepoint list), then the SharePoint system sends an HTTP POST payload to the subscriber. It is recommended to use Azure Function Apps in conjunction with Sharepoint Webhooks, where Azure Function App acts as the subscriber, which listens for the webhooks and then contains the logic to craft real-time notifications to the Moveworks Event Workspace. See this guide for more details on how to leverage Sharepoint Webhooks and Azure Function Apps together. Note: the webhooks event themselves have up to 1 minute of latency.
  2. Scheduled Notifications: Use Azure Logic Apps or Azure Functions to perform periodic tasks, that
    1. Queries the Sharepoint List with a specified query. Similar to the Query Workspace, you can use OData filtering to get to the exact set of results you want.
    2. Performs any analysis e.g: filter results
    3. Sends out Messages via Event Workspace
  3. Queue Based Notifications: Sharepoint does not have any native queueing functionality, and typically event volume is not high enough to warrant queueing. However if your use case does warrant a need for queueing, you can send your Sharepoint webhook events to a

Other Considerations

Security considerations:

  • Authentication, authorization, and data protection are all baked into the Sharepoint and Azure Function Apps infrastructure, where a unique token is sent as the validationtoken in the URL parameters.
  • Most organizations avoid putting highly sensitive private information in Sharepoint. If you choose to put sensitive information in Sharepoint, ensure the site you are reading data from is shared appropriately within Sharepoint, and the corresponding use case with Creator Studio leverages Launch Options , so the use case is only available to the relevant users.