Copilot Studio: Use SharePoint Lists as a Knowledge Source

On this page

Microsoft Copilot Studio supports SharePoint as a knowledge source, but SharePoint lists only in a very limited way: a list can only be loaded as a whole. The workaround in this tutorial is to give the agent two agent flow tools. The first reads the list's internal column names from the SharePoint REST API. The second runs an OData query, written by the agent, that returns only the list items and columns needed to answer the user's question.

Why not just load the whole list? If your list has thousands of items, the agent loads all of them into its context, which will most probably lead to poor results. Or worse, if the list is too big, Copilot Studio truncates it so it doesn't overload the agent's context, and you lose a lot of information that way.

In the steps below, I'll show you how to build both agent flows, connect them to a Copilot Studio agent, and test that the agent answers questions from the list's content.

Prerequisites

To follow along, you only need a SharePoint list. There is really not much else to prepare.

For this guide, I prepared a machine alarms list with the most common alarms for a type of machine and their solutions.

The machine_alarms_1000 list with alarm ID, alarm text, machine part and solution

Tutorial: Using SharePoint lists as knowledge sources in Copilot Studio agents

Before the actual steps, here is the high-level idea. SharePoint lists come with an API-driven query and filter mechanism that lets you retrieve specific items of a list. More technically, SharePoint supports OData query parameters to filter and query list items. To fetch a subset of a list, all we need is:

1. The list's ID or name

2. The SharePoint site ID the list is located in

3. The list's internal column IDs

With these three pieces of information, an HTTP request in a Copilot Studio agent flow can retrieve specific items of the list and pass them to the agent as knowledge.

There is one catch: guessing the internal column IDs of a list is quite tricky, and if the list ever changes, you'd have to adjust all your agents by hand. Luckily, there is a neat trick around this. The /fields endpoint of the SharePoint API returns all fields of a list, including their internal IDs, and the agent can use that result to build the actual data query.

Two HTTP requests: fetch the list fields, then fetch filtered list items with OData

As the diagram shows, we use two HTTP requests:

1. The first request retrieves the list's fields and their internal IDs.
2. The second request uses these field IDs to fetch the actual list items, filtered by what the agent needs.

This lets the agent filter dynamically and retrieve only the rows and columns it needs.

One more thing: we don't run both HTTP requests in one flow. Instead, we create two tools:

• The first tool retrieves the field IDs (column IDs).
• The second tool uses them to fetch the actual data.

Why two tools instead of one? Because then the agent can change the query of the second tool if it doesn't get any results. Say it wrote a wrong filter query: it can simply rerun the second tool with a different one.

Creating a tool to fetch SharePoint list column internal names and ids

Let's set up the first tool.

1. Create a new agent and enter the title, description and agent instructions. You can follow the screenshot below.

   Creating a new agent in Copilot Studio

2. Add a tool and select Agent Flow.

   Selecting Agent Flow as the new tool type

3. In the Agent Flow Designer, click on the first card and add a text input:

   Add Text Inputs (note: you only need the list-name input)

   • list-name: the name of the SharePoint list we want to query. If you have only one list, you can skip this input and hardcode the list name in the HTTP requests below. I'd make it configurable though, as that makes the agent more flexible and reusable.

   The screenshot also shows a second input, natural-language-query. The flow never uses it, so you can skip it. It still shows up as a tool input in the permission screenshot in the testing section, because my test flow had it.

4. Click the + icon below and select Send HTTP Request to SharePoint. Click Sign In and authenticate with your Microsoft 365 account.

5. In this HTTP request card, you define 3 things:

   • The SharePoint site address
   • The request method
   • The endpoint URI

   For the site address, use the dropdown to select the site where your list is located. For the method, select GET. For the endpoint URI, enter the following:

    _api/web/lists/getbytitle('@{triggerBody()?['text']}')/fields?$select=Title,InternalName,TypeAsString&$filter=CanBeDeleted eq true or InternalName eq 'ID'
   

   Note: text refers to the text input we created in step 3, which contains the list's name. If you skipped that input, hardcode the list name in the URI instead:

    _api/web/lists/getbytitle('My List Name')/fields?$select=Title,InternalName,TypeAsString&$filter=CanBeDeleted eq true or InternalName eq 'ID'
   

   If you add more text inputs, the second one is text_1, and so on.

   This first request retrieves all fields of the specified SharePoint list. The response looks like this:

   1{
   2  "d": {
   3    "results": [
   4      {
   5        "__metadata": {
   6          "id": "https://pondhousedataog.sharepoint.com/_api/Web/Lists(guid'dd00d367-461f-402a-b954-6c1127917e3c')/Fields(guid'286a5897-dcd7-41a3-a6bb-e9943e9f339c')",
   7          "uri": "https://pondhousedataog.sharepoint.com/_api/Web/Lists(guid'dd00d367-461f-402a-b954-6c1127917e3c')/Fields(guid'286a5897-dcd7-41a3-a6bb-e9943e9f339c')",
   8          "type": "SP.FieldText"
   9        },
   10        "InternalName": "field_1",
   11        "Title": "Alarm Text",
   12        "TypeAsString": "Text"
   13      },
   14      {
   15        "__metadata": {
   16          "id": "https://pondhousedataog.sharepoint.com/_api/Web/Lists(guid'dd00d367-461f-402a-b954-6c1127917e3c')/Fields(guid'3ff75d29-d0f9-439f-9246-46dac677bd05')",
   17          "uri": "https://pondhousedataog.sharepoint.com/_api/Web/Lists(guid'dd00d367-461f-402a-b954-6c1127917e3c')/Fields(guid'3ff75d29-d0f9-439f-9246-46dac677bd05')",
   18          "type": "SP.FieldText"
   19        },
   20        "InternalName": "field_2",
   21        "Title": "Machine Part",
   22        "TypeAsString": "Text"
   23      },
   24      {
   25        "__metadata": {
   26          "id": "https://pondhousedataog.sharepoint.com/_api/Web/Lists(guid'dd00d367-461f-402a-b954-6c1127917e3c')/Fields(guid'9c1fc378-c5df-43d7-99a0-8088599862db')",
   27          "uri": "https://pondhousedataog.sharepoint.com/_api/Web/Lists(guid'dd00d367-461f-402a-b954-6c1127917e3c')/Fields(guid'9c1fc378-c5df-43d7-99a0-8088599862db')",
   28          "type": "SP.FieldText"
   29        },
   30        "InternalName": "field_3",
   31        "Title": "Solution",
   32        "TypeAsString": "Text"
   33      }
   34    ]
   35  }
   36}
   

6. Next, parse the internal names from the response. This is a bit annoying, but not too bad. Below the HTTP request, add a Parse JSON action.

   Paste the JSON from step 5 as the sample to generate the schema. You can use my example response no matter what columns your list has, because all responses have the same structure.

   Generating the Parse JSON schema from a sample payload

   Click Done. The JSON parsing card should now look like this:

   The configured Parse JSON card

7. The response is now a structured JSON object. We only want the data we need: the internal column names and their display names. For that, add a Select action below.

   Click the flash icon and select Body results from the Parse JSON action.

   Selecting Body results from the Parse JSON dynamic content

   Then in the Map section, create three new fields:

   • InternalName: Map this to Body internalName from the dynamic content
   • Title: Map this to Body title from the dynamic content (click on see more to find it)
   • Type: Map this to Body type from the dynamic content

   Mapping internal name, title and type in the Select action

   Your select card should now look like this:

   The finished Select card

   To show where we are, this is the output of the select card:

   1{
   2  "body": [
   3    {
   4      "InternalName": "field_1",
   5      "Title": "https://pondhousedataog.sharepoint.com/_api/Web/Lists(guid'dd00d367-461f-402a-b954-6c1127917e3c')/Fields(guid'286a5897-dcd7-41a3-a6bb-e9943e9f339c')",
   6      "Type": "SP.FieldText"
   7    },
   8    {
   9      "InternalName": "field_2",
   10      "Title": "https://pondhousedataog.sharepoint.com/_api/Web/Lists(guid'dd00d367-461f-402a-b954-6c1127917e3c')/Fields(guid'3ff75d29-d0f9-439f-9246-46dac677bd05')",
   11      "Type": "SP.FieldText"
   12    },
   13    {
   14      "InternalName": "field_3",
   15      "Title": "https://pondhousedataog.sharepoint.com/_api/Web/Lists(guid'dd00d367-461f-402a-b954-6c1127917e3c')/Fields(guid'9c1fc378-c5df-43d7-99a0-8088599862db')",
   16      "Type": "SP.FieldText"
   17    }
   18  ]
   19}
   

   We strip away everything we don't need and keep three values per list column.

   When an agent calls this flow, this is exactly the information it gets.

8. Finally, fill out the Respond to the Agent card. Click Add output, select Text and set:

   • Name: list-field-ids

   • Description: Internal SharePoint list field ids, use to find which column

   • Value: Map this to Output from the Select card

     The list-field-ids output in the Respond to the Agent card

Now publish your agent flow. Optionally, hit "Test" to run the flow and see the outputs of the individual cards. If you click Show raw inputs on the final card of a test run, you should see something like this:

Test run showing the list-field-ids returned to the agent

Go to the Tools section of your agent and give this tool a descriptive name and description.

• Name: Sharepoint list alarms - fetch ids
• Description: Use this tool to fetch the internal column ids of the SharePoint list, which are required to query the list items. The output will be a list of column names, their respective internal ids and types. Follow up this tool with the 'Sharepoint list alarms - fetch data' tool.

Creating a tool to fetch filtered list items from a SharePoint list

With the internal column names available, the second tool fetches the actual pre-filtered list items.

1. Go back to your agent, add another tool and again select Agent Flow.

2. In the Agent Flow Designer, add one input:

   • odata-rest-api-query: The OData query which we'll use to filter the list items. Use OData REST API query, starting with '_api' as description.

   The odata-rest-api-query input of the second agent flow

   Note: Where does the odata-rest-api-query come from? The AI agent generates it, based on instructions we'll add to the tool description later. That's also why this tool doesn't need the field IDs from the first tool as input: the agent sees all tool results and can use them directly.

3. Add a Send HTTP Request to SharePoint action, just like in the first tool. Again, authenticate if you haven't already.

   Use the same site address and method as in the first tool. For the endpoint URI, select the odata-rest-api-query input from the dynamic content.

   HTTP request card using the odata-rest-api-query input as URI

4. Finally, connect the output of the HTTP action to the Respond to the Agent card. As in the first tool, add a new output of type Text with these settings:

   • Name: list-data
   • Description: The retrieved list items based on the provided OData query
   • Value: Map this to Body from the HTTP card

Now publish this agent flow as well.

Go to your agent's Tools section and give this tool a descriptive name and description as well.

• Name: Sharepoint list alarms - fetch data

• Description: Use this tool to fetch specific list items from the SharePoint list. Make sure to use the internal column names retrieved from the 'Sharepoint list alarms - fetch ids' tool when creating your OData query, so always first run the 'fetch ids' tool to get the column names and their respective internal names. This tool requires the odata-rest-api-query input, which should be an OData REST API query starting with _api.

  Feel free to run this tool multiple times with different OData filters, if you don't get the information you need with the first query.

  Do not filter too strictly (eg. don't use 'eq' for text columns), as you might filter out relevant information. Always prefer broader and more fuzzy filters.

In the Inputs section of the tool, click Customize and add this description:

OData REST API query, starting with '\_api'.

You can create such a request as follows:

1. Start the URI with \_api/web/lists/getbytitle('machine_alarms_1000').
   Without leading '/'.
1. Append an ODATA filter query which is used to answer the user's
   question. This should pre-filter the sharepoint list according the
   users needs.
1. in a $select url parameter, include the sharepoint list columns as
   defined below. Make sure to include all columns which might be
   required to answer the users question and use the correct data types.
1. Return a single line of text being the sharepoint REST URI.

  Do not filter too strictly (eg. don't use 'eq' for text columns), as you
  might filter out relevant information. Always prefer boader and more
  fuzzy filters.

  The Alarm number is the ID column.

Input description for the fetch data tool in Copilot Studio

Double-check that your agent now has both tools configured:

The agent's tools overview with both SharePoint list tools

Testing the SharePoint list agent

To test the agent, hit the "Test" button in the top-left corner of the agent overview page. This opens the testing pane, a chat interface where you can talk to your agent and see how it responds.

Ask a question that you know your SharePoint list can answer. For extra points, pick one that only list items in the lower half of your list can answer. That way you see whether the filtering actually works, because otherwise the agent could cheat and simply select the whole list.

The first time the agent wants to use a tool during testing, it asks for permission to connect to SharePoint. Click Allow.

Copilot Studio asking permission to connect to SharePoint

The results are what I'd hoped for. The agent first used the Sharepoint list alarms - fetch ids tool to retrieve the internal column names. Then it used the Sharepoint list alarms - fetch data tool to retrieve the relevant list items with a generated OData query. In the screenshot below, the question is about alarm ALM-0294: the agent filtered the list with substringof on the three text columns plus the ID, limited the result to 10 items, and answered with troubleshooting steps for that alarm.

Agent test: both tools ran, and the agent answered from the list item

If you want to compare this approach with building agents in code, see our guide to Microsoft's Agent Framework.

The two tools work well for structured list items. For knowledge that lives in documents instead, a search index is the better fit, as in our Azure AI Search RAG tutorial. If you are planning an agent on your own SharePoint data and want a second opinion or a prototype, have a look at our AI consulting services.

Interested in building high-quality AI agent systems?

We prepared a comprehensive guide based on cutting-edge research for how to build robust, reliable AI agent systems that actually work in production. This guide covers:

• Understanding the 14 systematic failure modes in multi-agent systems
• Evidence-based best practices for agent design
• Structured communication protocols and verification mechanisms

Further reading

• Orchestrating complex tasks with Microsoft Agent Framework workflows
• How we moved our company documents from Confluence to a self-maintaining LLM wiki
• Integrating enterprise knowledge with LLMs

About the author

Andreas Nigg

Co-Founder, Pondhouse Data

Andreas has spent more than eight years building data applications. At Pondhouse Data he designs and ships AI systems and integrates them into complex, real-world environments.

Talk to Andreas about your projectFollow on LinkedIn