Dynamic SharePoint List Queries for Copilot Studio Using AI Prompts

Your Copilot agent can handle documents and web content, but what about live data sitting in a SharePoint list? Out of the box Copilot Studio does not support SharePoint lists as a knowledge source. However, you can build a custom action that uses Power Automate and an AI Builder prompt to dynamically translate user questions into SharePoint REST API queries. The agent calls a flow, which fetches the list schema, generates a filter URI, retrieves matching rows, and returns the results to the chat. This article walks through the complete workaround using an HR employee directory example.

The Core Idea

Instead of trying to force a SharePoint list into the “knowledge” tab, we hand control to an orchestrating flow. The flow does three things:

1. Discovers the column names and data types of the target list.
2. Uses an AI Prompt to build a valid OData filter string based on the user’s natural‑language request.
3. Executes the filter against the SharePoint REST API and sends the results back to the agent.

The result is a conversational experience where users can ask “Show all employees in Engineering hired after 2021” or “List offices with headcount over 200” and get an instant, filtered answer.

Example Scenario: HR Employee Directory

An HR team stores employee records in a SharePoint list named EmployeeRecords. The list contains these columns:

You want a Copilot agent (called HR Assistant) that can answer queries like:

• “Who works in the Chicago office?”
• “Give me all employees in Sales with a salary above $80,000.”
• “Which employees started in 2022?”

The agent must dynamically filter the list – exactly what the workaround enables.

Step 1 – Prepare the SharePoint List

Create the EmployeeRecords list and populate it with sample data. The exact columns can vary; just make sure they mimic a real directory. Because the flow will read the list schema at runtime, you can reuse the same flow for any list – just tell the agent the list name.

Step 2 – Create the Copilot Agent

In Copilot Studio create a new agent with generative orchestration turned on. Give it a clear description and instructions so it knows when to call the flow.

• Name: HR Assistant
• Description: An agent that returns matching rows from a SharePoint EmployeeRecords list based on the user’s natural‑language query. Results are displayed as a bulleted list.
• Instructions: Ask the user which SharePoint list they want to query. Verify the list exists, then ask for their search criteria in natural language. Use the GetMatchingEmployees action to run the query and display the results.

The agent will rely on the action’s name and description to decide when to invoke it. We’ll define that action next.

Step 3 – Build the Power Automate Flow

From the agent’s Actions tab add a new action and select New Power Automate flow. The flow will have a Copilot trigger with three inputs:

• SiteUrl – the SharePoint site URL
• ListName – the target list name (e.g., “EmployeeRecords”)
• UserQuery – the natural‑language question from the user

3.1 – Get the List Columns

The agent needs to know the column names and data types to build an accurate filter. Add a Send an HTTP request to SharePoint action.

• Site Address: SiteUrl (from trigger)
• Method: GET
• Uri: _api/web/lists/getbytitle('@{triggerBody()?['text_2']}')/fields?$select=Title,InternalName,TypeAsString&$filter=CanBeDeleted eq true
• Headers:

{
"Accept": "application/json"
}

The response is an array of field objects. We only need the Title, InternalName, and TypeAsString properties.

3.2 – Clean the Field Information

Use a Parse JSON action with a schema that matches the response. Then use a Select action to keep only the three desired columns, and finish with a Compose action to turn the array into a formatted text block.

The output of the Compose action will look something like:

Title: FullName, InternalName: FullName, TypeAsString: Text
Title: Department, InternalName: Department, TypeAsString: Text
Title: HireDate, InternalName: HireDate, TypeAsString: DateTime
Title: BaseSalary, InternalName: BaseSalary, TypeAsString: Currency

This text is passed to the AI Prompt so the LLM knows the exact column names and types.

Step 4 – Create the AI Builder Prompt

In Power Automate go to AI Prompts (Preview) and create a new prompt. We’ll call it Generate SharePoint Filter URI.

Objective

Return a valid SharePoint REST API URI that includes a $filter clause and a $select clause, based on the user’s natural‑language query.

Inputs

Instructions (keep concise)

• Start the URI with _api/web/lists/getbytitle('SharePointListName')/items?
• Add a $select containing all column internal names from SharePointColumns.
• Append a $filter that implements the user’s request. Use proper OData syntax.
• For date filters, use range comparisons (datetime'2021-01-01T00:00:00Z'), not functions like year().
• Output only the URI – no extra text.

Test the prompt with GPT‑4o or GPT‑4o‑mini to verify it produces valid URIs.

Step 5 – Assemble the Flow

Back in the flow, add a Create text with GPT using a prompt action and select the prompt you just created. Map the dynamic values:

• SharePointListName: ListName (from trigger)
• UserFilterQuery: UserQuery (from trigger)
• SharePointColumns: output of the Compose action from step 3.2

Now add a second Send an HTTP request to SharePoint action to fetch the filtered items.

• Site Address: SiteUrl from trigger
• Method: GET
• Uri: output of the AI Prompt action

{
"Accept": "application/json;odata=nometadata"
}

Use odata=nometadata to keep the payload small and easy to parse.

Convert Items to Text and Respond

Add a Compose action with the expression:

outputs('Send_an_HTTP_request_to_SharePoint_-_Get_Items')?['body/value']

Finally, add a Respond to Copilot action and pass the output of the Compose as the response. Set a friendly description so the agent knows what to do with the data.

Step 6 – Configure the Action in Copilot Studio

When you save the flow, Copilot Studio automatically registers it as an action. Adjust the action’s display name and description – Copilot uses these to decide when to call it.

• Display Name: Get Matching List Items
• Description: Retrieves rows from a SharePoint list by generating a REST API filter from the user's natural language query.

Test the agent by sending a sample question. The flow should fire, and the returned items should appear in the chat as a bulleted list.

Security and Performance Considerations

• Permissions: The flow runs under the identity of the connection used. Ensure that identity has at least Read access to the SharePoint list. For sensitive data, consider using a service principal or a dedicated user account.
• AI Builder Credits: Each execution of the AI Prompt consumes AI Builder capacity. Monitor usage if you expect high volume.
• Large Lists: The HTTP request does not handle pagination by default. For lists with thousands of items, add a $top parameter or implement a loop that follows odata.nextLink.
• Data Types: The AI Prompt must see the correct column types to generate valid OData. Always include the TypeAsString information.

Common Pitfalls and Troubleshooting

Final Recommendation

This workaround gives you the power of natural‑language querying against any SharePoint list, all without moving data to a separate knowledge store. The same flow can be reused for multiple lists – just modify the agent’s instructions to accept different list names. Keep the AI Prompt instructions clear and test with real user questions to tune the output format. If you need reliable, conversational access to live SharePoint data, this pattern is a solid foundation.

References

• Matthew Devaney, Copilot Studio SharePoint List Knowledge Workaround (original inspiration – see originalReference in frontmatter)
• Use OData query operations in SharePoint REST requests (Microsoft Learn)
• Create text with GPT using AI Builder prompts (Microsoft Learn)