Copilot Studio SharePoint List Knowledge Workaround

Imported reference from Matthew Devaney's blog for learning purposes. Original: https://www.matthewdevaney.com/copilot-studio-sharepoint-list-knowledge-workaround/. Author: Matthew Devaney.

Copilot Studio SharePoint List Knowledge Workaround Posted by - Matthew Devaney on - April 20, 2025 11 Comments

Copilot Studio cannot use a SharePoint list as knowledge but there is a workaround with Agent actions that delivers excellent results. The Agent can call a flow to get information about the SharePoint list columns and data types. Then it can feed it into an AI Prompt along with the user’s question to craft a SharePoint Rest API filter query to get the relevant list items. Once the list items are returned the Copilot Studio Agent uses generative AI to craft a message and send the answer to the user. Table of Contents

Introduction: The SharePoint Knowledge Copilot Studio Agent Create A New Agent In Copilot Studio

Add A New Power Automate Flow Action To The Agent Setup The SharePoint List

Get The SharePoint List Columns Information

Parse The SharePoint List Field Information

Build An AI Prompt To Generate A SharePoint Filter Query Add A Prompt Action To The Flow

Get The SharePoint List Items With An HTTP Request

Send SharePoint List Items To The Copilot Studio Agent

Describe The Copilot Studio Action To Get SharePoint List Items Define The Copilot Studio Action Inputs

Test The Copilot Studio Agent With SharePoint List Knowledge Full Power Automate Flow Screenshot

Introduction: The SharePoint Knowledge Copilot Studio Agent

A Copilot Studio Agent is used by a team of property managers to get information about buildings. The building information is stored in a SharePoint list.

The Copilot Studio Agent can answer a broad range of questions such as:

Show me all of the offices in the state of Illinois

Give me a list of all offices with a headcount higher than 200

Tell me which offices have a lease end date in 2026 Create A New Agent In Copilot Studio

Open Copilot Studio and create a new agent named SharePoint List Query Agent . Turn on Generative Orchestration . Write this description for the Agent:

The Agent should return a list of matching SharePoint list items from a specified SharePoint list and filter the results per the User’s specifications. The results should be returned as a bulleted list of items. And give the Agent these instructions:

The Agent will ask the User to choose a SharePoint list. When the User responds the Agent will verify that the SharePoint list exists. If the SharePoint list exists the Agent will ask user to input search criteria as natural language. The natural language search criteria will be converted into a SharePoint REST API filter query by the Agent. Any results matching the SharePoint REST API filter query will be returned to the User in chat.

Add A New Power Automate Flow Action To The Agent

The Copilot Studio Agent will use an action to get the SharePoint list items. Go to the actions menu and add an action.

Select New Power Automate flow from the new action button dropdown.

The Power Automate flow is created with a Copilot trigger. We will setup the flow to be re-usable for other SharePoint lists. Add the following inputs to the trigger: SP Site URL SP List Name User Query Setup The SharePoint List

The Copilot Studio Agent will use a Power Automate flow to read items from a SharePoint list. Create a new SharePoint list named Office Locations and use this sample data. StreetAddress City State ZipCode Headcount LeaseEndDate 100 1st Street North Albany New York 90013-1012 250 3/1/2026 40 Wolseley Avenue Schaumberg Illinois 90247-9096 25 11/1/2029 23 River Road Fargo North Dakota 91010-4804 10 1/1/2030 300 State Street Bronx New York 90023-4434 375 8/1/2027 10 Aspen Road Chicago Illinois 94039-2032 100 12/1/2026 40 Park Place Newark New Jersey 91000-1003 80 6/1/2028 3rd Avenue North Minneapolis Minnesota 98430-2392 425 2/1/2029 456 Tudor Crescent Ithica New York 92234-0592 50 5/1/2030 78 Magnolia Drive Boston Massachusetts 91111-1111 60 7/1/2027 Oak Lane Austin Texas 95050-5050 175 10/1/2028

The SharePoint list will look like this once it has been populated with data.

Get The SharePoint List Columns Information

To generate a SharePoint REST API request to get list items the Agent needs to know the SharePoint list column names and data types. We can use the SharePoint REST API to get this information.

Add a SharePoint – Send An HTTP Request To SharePoint action to the flow, supply the SharePoint Site URL from the flow trigger, and use the GET method.

Use this code in the URI field to return the SharePoint list fields information.

_api/web/lists/getbytitle( '@{triggerBody()?[' text_1 ']}' )/fields?$select=Title,InternalName,TypeAsString&$filter=CanBeDeleted eq true Code language: PHP ( php )

And include these values in the headers. Key Value Accept application/json

The SharePoint HTTP action will return a result that looks like this:

Parse The SharePoint List Field Information

The SharePoint List field information returned by the SharePoint HTTP action returns more columns than are necessary for our Agent to craft the filter query.

We will remove the unneeded columns by first passing the SharePoint HTTP action values into a Parse JSON action. Then we use the Select action to keep only the Title, InternalName and TypeAsString columns. Finally, we pass the output of the Select action into a Compose action to convert the data type from array to text.

Build An AI Prompt To Generate A SharePoint Filter Query

We want to use AI Prompts to create a SharePoint REST API request including a filter query . When we supply the prompt output to a SharePoint HTTP action it will get the list items we want.

Go to Power Automate and create a new AI Prompt.

Title the AI Prompt Generate URI To Get List Items SharePoint REST API .

Give the AI Prompt these instructions and add these inputs: SharePointListName (‘Office Locations’)

UserFilterQuery (‘Tell me which office leases end in the year 2026’)

SharePointListColumns – get this from the Compose action output in Power Automate Objective:

Generate a valid URI for use in the Power Automate action “Send An HTTP request to SharePoint.” Instructions:

1. Base URI: Start with _api/web/lists/getbytitle(‘SharePointListName’). Do not include a leading ‘/’.

2. OData Filter Query: Append an OData filter query based on the user’s input, referred to as UserFilterQuery. This should filter the list items according to the user’s criteria.

3. Select Query Options: Include the SharePointListColumns in a $select query option, ensuring all columns are specified using the correct SharePoint list column names and data types.

4. Output: Return a single line of text containing the complete, valid SharePoint REST API URI. Requirements:

– Use valid SharePoint REST API syntax. – Do not include additional text or explanations in the output.

– Ensure the URI is properly encoded if necessary. – Compare columns in the filter query to data of the same type – Do not use the year function, month function or day function to query dates in the OData filter. Use a date range comparison instead. Required Information:

SharePointListName: [SharePointListName] UserFilterQuery : [UserFilterQuery] SharePointListColumns: [SharePointListColumns]

Test the AI Prompt with GPT-4o. The output should be a valid SharePoint REST API request. Add A Prompt Action To The Flow

Go back into the flow editor and add a new AI Builder – Create Text With GPT Using A Prompt action. Select the Generate URI To Get List Items SharePoint REST API prompt. Add dynamic values to the prompt input fields as shown below.

Get The SharePoint List Items With An HTTP Request

The AI Prompt will output a URI which we can then use in a SharePoint – Send An HTTP Request To SharePoint action. Use the SP Site URL as the Site Address and use the GET method.

Add this Accept header key-value pair to return the list items without any metadata. Key Value Accept application/json;odata=nometadata

Send SharePoint List Items To The Copilot Studio Agent

Once the SharePoint list items are fetched by Power Automate we want to send them back to our Copilot Studio Agent. Use a Compose action to convert the array of list item to text. Then return the text to the Copilot Studio Agent in a Respond to Copilot action.

Use this code in the Inputs property of the Compose action.

body( 'Send_an_HTTP_request_to_SharePoint:_Get_List_Items' )?[ 'value' ] Code language: JavaScript ( javascript )

Describe The Copilot Studio Action To Get SharePoint List Items

Copilot relies upon display names and descriptions to know when to use an action. Give the flow the display name Get Matching SharePoint List Items For Office Locations .

Then write this description for the action:

When the user asks a question about “Office Locations” use this action to return a filtered set of SharePoint list items. Define The Copilot Studio Action Inputs

We must configure the Copilot Studio action inputs to let the Agent know what values are needed for the flow. Set the values as follows but use your own site URL:

SP Site URL: https://matthewdevaney.sharepoint.com/sites/MatthewDevaneyBlog SP List Name: Office Locations User Query: LastMessage.Text

Test The Copilot Studio Agent With SharePoint List Knowledge

Save the Copilot Studio Agent then test it with the following prompts. The Copilot Studio Agent will give the answer using SharePoint List knowledge.

Show me all of the offices in the state of Illinois

Give me a list of all offices with a headcount higher than 200

Tell me which offices have a lease end date in 2026 Full Power Automate Flow Screenshot

Here is a screenshot of the full Power Automate flow used as a Copilot Studio Agent action. Did You Enjoy This Article? 😺

Subscribe to get new Copilot Studio articles sent to your inbox each week for FREE Enter your email address Sign Me Up Questions?

If you have any questions or feedback about Copilot Studio SharePoint List Knowledge Workaround please leave a message in the comments section below. You can post using your email address and are not required to create an account to join the discussion. Matthew Devaney Copilot Studio Video

Video: Copilot Studio – Add Human Review to Your AI Agents