# Power Automate SharePoint Connector The current Power Automate–based SharePoint connector is being replaced by our new cloud-native [SharePoint Connector](https://docs.unique.ai/it-operators/integrations-connectors/sharepoint-connector). ## Overview The **SharePoint Connector** connects Microsoft SharePoint Online to Unique AI via Microsoft PowerAutomate. The Unique SharePoint connector consists of a series of [Power Automate flows](https://learn.microsoft.com/en-us/power-automate/get-started-logic-flow) bundled in a solution that will be provided by Unique (ZIP file). Clients need to import and setup the Solution in their own environment’s Power Automate. As the solution uses environment variables, **only one solution can be deployed per environment.** Files and site pages to sync are identified by an ad hoc boolean column (`Unique AI` in this documentation) whose name can be customised. The solution **runs globally** and is able to sync all SharePoint sites and libraries **at once**. ## Requirements - A Power Automate (PA) environment with a Dataverse database - A PA user that has the following permissions on SharePoint: - Read list and library names, as well as the names of the columns for all synchronised sites - Read files and metadata for all synchronised sites and libraries - Read and update the Configuration List items - Power Automate required connectors: - [SharePoint](https://learn.microsoft.com/en-us/connectors/sharepointonline/) - [HTTP](https://learn.microsoft.com/en-us/power-automate/desktop-flows/actions-reference/web) - [Azure Key Vault](https://learn.microsoft.com/en-us/connectors/keyvault/) - Premium connector - An Azure key vault with PA user read permission - Ability to call Unique’s endpoints from the PA environment ## Current Export of the Solution v. 1.0.0.9 (update: file type validation) [CSVUnique_SP_Connector_Configuration_List.csv](https://unique-ch.atlassian.net/wiki/rest/api/content/1385005939/child/attachment/att1603534923/download) [ZIPUniqueFinanceGPTSharepointConnector_1_0_0_9.zip](https://unique-ch.atlassian.net/wiki/rest/api/content/1385005939/child/attachment/att1603534929/download) ## Architecture The architecture of the SharePoint connector with the scheduled sync approach makes the SharePoint integration more scalable and more stable compared to an event based approach, as per the current state of SP events that show unreliability and inconsistency (Aug 2024). The benefits of following the scheduled approach are: - Single run to synchronise multiple libraries without the need to duplicate the PA flows. - Easy and quick customisation by the customer’s SP admins to fit tailored needs. - Direct overview of the flow runs and errors by the SP admins allowing facilitated debugging by the same team that is in charge of running and maintaining SP. The solution is a collection of flows that scan the files to ingest, detect the changes, and trigger ingestion, changes of folder, or deletion where needed. The synchronisation flows are called sequentially and the whole synchronisation process cannot run in parallel to ensure consistency. This means that no matter how high the synchronisation frequency is set, no new synchronisation will be triggered until the ongoing synchronisation is done sending all files to be ingested to Unique. ## Overview Diagram The Logic of the scheduled SharePoint Flow ## Scheduler flow The “Scheduler” flow in the solution is responsible for triggering the sync of the files from SharePoint to Unique’s knowledge base. The flow triggers the synchronisation process by calling the `Sharepoint Files Scan` flow. ## Sharepoint Files Scan flow The flow holds the main logic part and loops through the sites and their libraries. In the process it calls the child flows `IDP Get Access Token` to get the Unique token, and `Ingest Content`. A list of SharePoint Sites that contain content to synchronise in some or all of their libraries must be provided as a configuration list. The main logic in the flow uses this input and executes a series of nested loops. Within the listed sites, the flow identifies the libraries that have the `Unique AI` column set, and fetches the set of files that have been marked for ingestion. We filter for the published documents and Site Pages with `OData__ModerationStatus` properties on the file. Field values are as follows: ``` 0 = Approved 1 = Rejected 2 = Pending 3 = Draft ``` A diff is calculated by calling Unique `file-diff` endpoint on the ingestion service: - _Deleted files_ → if content exists in the knowledge base, the content gets deleted from the knowledge base by Unique’s backend. - _Moved files_ → path will be adjusted by Unique’s backend to maintain an accurate scope. - _New files/ content updated_ → the list of created and updated files is sent back to the flow, and is passed to the `Ingest Content` flow. ## Ingest Content flow The flow is responsible for handling the calls to the Unique ingestion API and uploading the file content to the blob storage for ingestion. `IDP Get Access Token` flow is called as well in this flow to get a token for the API calls. Calls to the flow are library based: the flow loops through the provided file id list of new and modified files within a single library and sends them for ingestion. ## IDP Get Access Token flow The flow is responsible for getting an access token that can be used to call the Unique APIs. Unique sets up a service user for each client that has the necessary permissions to call the ingestion APIs. The service user’s client credentials will be provided to the clients and are used to get a token in this flow. ### username + password flow (deprecated) If you are not using a key vault you can use the `IDP Get Access Token - username + password` flow. You will need to configure the related environment variables. You will need to modify the child flow called in the `Get Access Token` actions in both Sharepoint Files Scan and Ingest Content flows to select the proper child flow to get the token. ## Delete Flows Two flows are responsible for deleting all files belonging to a site and to its libraries. ### Delete Library Deletes the library by sending an empty array to `file-diff`. Ingested files are deleted in Unique by Unique’s backend. ### Delete Site Loops through all site libraries and call `Delete Library` for each one. The flow is called when a site is marked for deletion in the configuration list. ## Setup ### Add custom column in SharePoint A custom boolean column needs to be added to a SharePoint library to control the ingestion of the files. To set the custom column: 1. In SharePoint, add a custom column by clicking the “+ Add column” button. Select the column type “Yes/No”. 2. Name the custom column. This will be the name you need to use when setting up the Power Automate Flow variables. Also set a default value for new files. 3. After saving and creating the column, you can optionally format the column to make the selected values more obvious to the users in SharePoint. Select the column’s dropdown > Column settings > Format this column. There select “Format yes and no” (you can also choose the colors by editing the styles). ### Setting up the SharePoint Connector’s Power Automate flows The setup process for the Unique SharePoint Connector consists of the following steps: 1. Import the Power Automate Solution provided by Unique 2. Configure the environment variables while or after having imported the solution ### Import the Power Automate Solution Unique provides the Unique SharePoint connector to Customers as an exported Power Automate solution, which is a ZIP file. Along with the ZIP file, the Customers receive client credentials and all necessary values for configuring the environment variables. In Power Automate navigate to the `Solutions` tab on the left side. You should see an overview of all existing solutions. On the top, click the `Import solution` button and you will be prompted to provide a file. Upload and import the ZIP file that Unique provided containing the Power Automate solution for the Unique SharePoint connector. ### Set Up the Configuration list The Sharepoint sites to synchronise are stored in a Sharepoint list that needs to be accessible from the Power Automate Flow. The site hosting the list does not have to be synchronised but it must be accessible by the user running the flow. The list must contain the following columns: - `Title` (_Text_): the title of the site. - `Url` (_Text_): the full url of the site including the protocol and the SP domain. Ex: [https://uniqueapp.sharepoint.com/sites/SiteToSync](https://uniqueapp.sharepoint.com/sites/QA) - `Scope` & `Owner Type` (_Choice_): These two variables must be setup in accordance with each other. - `Owner Type`: `SCOPE` or `COMPANY` - `Scope`: `COMPANY` or `PATH` or the scope Id applicable to **all synced files** - Possible set of values and behaviour: - If both variables are set to `COMPANY` the files will be available to the whole company. - If `Scope` is set to `PATH`, `Owner Type` must be set to `SCOPE`: each folder will generate its own permission scope that will be applied to nested **files but not to subfolders**. - If `Scope` is set to a specific scope id applicable to all synchronised files, `Owner Type` must be set to `SCOPE` ⚠ **Any other set of scope values will fail the ingestion calls** ⚠ - `Delete` (_Yes/No_): marks the site for deletion. ### Configure environment variables There are two logical sets of environment variables with the global prefix `ufgpt` like the rest of the objects within the solution: - `sp_xxx` related to SharePoint setup - `uq_xxx` related to Unique setup The SharePoint variables must be configured as such: - `sp_domain`: the root SharePoint domain - Configuration list variables: access to the config list is managed by 2 environment variables: - `sp_list_hosting_site`: the site where the configuration list is hosted - `sp_sites_list_name`: the name of the list - the urls of the sites listed **MUST** be stored in a column named **exactly** `Url` - you can pass the list display name in `sp_sites_list_name`, but it is recommended to pass the **list id** to prevent disruption if the list name is inadvertently modified. - Go to the list then `Settings > List settings`. The list id can be found in the url as `List={the-list-id}`. - `sp_sync_column_name`: the name of the Unique column that controls for **file** synchronisation The Unique variables must be configured as such: - `uq_file_sync_endpoint`: the url of the `file-diff` endpoint on `ingestion` service - `uq_idp_project_id`: the Zitadel project id - `uq_idp_token_endpoint`: the Unique token issuer endpoint - `uq_ingestion_graphql`: `/graphql` endpoint on `ingestion` service - `uq_store_internally`: boolean value that defines whether or not the synced documents should also be stored internally at Unique. - If you are using the username + password credential flow (deprecated), set up the following 2 variables: - `uq_idp_client_id`: Zitadel client id - `uq_idp_client_secret`: Zitadel client secret Power Automate does not like empty environment variables so if they are not used, these variables should be left to the default empty string value `""`. ## Scopes Considerations ### Scope types 3 types of scopes are available: - `PATH`: file access scopes are attributed by SP folder. - `COMPANY`: all ingested files are available at the company level - **Scope Id**: all ingested files are attributed to a **single** specific scope identified by its scope id. ### SP Service User Scope Access The connector is in charge of creating the needed scopes in the case of scope set to `PATH`. The service user is automatically granted `READ/WRITE` permission on all the scopes it creates, and **only on those**. ## Known Limitations & Issues ### Ingestion of linked content in SharePoint Pages What can be ingested from SharePoint pages has limitations. **What works:** - All text content **What does not work:** - Linked document libraries - Linked content in general The limitation stems from the fact that we fetch the content of the SharePoint page from SharePoint’s API via Power Automate flow and what we receive there is the actual content on the page. ### Other Known Limitations Note that most known limitations can be overcome by adapting the flows to your needs. The SharePoint Inline - Power Automate connector is designed to serve as a general purpose connector for the Unique chat ingestion. It mostly serves as a basis for further configurations that match your specific needs.