Insane Copilot Studio SharePoint List Knowledge Workaround!!

SharePoint lists can't be added as knowledge to a Copilot Studio Agent but you can use a little known hack with Agent actions instead. And the results are amazing

The Agent can use a flow action to determine the SharePoint list columns and data types. Then it passes this information to an AI prompt, which writes a SharePoint REST API filter query. The filter query fetches the SharePoint list items we need, and Copilot uses generative AI to respond with the final answer.

Video Chapters 🎬
[00:00:00] Use A SharePoint List As Knowledge Demo
[00:02:32] Create A New Agent & Flow Action From Scratch
[00:05:38] Setup A SharePoint List With Text, Numbers & Dates
[00:06:27] SharePoint HTTP Action To Get List Columns & Data Types
[00:14:56] AI Prompt to Generate SharePoint REST API Filter Query
[00:20:23] Add Prompt Action To Flow And Fetch SharePoint List Items
[00:23:59] Respond To The Agent In Copilot Studio
[00:26:25] Configure Agent Action & Inputs In Copilot Studio
[00:29:22] Test Your Copilot Studio Agent

Follow along with these written instructions ✍
https://www.matthewdevaney.com/copilo...

Code Snippets 👨‍💻

SharePoint HTTP URI to Get List Columns And Data Types:
_api/web/lists/getbytitle('@{triggerBody()?['text_1']}')/fields?$select=Title,InternalName,TypeAsString&$filter=CanBeDeleted eq true


AI Prompt To Generate SharePoint REST API URI:

Objective:
Generate a valid URI for use in the Power Automate action "Send An HTTP request to SharePoint."

Instructions:
1. Base URI: Start with _api/web/lists/getbytitle('SharePointListName'). Do not include a leading '/'.
2. OData Filter Query: Append an OData filter query based on the user's input, referred to as UserFilterQuery. This should filter the list items according to the user's criteria.
3. Select Query Options: Include the SharePointListColumns in a $select query option, ensuring all columns are specified using the correct SharePoint list column names and data types.
4. Output: Return a single line of text containing the complete, valid SharePoint REST API URI.

Requirements:
Use valid SharePoint REST API syntax.
Do not include additional text or explanations in the output.
Ensure the URI is properly encoded if necessary.
Compare columns in the filter query to data of the same type
Do not use the year function, month function or day function to query dates in the OData filter. Use a date range comparison instead.

Required Information:
SharePointListName: [SharePointListName]
UserFilterQuery : [UserFilterQuery]
SharePointListColumns: [SharePointListColumns]