Copilot Studio Azure OpenAI with SharePoint Data Source

In this post, we will see how to index SharePoint documents with Azure AI Search index and use it with an Azure Open AI model for chat playground. Curently, SharePoint data source is not available in the Azure AI Search UI but we can configure using REST API. This GPT model further can be integrated with Copilot Studio and deployed to various channels such as MS Teams.

Prerequisites:
  • SharePoint sites with documents
  • Azure Entra Service Principal
  • Azure AI Search (to create SharePoint as Data Source, Search Index and Indexer)
  • Open AI Access
Create Service Principal:

We need to create an Azure Entra ID service principal to configure the SharePoint data source. In this case, Azure AI Search uses Graph API to access the SharePoint data. we use delegated permissions to configure app registration. You can can also use application permissions. You need to create client secret if you are using application permissions.

Navigate to Azure Entra Id -> App Registrations -> Create App Registration

Select API Permissions blade -> Add a permission -> Select Microsoft Graph -> Delegated Permissions -> Select Files.Read and Sites.Read permissions

Select Authentication blade -> Add a platform -> Select ‘Mobile and desktop applications’ -> Select Redirect URIs and set ‘Allow public client flows’ to Yes

Create Azure AI Search Resources

Now, we need to create Azure AI Search resource, SharePoint data source, search index and indexer using the REST API or Power Shell as SharePoint data source is not available in the Azure UI. Both PowerShell and Postman scripts ate provided to create each resource. Use either PowerShell or Postman script as per your toolset



Create Azure AI Search resource

In Azure Portal, Search for ‘AI Search’ -> Create a search service -> Select a Resource Group -> Enter Service Name -> Create

Search service should be created with the URL: https://.search.windows.net

Navigate to Keys blade and grab Primary admin key. This will be your apikey later used to create further resources

Create SharePoint Data Source

Run the below PowerShell script to create a SharePoint data source in AI Search. 

In this case. we are querying the data from a specified document library. you can use the entire site url as well. Replace variables as per your Search Service and SharePoint site URL. Include ApplicationSecret in connectionString in case of application permissions


$headers = @{ "api-key" = "<apikey>" }


$request = @"

{

    "name" : "sharepoint-datasource",

    "type" : "sharepoint",

    "credentials" : { 

        "connectionString" : "SharePointOnlineEndpoint=https://tenant.sharepoint.com/sites/sitename;ApplicationId=<appid>;TenantId=<tenantid>"

    },

    "container" : { 

        "name" : "useQuery", 

        "query" : "includeLibrary=https://tenant.sharepoint.com/sites/sitename/documentlibrary"

    }

}

"@


$uri = "https://<searchservicename>.search.windows.net/datasources?api-version=2023-10-01-Preview"


Invoke-RestMethod -Uri $uri -Headers $headers -Method Post -Body $request -ContentType "application/json"



Create SharePoint Index


Run below Power Shell script to create SharePoint search index. Replace variables as per your tenant resources


$headers = @{ "api-key" = "<apikey>" }


$request = @"

{

    "name" : "sharepoint-index",

    "fields":  [

        { "name": "id", "type": "Edm.String", "key": true, "searchable": false },

        { "name": "metadata_spo_item_name", "type": "Edm.String", "key": false, "searchable": true, "filterable": false, "sortable": false, "facetable": false },

        { "name": "metadata_spo_item_path", "type": "Edm.String", "key": false, "searchable": false, "filterable": false, "sortable": false, "facetable": false },

        { "name": "metadata_spo_item_content_type", "type": "Edm.String", "key": false, "searchable": false, "filterable": true, "sortable": false, "facetable": true },

        { "name": "metadata_spo_item_last_modified", "type": "Edm.DateTimeOffset", "key": false, "searchable": false, "filterable": false, "sortable": true, "facetable": false },

        { "name": "metadata_spo_item_size", "type": "Edm.Int64", "key": false, "searchable": false, "filterable": false, "sortable": false, "facetable": false },

        { "name": "content", "type": "Edm.String", "searchable": true, "filterable": false, "sortable": false, "facetable": false }

    ]

}

"@


$uri = "https://<searchservicename>.search.windows.net/indexes?api-version=2023-10-01-Preview"


Invoke-RestMethod -Uri $uri -Headers $headers -Method Post -Body $request -ContentType "application/json"      


Create Search Indexer


Run below Power Shell script to create search indexer. Replace variables as per your tenant resources. We need to execute the next script within 10 minutes after executing the below script and complete the device login and the indexer script. Indexer script waits until we complete the device login.


$headers = @{ "api-key" = "<apikey>" }


$request = @"

{

    "name" : "sharepoint-indexer",

    "dataSourceName" : "sharepoint-datasource",

    "targetIndexName" : "sharepoint-index",

    "parameters": {

    "batchSize": null,

    "maxFailedItems": null,

    "maxFailedItemsPerBatch": null,

    "base64EncodeKeys": null,

    "configuration": {

        "indexedFileNameExtensions" : ".pdf, .docx, .pptx",

        "excludedFileNameExtensions" : ".png, .jpg, .gif",

        "dataToExtract": "contentAndMetadata"

      }

    },

    "schedule" : {},

    "fieldMappings" : [

        { 

          "sourceFieldName" : "metadata_spo_site_library_item_id", 

          "targetFieldName" : "id", 

          "mappingFunction" : { 

            "name" : "base64Encode" 

          } 

        }

    ]

}

"@

$uri = "https://<searchservicename>.search.windows.net/indexers?api-version=2023-10-01-Preview"


Invoke-RestMethod -Uri $uri -Headers $headers -Method Post -Body $request -ContentType "application/json"      


Get Search Indexer Status

Execute the below script while indexer script awaits to complete the device login. The below script returns a response with errorMessage and the code to authenticate.

$headers = @{ "api-key" = "<apikey>" }


$uri = "https://<searchservicename>.search.windows.net/indexers/sharepoint-indexer/status?api-version=2023-10-01-Preview"


Invoke-RestMethod -Uri $uri -Headers $headers -Method Post -Body $request -ContentType "application/json"   


Navigate to https://microsoft.com/devicelogin and enter the code returned from the above script and approve permissions. It will ask you to sign-in into the tenant.

The Search Indexer will access the SharePoint content as the signed-in user. The user that logs in during this step will be that signed-in user. So, if you sign in with a user account that doesn’t have access to a document in the Document Library that you want to index, the indexer won’t have access to that document.

Now, Search Indexer should be able to index all of the documents the signed in user has access to within the SharePoint document library.

Create OpenAI Service

Search ‘Azure OpenAI’ in Azure Portal -> Select Resource group, Name and create a OpenAI resource

Navigate to Azure OpenAI Studio from OpenAI resource

Select ‘Bring your own data’ -> Select Azure AI Seach frm data source dropdown -> Select Azure AI Search Service and Search Index created above

Select Search type ‘Keyword’ -> Save and close

Start chatting with your SharePoint data in the Azure Open AI Chat playground

Deploy to Copilot Studio

You can fine tune and test the model and further deploy to Copilot Studio from Azure Open AI Chat playgroud and access your copilot from Copilot Studio.





Comments

Post a Comment

Popular posts from this blog

Create and Deploy custom copilot to SharePoint Site (Part 2)

Image
  Custom Copilot as a canvas control on SharePoint site: This post is an extension to Part 1 to deploy the custom Copilot to SharePoint site. Before proceeding further please refer to the Part 1 as we are leveraging the custom Copilot and Service Principal created in the previous post. Deploying custom Copilot requires an additional app registration(this is different than the one created in the Part 1 for enabling authentication) to register SharePoint site as a canvas app to host copilot and handle single sign on flow. Create App Registration for SharePoint App: Login to https://portal.azure.com -> Microsoft Entra ID -> Create New App Registration Select 'Single page application' for platform and the SharePoint URL where the copilot is to be hosted and accessed Select API permissions blade and add custom API permission which was created in part 1 Save the Application(client Id) and the tenant id these will be used in SharePoint canvas app Deploy SharePoint SPFx App on ...
Read more

Create and Deploy custom copilot to MS Teams and SharePoint Site (Part 1)

Image
Custom Copilot using Copilot Studio published to MS Teams: In this two part series we see how to create a custom copilot using Microsoft Copilot Studio for Generative Answers based on the SharePoint Knowledge DB(files uploaded to SharePoint) and deploy the copilot to MS Teams and a SharePoint site. Deploying custom copilot to MS Teams and SharePoint require an authentication enabled. Lets create an Azure Service Principal(Client Id and Secret) required to enable Manual Authentication as deploying to SharePoint requires Manual Authentication. Create App Registration for Authentication: Login to https://portal.azure.com -> Microsoft Entra ID -> Create New App Registration    Add delegated API permissions: openid, profile, Files.Read.All, Sites.Read.All from Microsoft Graph Add a Scope from 'Expose an API' blade. This is required to publish the copilot to SharePoint site. You can ignore this step if you are publishing your copilot to MS Teams only. Crete a client secret fro...
Read more

Copilot Announcements from Microsoft Build 2024

Image
  Microsoft Announced major updates to Copilot Studio in Build 2024. In this article we will see the new Copilot features announced in Build 2024. New User Experience Redefined simplified developer experience for creating copilots using Copilot Studio.  Templates Industry specific templates to create custom copilots based on the industry specific templates. Templates will accelerate the development as we do not need to start from scratch. Some of these templates are Store Operations, Sustainability Insights, Website Q & A etc. we can also save custom copilot as template and made it available to others. Create Copilots from SharePoint - Private Preview Users can directly create custom copilots from SharePoint site by selecting documents and extend the copilot using Copilot Studio. Copilot will be available on the SharePoint site and we can directly ask any questions on the data from the data within the SharePoint site Copilot Connectors/Knowledge Sources We can now bring in...
Read more