We will learn how Copilot Studio / Power Virtual Agent get data from SharePoint list? As SharePoint and Power Virtual Agents (PVA) are two Microsoft services that can be integrated to create powerful and intelligent chatbot solutions for your organization lets give brief description first for each of them.

• Power Virtual Agent ( Copilot Studio now )is a Microsoft Power Platform service that enables companies to quickly develop and deploy AI-driven chatbots or virtual agents for interacting with both clients and staff members across a variety of digital channels, such as websites, mobile applications, and messaging platforms. This low-code/no-code approach makes it easier to create and manage conversational experiences, making it usable by a variety of users who lack in-depth coding knowledge. Businesses now have the resources they need to improve customer interactions and automate operations thanks to PVA’s connection with the Power Platform and its AI capabilities, including natural language comprehension.

• SharePoint is a web-based platform developed by Microsoft that provides a collaborative environment for organizations to manage and share information, documents, and data.
  You can integrate your SharePoint lists and libraries with Power Virtual Agents to access this data. For example, you can use PVA to query SharePoint lists for specific information, making it available to your chatbot, and that is what we will do in this post.
  Power Virtual Agents can interact with SharePoint to retrieve documents, PVA can use the SharePoint integration to provide links or send the documents directly through the chatbot interface.

How Power Virtual Agent get data from SharePoint list?

Copilot Studio/Power Virtual Agent get data from SharePoint list with the help of Power Automate, You can create flows in Power Automate that are triggered by Power Virtual Agents’ requests or user interactions, and these flows can perform actions in SharePoint, such as retrieve data, provide information or answers based on the content stored in SharePoint libraries, document approval, file uploads, or data updates all from within the chatbot interface.

Scenario: Travel Request Chatbots

Want to build a travel request chatbot that want to streamline and automate the process of managing travel requests for employees. Such a chatbot can assist with various aspects of travel request management, including submitting, tracking, and approving travel requests.

Solution: using Power Virtual Agents and SharePoint

Here are the steps of integrating Power Virtual agent and Power Automate to get data from SharePoint.

1. Create New Topic : In which we will set trigger phrases that are phrases, keywords, and questions that a user is likely to use, related to the topic of Travel request to activate or initiate a response from a chatbot. These phrases serve as cues for the chatbot to understand and perform specific actions or provide relevant information. They are used to start a conversation or invoke a particular task. Trigger phrases train your chatbot’s natural language understanding model.

• Add new Topic and name it Travel reques

2. In the Trigger click Edit to add Phrases related to the topic as shown below

3. Add Question to provide the user with choices that he can choose from and the response will Save in a variable at the end of the Question , you can click and change it’s name

4. The Power Virtual agent will add conditions Nodes depending on the choices you provide in the Question and Else Conditions to handle scenarios when none of the conditions are met.
   

In Power Virtual Agents, you can use conditions to make decisions and control the flow of the conversation. Conditions allow your chatbot to respond differently based on the user’s input, context, or data values. Within the “Condition” node, you can define the conditions based on the recognized intents, entities (data extracted from user messages), or variables.

5. Specify Actions for each condition, for example in the View My travel requests condition node , we will ask the user to type his Email address and save it in a variable using the Question Node, and then we will retrieve the information from SharePoint list using Call an Action and choose your flow or create new one.

6. The flow will return the information of the user request and display it through a message

Power Virtual Agent get data from SharePoint list

This is the flow that we will use to retrieve data from the SharePoint list to a Power Virtual agent bot.

The flow will use the Power Virtual Agent Flow Trigger to initiate the flow from your chatbot.

1. Configure the flow trigger to pass any required parameters to the flow. In this scenario the parameter will be the user email.

2. Use the Get items to retrieve items that match the user email “The input of the Power Virtual Agent Trigger”.

3. Use the Parse JSON action, and type in the schema the fields that you want to retrieve as Shown below

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "Title": {
                "type": "string"
            },
            "TravelStartDate": {
                "type": "string"
            },
            "Status": {
                "type": "object"
            }
        }
    }
}

4. initialize variable to concatenate the data that returned from the JSON output in a string to send back to the Power Virtual agent
5. Because the status is a choice field ,to get the value of this status field you have to use the compose action and extract the value from the field.

outputs('Compose')['value'] // the value of the travel status from compose action

6. In the Append to string Variable append the data extracted from the Json and the status value from the compose action.

7. The final step is to return the appended string back to the Virtual agent bot

This is how Power Virtual Agent get data from SharePoint list whit the help of Power Automate

See Also

• How To Use Pdf Function In PowerApps?
• Working With PowerApps Nested Gallery Control
• How Power Automate Create Excel Table Dynamically? | Power Automate
• How To Make A Csv File Utf-8 Encoded? | Power Automate
• How To Format Text In PowerApps? PowerApps Convert Text To Other Data Types
• Alternate Row Color In Gallery PowerApps: Simplest Way
• Check If PowerApps Contains Substring
• PowerApps If Statement And Nested If With Example
• Environment Variable In Power Platform With Examples
• Model Driven App With SharePoint Data Source
• PowerApps Get Day Of Week Date

Join us

• Subscribe to Power Platform Geeks YouTube channel.
• Register to Saudi Arabia Power Platform User Group.

Need Help

• Have a related question? Please ask it at deBUG.to Community.