Using SharePoint Lists as Knowledge Sources in Microsoft Copilot Studio Agents

As of today, Microsoft Copilot Studio allows you to connect various knowledge sources which the agents can then retrieve information from and reason over. One of the sources is SharePoint. However, one very prominent SharePoint feature is not yet supported as a knowledge source: SharePoint Lists - or at least its only supported in a very limited way. One of the most glaring issues is that SharePoint lists can only be loaded as whole, meaning if you have a list with thousands of items, the agent will load all of them into context, which most probably will lead to poor results. Or worse, if the list is too big, Copilot Studio intelligently truncates the list to not overload the agents context, but obviously you loose a lot of information that way.

That's where this tutorial comes in. I'll show you how to use a Copilot Studio agent flow to query the SharePoint list, and then feed the retrieved information into the agent's context, allowing him to answer questions based on the list's content.

To follow along with this tutorial, you'll need a SharePoint list prepared. There is really not much else to prepare.

For the sake of this guide, I prepared a machine alarms list, containing the most common alarms for a type of machine as well as their respective solutions.

My SharePoint example list

Before we dive into the actual steps, let's quickly go over the high-level idea of how this works. In general, SharePoint lists internally use an API-driven query and filter mechanism, allowing you to retrieve specific items of a list. For the more technical inclined among our readers, SharePoint allows to use OData query parameters to filter and query list items. All we need to fetch a subset of a list, are:

1. The list's ID or name

2. The SharePoint site ID the list is located in

3. The internal lists column ids

With these three pieces of information, we can use an HTTP request in a Copilot Studio agent flow to retrieve specific items of the list, and use this as knowledge source for the agent.

Sounds straight-forward enough, but there is one catch: Guessing the internal column ids of a list is quite tricky, and also if the list ever changes you need to manually adjust all your agents - which is obviously not ideal. Luckily, there is a neat trick to get around this: You can use the /fields endpoint of the SharePoint API, which allows you to retrieve all the fields of a list, including their internal ids, and then use the results of this request to create the final, actual data fetching query.

SharePoint list fetching strategy

So, as you can see in the image above, we will entertain two http requests:

1. The first request will be used to retrieve the lists fields and their respective internal ids
2. The second request will then use the retrieved field ids to fetch the actual list items, filtered by what the agent needs.

This is actually quite neat, as this allows the agent to dynamically filter and retrieve only the information it needs - it can filter out specific columns and rows.

One more thing: We don't run both http requests as one workflow, but we'll create two tools:

• The first tool which retrieves the field ids (column ids)
• The second tool which uses these to fetch the actual data

Why would we use two tools instead of one you ask? Because than the agent can decide to change the search query of the second tool if they don't get any results (let's say they created a wrong filter query, then they can rerun with a different query).

So, enough talk, let's dive into the actual steps of setting this up.

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

   Create your Agent

2. Add a tool and select Agent Flow Select Agent Flow

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

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

   • list-name: This will be 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 following http requests. I'd make it configurable though, as it allows for more flexibility and reuse of the agent.

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

5. This HTTP request card now allows you to 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 first text input we created in step 3, which is supposed to contain the list's name. If you skipped creating this input, you can also just hardcode the list name in the URI, like this:

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

   Note that if you want to use the second text input, you need to use text_1 and so on.

   This first request will now retrieve all the fields of the specified SharePoint list, and might look as follows:

   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. Now we need to parse the internal names from the response. This is a bit annoying, but not too bad. Below this HTTP request, add a new action Parse JSON.

   Copy the exact JSON from step 5 above (you can copy my example response above, no matter what columns you have. All the responses will look like mine, so simply copy/paste the above JSON).

   Create schema from sample

   Click on Done. You should now see the JSON parsing card as follows:

   JSON parsing card

7. Our response is now a structured JSON object and we can work with that. We want to only select the data we need, which ultimately are the internal column names and their respective display names. To do that, we add another action Select below.

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

   Select 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

   Map internal name and title

   Your select card should now look like this:

   Final select card

   Maybe to give you an idea where we are at, 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}
   

   As you can see, we strip away all the unnecessary information and just keep the three information we need - per list column.

   Now when we use this agent flow in an actual agent, the agent would also have exactly this information so far.

8. At the end, all we need to do is to fill out the Respond to the Agent card. Click Add output, select Text and there 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

     List field ids output

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

Fetch ids test

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

• 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.

Now that we have the internal column names, we can create a second tool which uses these to fetch the actual pre-filtered list items.

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

2. In the Agent Flow Designer, we need to 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.

   Tool 2 inputs

   Note: You might ask: where is the odata-rest-api-query supposed to come from? This will be generated by our AI agent, so we don't need to worry about that. We'll add the prompt for that in the tool description later on. That's also the reason why we don't need for example the list-ids of the previous tool as input - the agent is aware of any tool results and can simply use them.

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

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

   Tool 2 HTTP request setup

4. Finally, we can connect the outputs of the http action to the Respond to the Agent card. Just like in the previous tool, add a new output of type Text with the following 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 Agents Tools section and add a descriptive name and description for this tool.

• 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 boader and more fuzzy filters.

In the Inputs section of the tool, click Customize 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.

Fetch Data Tool configuration

Make sure to double-check that your agent now has these two tools configured:

Agent tools overview

For testing the agent, simply hit the "Test" button in the top-left corner of the agent overview page. This will open the testing pane, which is basically a chat interface where you can interact with your agent and see how it responds.

Add a question which you know can be answered with the information in your SharePoint list. For extra points, use a question which can only be answered by list items somewhere in the lower half of your list, to see of the filtering actually works - otherwise the agent could cheat and simply select the whole list.

During testing, and if the agent wants to use a tool for the first time, you are asked to allow connecting to SharePoind, so click Allow.

Connection attempt to SharePoint

The results are what I'd hope for: The agent first used the Sharepoint list alarms - fetch ids tool to retrieve the internal column names, and then used the Sharepoint list alarms - fetch data tool to retrieve the relevant list items based on the generated OData query. Finally, it was able to answer the user's question based on the retrieved list items.

Agent test results

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