Blog Post

Healthcare and Life Sciences Blog
7 MIN READ

Searching through a SharePoint Term set with a Power Virtual Agent

mjschanne's avatar
mjschanne
Icon for Microsoft rankMicrosoft
Oct 19, 2022

 

During our last hackathon, my team and I worked on creating a Power Virtual Agent that can search through a SharePoint term set. Our use case was providing a bot through teams to help answer questions about acronyms, jargon, etc. that was unfamiliar to employees – especially new employees.

Prerequisites

Why

When I first joined Microsoft, I felt initially overwhelmed by all of the new information coming at me. The classic expression that everyone uses is drinking from a fire hose and it certainly felt like it. However, we do have tools that help with onboarding – in particular, I found our corporate glossary in the form of a searchable SharePoint term set super useful. However, the act of switching to the browser to search for information felt distracting from my meetings in Teams so our team conceived of searching for the information within Teams using a Power Virtual Agent. Beyond providing an alternative avenue to find the information, it also allows users to get the info without having to switch applications or pull their attention away from the source of the question.

 

Businesses of any sufficient size also incur onboarding issues – my experience was not exclusive to Microsoft. A corporate glossary that is easily searchable by employees is a great tool for any organization seeking to streamline their onboarding experience.

Caveat

Our scope for the hackathon was smaller than a production level version of this project, as such we scoped our work to searching one term set in one group in the term store. The project could be improved and expanded by looping through the groups and sets to find the correct term for the user. This is possible through Power Automate but out of the scope of this article.

Creating Terms in SharePoint

To facilitate our hack, our Team decided to emulate the official corporate glossary in a separate tenant so we could iterate quickly. If you don’t already have a term set, you must be a SharePoint contributor, group manager or a term store admin permissions.

  1. Navigate to the termstore: https://{{tenant}}-admin.sharepoint.com/_layouts/15/online/AdminHome.aspx#/termStoreAdminCenter
  2. Create a new term set: Set up a new term set - SharePoint in Microsoft 365 | Microsoft Learn.
  3. Add terms: Create and manage terms in a term set - SharePoint in Microsoft 365 | Microsoft Learn.

You can explore your results programmatically using the query: https://graph.microsoft.com/v1.0/sites/{{tenant}}.sharepoint.com/termStore/groups

in the graph explorer.

Create App Registration

Next, let’s create an app registration to authorize our Power Automate flow to hit the Graph API which will let us retrieve our terms. Our registration will be pretty simple, we only need to add a client secret – don’t lose it (keep it secret, keep it safe).

 

We also need to give the app permissions to information from the term store. Go to the API permissions and add the following:

 

They can be found under Microsoft APIs > Microsoft Graph > Delegated permissions > Term Store. Also be sure to hit Grant admin consent once you’ve added them.

Create KeyVault

Now, we have a secret – we should keep our secret safe. Let’s add a KeyVault and place our clientSecret in KeyVault.

Create Power Virtual Agent

We’re not ready to start on our Power Platforms work. Navigate to https://make.powerapps.com/ and create a new solution. We’re going to be creating a Power Virtual agent, so once your solution is ready, add a new power virtual agent. Complete the creation wizard. For the scope of this article, we’re only going to be modifying the greeting trigger but you can tinker with those to meet your own needs or to bring it more inline with user expectations. Open up your Greeting topic and add a Question after the initial message from the Bot. The question should capture the user’s entire response and save it as a variable. After that, add a Power Automate action that uses your new variable. The result of the Power Automate flow should be delivered to the user at the end of your Topic flow. It should look something like this:

 

To recap, our bot receives a trigger to initiate the search process. It prompts the user for a search term. The user response then triggers a Power Automate flow that takes the user input and will perform our search logic. The bot will then return the result to the user. Right now, it just provides it as plain text but come November the public preview for adaptive cards in Power Virtual Agents is scheduled to be available so get excited.

Add Environment Variables

Before we can work on our Power Automate flow, we’re going to need to add some environment variables that it will rely on. Return to your solution at https://make.powerapps.com/. Add a new key vault environment variable for your client secret. For our project, since we were scoped to a single term group and term set, we also added regular environment variables for their IDs.

Create Power Automate Flow

Let’s go ahead and work on the logic in our Power Automate flow that provides the logic layer for performing our search. We’re going to work on it through the power apps website, so navigate to it through your solution rather than through the PVA. The reason for this is that the Power Automate specific UI does not recognize your environment variables as existing so you’ll want to stay inside the scope of your solution in order to have them be recognized as variables while designing your automate flow.

 

Our flow already starts with the input from our user provided by the question in our PVA’s topic flow. Our next step will be to make a request to the term set to find the term the user searched for. We will add an HTTP action, our request will be a GET. Our URL will be essentially:

https://graph.microsoft.com/v1.0/sites/{{ sharepointSite}}/termStore/groups/{{ groupId}}/sets/{{ setId)}/terms?$filter=labels/any(s:tolower(s/name) eq '{{toLower(triggerBody()['text'])}}’)

You’ll need to build the expression with dynamic content – injecting appropriate environment variables as well as your user input (which should be set to all lower case). You’ll note that we achieve case agnostic search by using two lower as an odata function on the attribute we’re comparing to as well as the expression to our user input.

 

We will also open the advanced options to add Oath using the values from our app registration. By the end, if you peek code on your action, you should see code that resembles the following:

 

 

{
    "inputs": {
        "method": "GET",
        "uri": "https://graph.microsoft.com/v1.0/sites/@{parameters('sharepointSite (emops_sharepointSite)')}/termStore/groups/@{parameters('groupId (emops_groupId)')}/sets/@{parameters('setId (emops_setId)')}/terms?$filter=labels/any(s:tolower(s/name) eq '@{toLower(triggerBody()['text'])}')",
        "authentication": {
            "type": "ActiveDirectoryOAuth",
            "authority": "https://login.microsoftonline.com/",
            "tenant": "@parameters('tenantId (emops_tenantId)')",
            "audience": "https://graph.microsoft.com",
            "clientId": "@parameters('clientId2 (emops_clientId)')",
            "secret": "@parameters('clientSecret (emops_clientSecret)')"
        }
    },
    "metadata": {
        "operationMetadataId": "7ddaecdf-36dd-43d5-b3f6-b644bfe9f29b"
    }
}

 

 

Now that we’re done with our request to search for the term, we need to check for success and then parse the results. To do this we’ll start by adding a conditional action which will check for a 200 status in the response. If we don’t get 200, we do nothing currently – feel free to add some custom logic there as you feel is appropriate. If we do get a success though, we need to parse the contents to see if the values array contains any successful lookups. To do this, under the success branch, add a parse JSON action.

 

Your JSON parse action will take the body of the response to that HTTP request we made and read it. To help get intellisense, you can test a successful version of the call in the Graph Explorer and copy that response into the provide response section of the parse JSON action. The platform will then use that to generate a schema and enable intellisense against that action output.

Now that you’ve parsed the data, you can do whatever you’d like with it – format it or return to the user as is. Come November 2022, you might be able to start creating adaptive cards to send back to the user. For now, our team loops through the values in the response, loops through each description inside that loop, and appends them to a results array which we then join into one string to provide back to our flow.       

Using Our Power Virtual Agent

Now that you have your PVA complete, you need to publish your PVA to Teams. You’ll want to follow the instructions here and then here. Two things to keep in mind, your install only installs it for you. Others will have to install it in the app store in the Built by your colleagues section of Built for your org which is only enabled via a checkbox during your publish to teams.

Conclusion

Creating a PVA that can leverage the Graph API and provide information to users through natural langue processing in a teams chat is pretty straightforward. Terms in SharePoint are just one example of useful information that can be provided to users and you can build pretty robust topic collection for users to interact with. Things to remember when developing:

  • Use Key Vault environment variables for your secrets
  • To reference your environment variables in the flow, use the Power Platform portal not the Power Automate portal
  • Understand in your Power Automate flow if you will need premium actions

If you like the content I put out or want to be part of a community of healthcare developers sharing knowledge and resources, check out our HLS Developer discord at https://aka.ms/HLS-Discord. We have links to all our content there and a bunch of channels to communicate with us and likeminded tech and healthcare people. Hope to see you there.

Updated Oct 19, 2022
Version 1.0
No CommentsBe the first to comment