Cognitive Search API - SharePoint Data Source - Reusable Postman collection

readme.md

Reusable Postman collection that configures SharePoint Data source for Cognitive Search

Postman Collection Variables

First, download the Cognitive Search API - SharePoint Data Source.json file and import it into your Postman. Then, in the Variables section of your imported collection, setup the following variables:

• AZURE_SEARCH_SERVICE_NAME - Your Azure Cognitive Search Service Name. You can retreive it from the URL. It goes between https:// and .search.windows.net: https://clever-cognitive-search.search.windows.net
• AZURE_SEARCH_ADMIN_KEY - Cognitive Search Admin key
• AZURE_APPLICATION_ID - Azure App Registration ID. Note: The app must have read access to your SharePoint tenant
• AZURE_APPLICATION_SECRET - Azure App Registration Secret.
• AZURE_TENANT_ID - Azure Active Directory Tenant ID. The same that hosts Cognitive Search and SharePoint.
• SHAREPOINT_SITE_URL - Full URL of a SharePoint site. For example, https://gocleverpointcom.sharepoint.com/sites/Teailspin-Records-CognitiveSearchDemo
• SHAREPOINT_LIBRARY_URL - Full URL of a document library. For example, https://gocleverpointcom.sharepoint.com/sites/Teailspin-Records-CognitiveSearchDemo/Shared Documents/Forms/AllItems.aspx
• SHAREPOINT_ADDITIONAL_COLUMNS - Custom SharePoint columns that you want to index. For example: Title,Year,DocumentType,Critical,LastReviewed,DocumentOwner,AnnualCosts,FullFilePath

Important resources

• Download Postman
• Quickstart: Create an Azure Cognitive Search index using REST APIs
• Index data from SharePoint document libraries

Video tutorial

https://youtu.be/GiSwKJ7xkZA?si=2nEK3vrSDnJY3DV8&t=431

Cognitive Search API - SharePoint Data Source.json

{
	"info": {
		"_postman_id": "7f9a258e-feee-40a7-827f-760caa441e43",
		"name": "Cognitive Search API Reusable",
		"schema": "https://schema.getpostman.com/json/collection/v2.1.0/collection.json",
		"_exporter_id": "641744"
	},
	"item": [
		{
			"name": "Create SharePoint Data Source",
			"request": {
				"method": "POST",
				"header": [
					{
						"key": "api-key",
						"value": "{{AZURE_SEARCH_ADMIN_KEY}}",
						"type": "text"
					},
					{
						"key": "Content-Type",
						"value": "application/json",
						"type": "text"
					}
				],
				"body": {
					"mode": "raw",
					"raw": "{\r\n    \"name\": \"sharepoint-datasource\",\r\n    \"type\": \"sharepoint\",\r\n    \"credentials\": {\r\n        \"connectionString\": \"SharePointOnlineEndpoint={{SHAREPOINT_SITE_URL}};ApplicationId={{AZURE_APPLICATION_ID}};ApplicationSecret={{AZURE_APPLICATION_SECRET}};TenantId={{AZURE_TENANT_ID}}\"\r\n    },\r\n    \"container\": {\r\n        \"name\": \"defaultSiteLibrary\",\r\n        \"query\":\"includeLibrary={{SHAREPOINT_LIBRARY_URL}};additionalColumns={{SHAREPOINT_ADDITIONAL_COLUMNS}}\"\r\n\r\n    }\r\n}"
				},
				"url": {
					"raw": "https://{{AZURE_SEARCH_SERVICE_NAME}}.search.windows.net/datasources?api-version=2021-04-30-Preview",
					"protocol": "https",
					"host": [
						"{{AZURE_SEARCH_SERVICE_NAME}}",
						"search",
						"windows",
						"net"
					],
					"path": [
						"datasources"
					],
					"query": [
						{
							"key": "api-version",
							"value": "2021-04-30-Preview"
						}
					]
				}
			},
			"response": []
		},
		{
			"name": "Create an index",
			"request": {
				"method": "POST",
				"header": [
					{
						"key": "api-key",
						"value": "{{AZURE_SEARCH_ADMIN_KEY}}",
						"type": "text"
					},
					{
						"key": "Content-Type",
						"value": "application/json",
						"type": "text"
					}
				],
				"body": {
					"mode": "raw",
					"raw": "{\r\n    \"name\" : \"sharepoint-index\",\r\n    \"fields\": [\r\n        { \"name\": \"id\", \"type\": \"Edm.String\", \"key\": true, \"searchable\": false },\r\n        { \"name\": \"metadata_spo_item_name\", \"type\": \"Edm.String\", \"key\": false, \"searchable\": true, \"filterable\": false, \"sortable\": false, \"facetable\": false },\r\n        { \"name\": \"metadata_spo_item_path\", \"type\": \"Edm.String\", \"key\": false, \"searchable\": false, \"filterable\": false, \"sortable\": false, \"facetable\": false },\r\n        { \"name\": \"metadata_spo_item_content_type\", \"type\": \"Edm.String\", \"key\": false, \"searchable\": false, \"filterable\": true, \"sortable\": false, \"facetable\": true },\r\n        { \"name\": \"metadata_spo_item_last_modified\", \"type\": \"Edm.DateTimeOffset\", \"key\": false, \"searchable\": false, \"filterable\": false, \"sortable\": true, \"facetable\": false },\r\n        { \"name\": \"metadata_spo_item_size\", \"type\": \"Edm.Int64\", \"key\": false, \"searchable\": false, \"filterable\": false, \"sortable\": false, \"facetable\": false },\r\n        { \"name\": \"content\", \"type\": \"Edm.String\", \"searchable\": true, \"filterable\": false, \"sortable\": false, \"facetable\": false },\r\n\r\n\r\n        { \"name\": \"Title\", \"type\": \"Edm.String\", \"searchable\": true, \"filterable\": false, \"sortable\": false, \"facetable\": false },\r\n        { \"name\": \"Year\", \"type\": \"Edm.String\", \"searchable\": true, \"filterable\": false, \"sortable\": false, \"facetable\": false },\r\n        { \"name\": \"DocumentType\", \"type\": \"Edm.String\", \"searchable\": true, \"filterable\": false, \"sortable\": false, \"facetable\": false },\r\n        { \"name\": \"Critical\", \"type\": \"Edm.String\", \"searchable\": true, \"filterable\": false, \"sortable\": false, \"facetable\": false },\r\n        { \"name\": \"LastReviewed\", \"type\": \"Edm.String\", \"searchable\": true, \"filterable\": false, \"sortable\": false, \"facetable\": false },\r\n        { \"name\": \"DocumentOwner\", \"type\": \"Edm.String\", \"searchable\": true, \"filterable\": false, \"sortable\": false, \"facetable\": false },\r\n        { \"name\": \"AnnualCosts\", \"type\": \"Edm.String\", \"searchable\": true, \"filterable\": false, \"sortable\": false, \"facetable\": false },\r\n        { \"name\": \"FullFilePath\", \"type\": \"Edm.String\", \"searchable\": true, \"filterable\": false, \"sortable\": false, \"facetable\": false }\r\n\r\n        \r\n    ]\r\n}"
				},
				"url": {
					"raw": "https://{{AZURE_SEARCH_SERVICE_NAME}}.search.windows.net/indexes/?api-version=2021-04-30-Preview",
					"protocol": "https",
					"host": [
						"{{AZURE_SEARCH_SERVICE_NAME}}",
						"search",
						"windows",
						"net"
					],
					"path": [
						"indexes",
						""
					],
					"query": [
						{
							"key": "api-version",
							"value": "2021-04-30-Preview"
						}
					]
				}
			},
			"response": []
		},
		{
			"name": "Create Indexer",
			"request": {
				"method": "POST",
				"header": [
					{
						"key": "api-key",
						"value": "{{AZURE_SEARCH_ADMIN_KEY}}",
						"type": "text"
					},
					{
						"key": "Content-Type",
						"value": "application/json",
						"type": "text"
					}
				],
				"body": {
					"mode": "raw",
					"raw": "{\r\n    \"name\" : \"sharepoint-indexer\",\r\n    \"dataSourceName\" : \"sharepoint-datasource\",\r\n    \"targetIndexName\" : \"sharepoint-index\",\r\n    \"parameters\": {\r\n    \"batchSize\": null,\r\n    \"maxFailedItems\": null,\r\n    \"maxFailedItemsPerBatch\": null,\r\n    \"base64EncodeKeys\": null,\r\n    \"configuration\": {\r\n        \"indexedFileNameExtensions\" : \".pdf, .docx\",\r\n        \"excludedFileNameExtensions\" : \".png, .jpg\",\r\n        \"dataToExtract\": \"contentAndMetadata\"\r\n      }\r\n    },\r\n    \"schedule\" : { },\r\n    \"fieldMappings\" : [\r\n        { \r\n          \"sourceFieldName\" : \"metadata_spo_site_library_item_id\", \r\n          \"targetFieldName\" : \"id\", \r\n          \"mappingFunction\" : { \r\n            \"name\" : \"base64Encode\" \r\n          } \r\n         }\r\n    ]\r\n}"
				},
				"url": {
					"raw": "https://{{AZURE_SEARCH_SERVICE_NAME}}.search.windows.net/indexers?api-version=2021-04-30-Preview",
					"protocol": "https",
					"host": [
						"{{AZURE_SEARCH_SERVICE_NAME}}",
						"search",
						"windows",
						"net"
					],
					"path": [
						"indexers"
					],
					"query": [
						{
							"key": "api-version",
							"value": "2021-04-30-Preview"
						}
					]
				}
			},
			"response": []
		},
		{
			"name": "Get Indexer Status",
			"protocolProfileBehavior": {
				"disableBodyPruning": true
			},
			"request": {
				"method": "GET",
				"header": [
					{
						"key": "api-key",
						"value": "{{AZURE_SEARCH_ADMIN_KEY}}",
						"type": "text"
					},
					{
						"key": "Content-Type",
						"value": "application/json",
						"type": "text"
					}
				],
				"body": {
					"mode": "raw",
					"raw": "{\r\n    \"name\" : \"sharepoint-indexer\",\r\n    \"dataSourceName\" : \"sharepoint-datasource\",\r\n    \"targetIndexName\" : \"sharepoint-index\",\r\n    \"parameters\": {\r\n    \"batchSize\": null,\r\n    \"maxFailedItems\": null,\r\n    \"maxFailedItemsPerBatch\": null,\r\n    \"base64EncodeKeys\": null,\r\n    \"configuration\": {\r\n        \"indexedFileNameExtensions\" : \".pdf, .docx\",\r\n        \"excludedFileNameExtensions\" : \".png, .jpg\",\r\n        \"dataToExtract\": \"contentAndMetadata\"\r\n      }\r\n    },\r\n    \"schedule\" : { },\r\n    \"fieldMappings\" : [\r\n        { \r\n          \"sourceFieldName\" : \"metadata_spo_site_library_item_id\", \r\n          \"targetFieldName\" : \"id\", \r\n          \"mappingFunction\" : { \r\n            \"name\" : \"base64Encode\" \r\n          } \r\n         }\r\n    ]\r\n}"
				},
				"url": {
					"raw": "https://{{AZURE_SEARCH_SERVICE_NAME}}.search.windows.net/indexers/sharepoint-indexer/status?api-version=2021-04-30-Preview",
					"protocol": "https",
					"host": [
						"{{AZURE_SEARCH_SERVICE_NAME}}",
						"search",
						"windows",
						"net"
					],
					"path": [
						"indexers",
						"sharepoint-indexer",
						"status"
					],
					"query": [
						{
							"key": "api-version",
							"value": "2021-04-30-Preview"
						}
					]
				}
			},
			"response": []
		},
		{
			"name": "Run Indexer",
			"request": {
				"method": "POST",
				"header": [
					{
						"key": "api-key",
						"value": "{{AZURE_SEARCH_ADMIN_KEY}}",
						"type": "text"
					},
					{
						"key": "Content-Type",
						"value": "application/json",
						"type": "text"
					}
				],
				"body": {
					"mode": "raw",
					"raw": "{\r\n    \"name\" : \"sharepoint-indexer\",\r\n    \"dataSourceName\" : \"sharepoint-datasource\",\r\n    \"targetIndexName\" : \"sharepoint-index\",\r\n    \"parameters\": {\r\n    \"batchSize\": null,\r\n    \"maxFailedItems\": null,\r\n    \"maxFailedItemsPerBatch\": null,\r\n    \"base64EncodeKeys\": null,\r\n    \"configuration\": {\r\n        \"indexedFileNameExtensions\" : \".pdf, .docx\",\r\n        \"excludedFileNameExtensions\" : \".png, .jpg\",\r\n        \"dataToExtract\": \"contentAndMetadata\"\r\n      }\r\n    },\r\n    \"schedule\" : { },\r\n    \"fieldMappings\" : [\r\n        { \r\n          \"sourceFieldName\" : \"metadata_spo_site_library_item_id\", \r\n          \"targetFieldName\" : \"id\", \r\n          \"mappingFunction\" : { \r\n            \"name\" : \"base64Encode\" \r\n          } \r\n         }\r\n    ]\r\n}"
				},
				"url": {
					"raw": "https://{{AZURE_SEARCH_SERVICE_NAME}}.search.windows.net/indexers/sharepoint-indexer/run?api-version=2021-04-30-Preview",
					"protocol": "https",
					"host": [
						"{{AZURE_SEARCH_SERVICE_NAME}}",
						"search",
						"windows",
						"net"
					],
					"path": [
						"indexers",
						"sharepoint-indexer",
						"run"
					],
					"query": [
						{
							"key": "api-version",
							"value": "2021-04-30-Preview"
						}
					]
				}
			},
			"response": []
		}
	],
	"event": [
		{
			"listen": "prerequest",
			"script": {
				"type": "text/javascript",
				"exec": [
					""
				]
			}
		},
		{
			"listen": "test",
			"script": {
				"type": "text/javascript",
				"exec": [
					""
				]
			}
		}
	],
	"variable": [
		{
			"key": "AZURE_SEARCH_SERVICE_NAME",
			"value": "your_company-cognitive-search",
			"type": "string"
		},
		{
			"key": "AZURE_SEARCH_ADMIN_KEY",
			"value": "00Md1Lt1iWRiGOabz0cRNaJemnxq1cvnpQu0PReBUDAzSeAnDieq",
			"type": "string"
		},
		{
			"key": "AZURE_APPLICATION_ID",
			"value": "aaf72f62-20bf-4d5c-a181-4e4e4b1032a9",
			"type": "string"
		},
		{
			"key": "AZURE_APPLICATION_SECRET",
			"value": "Qak8Q~y367wCsvDCUv21gLoxzZO_NO-t_mMSicWy",
			"type": "string"
		},
		{
			"key": "AZURE_TENANT_ID",
			"value": "23bcd981-eaa6-475f-aac3-3afca0c35261",
			"type": "string"
		},
		{
			"key": "SHAREPOINT_SITE_URL",
			"value": "https://contoso.sharepoint.com/sites/Teailspin-Records-CognitiveSearchDemo",
			"type": "string"
		},
		{
			"key": "SHAREPOINT_LIBRARY_URL",
			"value": "https://contoso.sharepoint.com/sites/Teailspin-Records-CognitiveSearchDemo/Shared Documents/Forms/AllItems.aspx",
			"type": "string"
		},
		{
			"key": "SHAREPOINT_ADDITIONAL_COLUMNS",
			"value": "Title,Year,DocumentType,Critical,LastReviewed,DocumentOwner,AnnualCosts,FullFilePath",
			"type": "string"
		}
	]
}

Hello @Zerg00s
I have followed this article and video you have shared, The content is really good, thanks for being so much specific.
However I have one issue while searching, the custom columns are coming up as null for me.
Error Image with custom columns as null https://spigniter-my.sharepoint.com/:i:/g/personal/msadmin_signited_com/EfndAwHAeUtPgap5U35wtDEBxLcbtZS3ViMva3qL77mvKQ?e=9vcQYi
actual data which custom columns having some values https://spigniter-my.sharepoint.com/:i:/g/personal/msadmin_signited_com/EaeRkW-NyPdElEBYiZ6tEzYBbs8OwPo3R4EwuZK4XQb8xw?e=MGu3PT

Do you have idea, what went wrong for me?

Thanks in advance.

Hi @DevangZala. My best guess is that you need to specify SHAREPOINT_ADDITIONAL_COLUMNS or additionalColumns

https://learn.microsoft.com/en-us/azure/search/search-howto-index-sharepoint-online

additionalColumns

Index columns from the document library. The value is a comma-separated list of column names you want to index. Use a double backslash to escape semicolons and commas in column names:

Example 1 (additionalColumns=MyCustomColumn,MyCustomColumn2):

"container" : { "name" : "useQuery", "query" : "includeLibrary=https://mycompany.sharepoint.com/mysite/MyDocumentLibrary;additionalColumns=MyCustomColumn,MyCustomColumn2" }

Example 2 (escape characters using double backslash):

"container" : { "name" : "useQuery", "query" : "includeLibrary=https://mycompany.sharepoint.com/teams/mysite/MyDocumentLibrary/Forms/AllItems.aspx;additionalColumns=MyCustomColumnWith\\,,MyCustomColumnWith\\;" }

This needs to be done in the SharePoint DataSource creation:
https://{{AZURE_SEARCH_SERVICE_NAME}}.search.windows.net/datasources?api-version=2021-04-30-Preview

Body

{
    "name": "sharepoint-datasource-demo2",
    "type": "sharepoint",
    "credentials": {
        "connectionString": "SharePointOnlineEndpoint={{SHAREPOINT_SITE_URL}};ApplicationId={{AZURE_APPLICATION_ID}};ApplicationSecret={{AZURE_APPLICATION_SECRET}};TenantId={{AZURE_TENANT_ID}}"
    },
    "container": {
        "name": "defaultSiteLibrary",
        "query":"includeLibrary={{SHAREPOINT_LIBRARY_URL}};additionalColumns={{SHAREPOINT_ADDITIONAL_COLUMNS}}"

    }
}

Hello @Zerg00s ,
Thanks a lot for detailed answer, When I verified my settings, I can see that I have followed the same structure, off course credit goes to your video where you have explained the things in details.

Just that you can take a look at my approach, I have created a step by step guide of actions to make the bot working, please take a look, and if you can find any missing links or something wrong, i would be very happy to correct them and see the things working for me
Azure AI Search Configuration step by step.docx

Note: Step2 of the document - I have tried

{ "name": "CustomFilePath", "type": "Edm.String", "searchable": true,"retrievable": true },
{ "name": "DocumentType", "type": "Edm.String", "searchable": true,"retrievable": true }

and

{ "name": "CustomFilePath", "type": "Edm.String", "searchable": true },
{ "name": "DocumentType", "type": "Edm.String", "searchable": true }

Thanks in advance.