Query SharePoint Lists in Copilot Studio via Custom Actions

If you’ve ever wanted to put a SharePoint list behind your Copilot Studio agent, you’ve probably seen that lists aren’t available as a built‑in knowledge source. The platform indexes SharePoint pages and documents, but not the rows inside a list. However, you can still feed live list data to your agent by creating a custom action that calls Power Automate, uses AI Builder to turn a natural language question into a SharePoint REST API filter, and returns only the matching items. This article walks through the workaround using a simple help‑desk scenario.

Scenario Overview

Imagine a SharePoint list called Support Requests with these columns:

Your Copilot agent needs to answer questions like:

• “Show me all high‑priority requests that are still unresolved.”
• “What was logged today?”
• “Find requests assigned to Lisa.”

The workaround uses three parts:

1. Copilot Studio action – triggers a Power Automate flow.
2. Power Automate flow – retrieves the list schema, builds a REST API filter via AI Builder, and executes the query.
3. Response – the flow returns items as JSON, and the agent turns them into a natural language answer.

Building the Power Automate Flow

The flow starts with the When Copilot Studio calls a flow trigger. Configure two inputs:

• UserQuestion (text) – the full question from the chat
• ListName (text) – the name of the SharePoint list (e.g., “SupportRequests”)

1. Get List Columns

Use the Send an HTTP request to SharePoint action to retrieve the field schema:

_api/web/lists/getbytitle('@{triggerOutputs()?['body/ListName']}')/fields?$select=Title,FieldTypeKind&$filter=Hidden eq false

Set the method to GET and add headers: Accept: application/json.

Parse the response to extract Title and FieldTypeKind, then compose a plain‑text description of the schema, for example:

RequestId (Number), Title (Text), Priority (Choice), Status (Choice), AssignedTo (Person), Created (DateTime), Description (Text)

2. Generate the REST API Filter with AI Builder

Add the Create text with GPT action (AI Builder). Use a prompt like this:

You are a SharePoint REST API query generator.
Given the following list columns and types:
[SCHEMA]

User request:
[QUESTION]

Generate only the $filter clause that returns the matching items.
For example: Status eq 'Resolved' and Priority eq 'High'.
Do not include any other text.

Replace [SCHEMA] and [QUESTION] with the dynamic values from the previous steps.

3. Retrieve Items Using the Generated Filter

Make another HTTP request to SharePoint to fetch the items:

_api/web/lists/getbytitle('@{triggerOutputs()?['body/ListName']}')/items?$top=20&$filter=@{outputs('Compose_filter')}

Again set method to GET and include the Accept: application/json header. The $top value prevents the response from becoming too large. Consider adding $select=* or a specific list of columns to control what is returned.

4. Return Results to Copilot

The flow outputs a JSON array of the items. Use the Respond to Copilot Studio action to pass the array as a table property (or as a string). Once received, Copilot Studio can use a Generative AI message to summarise the data.

Configuring Copilot Studio

Create a new topic in Copilot Studio and add trigger phrases like “support requests”, “get tickets”, “list requests”, etc.

Add an Action node that calls the flow you built, mapping the user’s entire utterance to the UserQuestion input and the list name to ListName. After the action, use a Message node with generative AI mode.

Security and Performance

• Permissions – The flow runs under the connection owner’s identity. Make sure that identity has at least read access to the list.
• Filter validation – The AI‑generated filter may be malformed. Add a condition in the flow to check if the output starts with $filter. If not, return a fallback message.
• Pagination – For larger lists, implement paging by using $top and $skip or include odata.nextLink handling.
• Column indexing – Index columns used in common filters (e.g., Status, Priority) to improve query speed.
• Consumption – AI Builder calls consume credits. Monitor usage if your agent handles many conversations.

Common Mistakes and Troubleshooting

• Missing Accept header – The HTTP action can fail if the JSON header isn’t set.
• Filter with apostrophes – If the user asks for a name like “O’Brien”, the generated filter may break. Consider escape logic or instruct the AI to double single quotes.
• Response too large – Returning more than a few hundred items can cause timeouts. Always limit rows with $top.
• Copilot not using the data – Make sure the topic’s generative answer includes the flow response. You may need to adjust the “Conversation boost” settings.

Final Recommendation

This approach gives you a powerful way to inject structured list data into Copilot Studio without waiting for native list support. It works best for direct search and filter questions. For aggregations or complex joins, consider building a custom connector or using Azure OpenAI directly. The flow gives you complete control over the query, while AI Builder handles the translation from natural language to REST API syntax. Use it to fill the gap today, and move to a first‑party solution when available.

References

• Matthew Devaney, “Insane Copilot Studio SharePoint List Knowledge Workaround!!” – the original inspiration for this technique
• Microsoft Learn, “Use SharePoint REST API $filter” – official documentation
• Microsoft Learn, “Create text with GPT in AI Builder” – learn how to configure the AI Builder action