Use Microsoft Power Virtual Agent to get data from SharePoint | Quisitive
Cloud Security Assessment header
Use Microsoft Power Virtual Agent to get data from SharePoint
December 27, 2019
Use Microsoft Power Virtual Agent to get data from SharePoint

How can we use the Power Virtual Agent to start digging into, and surfacing, data from different sources? Let’s take a look at how to use Power Automate and flows, to extend the reach of Power Virtual Agent.

What’s our use case? Let’s say, hypothetically, that we have a list of cats (yes, cats because they are the best type of furry babies you can have) and we want the bot to be able to tell us who the owner (or “Hooman” as they like to refer to us as) of a specific kitten is.

First start with this link on how to get started to create your first bot:

Once the bot is created, let’s create a new Topic: One note on the trigger phrases is needed. Initially I was hoping that the trigger phrase was going to be wildcard and then parsable to extract the cat name. “Wow, that’s so many fancy words Pontus!”. What I mean by that is: can I trigger it by saying “Who is Dodgers hooman?” ? Unfortunately no. But I learned that triggers are very precise (think German electrical engineer) and we need to look at it more like an action call. So the trigger had to be “Find hooman”. And that would then start the topic. Hopefully we’ll get more options to make it more natural conversation -like in the future.

Anywhoo. When the triggers are set, our first question is to gather the cat name, and prepare the user that we’ll go trigger the flow:

To create the flow we just add a new step and enter the call for an action. This will create the flow in a new tab, and it comes pre-populated with some input and output variables.

Once the flow is saved, i still couldn’t see it in the Virtual Agent’s authoring canvas. The one missing step is to create a Solution and add the flow into it: Once that was done, the flow did show up!

Using some basic input and output variables I could set up the logic needed to handle the passing of data back and forth. If the flow returns “NotFound” – show one message. Else – show the actual persons name:

So how does the flow work it’s magic? First, let’s take a quick look at the data source in SharePoint, pretty basic stuff:

Let’s dig into the flow! I did edit the default JSON since I only needed one input (flow_input_string). Then the basic variable initialization and finally the call to SharePoint to get the item with matching title and at the end update the CatName variable with the Hooman’s name from the SharePoint list.

So that works just fine, but what if the user enters the name of a cat that doesn’t exist in the list? Let’s build some basic exception handling. If the HoomanName variable didn’t get a value set from SharePoint, just set it to “NotFound” since we use that value as the condition on the Virtual Agent’s authoring canvas.

Great! The topic has been created to trigger, gather the name of the cat you want to lookup, trigger the flow, get result back from the flow and then wrapped up with a condition to show different messages back to the bot user.

How will it look if we pass invalid cat name?

How will it look if we pass correct cat name?

Did it work? Let’s take a look at the data source again:


This was a very basic example on getting a single value back from SharePoint. Imagine expanding it to get multiple values, just by expanding the number of output variables from the flow. Easy peasy!