Copilot Studio SharePoint List Knowledge Workaround

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:

And give the Agent these instructions:

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.

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

And include these values in the headers.

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
  
  

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.

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']

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:

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.

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.