SharePoint Consulting Blog

Azure Open AI with SharePoint

We can index documents from a SharePoint library with Azure Cognitive Search, and use an Azure OpenAI model to query the data. Using a custom connector, we can bring this power into Power Automate.

Prerequisites

Have documents (*.docx, *.pdf) in a SharePoint library
Azure Cognitive Search service (Basic or Standard tier)
Azure OpenAI

Preparations

Note down the URL of the Site your library.
- Example: https://***nexus.sharepoint.com/sites/Manish/AI_Documents
Note down the URL and the Admin Key of your Azure Cognitive Search Service

Turn on System-Assigned Managed Identity in your Azure Cognitive Search Service
Create an Entra Id app registration with the following parameters:
- single tenant
  - Microsoft Graph API delegated permissions (don’t forget to grant admin consent) for
    - Files.Read.All
    - Sites.Read.All
  - Mobile and desktop applications
    - Enable
      - https://login.microsoftonline.com/common/oauth2/nativeclient (Note down the App Id.)

Connect your SharePoint library with Azure Cognitive Search

Create a Data Source with the Azure Cognitive Search Preview REST API

We have use the Automate flow to create a new data source for Azure Cognitive search using the REST API.
- POST to https://(name-of-your Azure Cognitive Search service).search.windows.net/datasources?api-version=2024-06-01-Preview
- Params:
  - api-version: 2024-06-01-Preview
- Headers:
  - Content-Type: application/json
  - api-key: (the Admin key of your Azure Cognitive Search Service)
- Body:
```
					  
  {
      "name": "sharepoint-datasource",
      "type": "sharepoint",
      "credentials": {
          "connectionString": "SharePointOnlineEndpoint=
          (your SharePoint Site URL0);ApplicationId=(your_App_Id)"
      },
      "container": {
      "name": "defaultSiteLibrary",
      "query": null
      }
  }   
                 
				
```
- Below is the HTTP Action we have used for create a source.
- This will return a 201 response, indicating that your data source was created. You can check this in the Azure portal.

Create your Index

Let’s now leverage metadata of your document to enhance your search experience. This as well is done by using the REST API. We will again do this in automate flow.
POST to https://(name-of-your Azure Cognitive Search service).search.windows.net/indexes?api-version=2024-06-01-Preview.
Params:
- api-version: 2024-06-01-Preview
Headers:
- Content-Type: application/json
- api-key: (the Admin key of your Azure Cognitive Search Service)

Body:

					  
  {
  "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
    }
  ]
}

This will return a 201 response, indicating that your data source was created. You can check this in the Azure portal.

Create your indexer

We want to create an indexer. It will later automate the indexing process from your SharePoint library to the Azure Cognitive Search service.
Once again, we do this in automate flow. This is a two-step process as we first need to POST a Create an indexer request - which will run and run and run as it is waiting for us to log in. So we will run a second call, which to GET the indexer status. This will return a device code with which we can sign in - Once we did that we can see that the call returns a 200. After that, the POST will succeed and return a 201 as well.

Create an indexer request

POST to https://(name-of-your Azure Cognitive Search service).search.windows.net/indexers?api-version=2024-06-01-Preview
Params:
- api-version: 2024-06-01-Preview
Headers:
- Content-Type: application/json
- api-key: (the Admin key of your Azure Cognitive Search Service)

* Image *

Body:

					  
{
  "name": "sharepoint-indexer",
  "dataSourceName": "aidocumentssource",
  "targetIndexName": "sharepoint-index",
  "parameters": {
	"batchSize": null,
	"maxFailedItems": null,
	"maxFailedItemsPerBatch": null,
	"base64EncodeKeys": null,
	"configuration": {
	"indexedFileNameExtensions": ".pdf, .docx",
	"excludedFileNameExtensions": ".png, .jpg",
	"dataToExtract": "contentAndMetadata"
	}
},
"schedule": {},
"fieldMappings": [{
	"sourceFieldName": "metadata_spo_site_library_item_id",
	"targetFieldName": "id",
	"mappingFunction": {
	"name": "base64Encode"}
}]
}

Get indexer status

Now, we have create a new flow for get a device login code.
- GET to https://(name-of-your Azure Cognitive Search service).search.windows.net/indexers/sharepoint-indexer/status?api-version=2024-06-01-Preview
- Params:
  - api-version: 2024-06-01-Preview
- Headers:
  - Content-Type: application/json
  - api-key: (the Admin key of your Azure Cognitive Search Service)
- This will return a response that contains an errormessage:
  - "errorMessage": "To sign in, use a web browser to open the page https://microsoft.com/devicelogin and enter the code LFXXXXXP to authenticate.\r\nTo sign in, use a web browser to open the page https://microsoft.com/devicelogin and enter the code LFXXXXXP to authenticate."
- Copy the code and open the link, then paste the code into the device login popup. Once you are logged in, you can close that browser tab again.
- Check now in the Azure portal that you do not only have an index, but also an indexer and documents indexed.

Test your app in the Playground

The Azure Open AI playground is a fabulous way to test and try out - so let’s do this.

In the Playground, create a new deployment
Select Add your data and then Add a data source
Select the Azure Cognitive Search service, your Subscription its running in, and the index we just created. All of these will automagically appear in the respective dropdown fields.
Now proceed with the index data field mapping - where you select all fields to be content
Save and close

You can now chat against your documents and ask the bot questions about it. By check/uncheck of the Limit responses to your data content you can determine whether you want the bot only to consider content from your documents or not. You can now deploy this as a web app - Or you can walk with me some more steps and have that power in Power Apps or Power Automate

If you have any questions you can reach out our SharePoint Consulting team here.