Text Analytics with Azure Cognitive Service for Power Automate– Part 3

In our previous post in this series, we understood how to incorporate Azure Microsoft Cognitive Services: Text Analytics API into an Power Apps.

In this blog, we will collect the customer responses in Microsoft Form, use Microsoft Azure Text Analytics in Power Automate (Microsoft Flow) action and store the result in SharePoint List.

In SharePoint List we will use the Column Customizer and to quickly display if the customer is ‘Happy‘ or ‘UnHappy‘ based on the value returned by Text Analysis Sentiment API.

Note: This API returns a sentiment score between 0 and 1 for each document, where 1 is the most positive.

This blog series is focused towards Power-User’s so we will be using the Platforms\Tools below:

• Introduction to Azure Cognitive Service(recommended).
  • Postman REST Client
  • PowerShell
• Text Analytics with Azure Cognitive Service for Power Apps.
• Text Analytics with Azure Cognitive Service for Power Automate.

---

• Now that we have our prerequisites in place lets start building Feedback analysis system:
  • Part 1: Setting up a Microsoft Forms.
  • Part 2: Configuring the SharePoint Online List.
  • Part 3: Microsoft Power Automate to Analyze Response and store the feedback in SharePoint List. 

Part 1: Setting up Microsoft Forms:

For this demo, create a sample Form with Text Field.

---

Part 2: Configuring the SharePoint Online List

Step 1: Create a SharePoint Online List with reference to article and create a column of type number and Single Line Text with reference to article.

• Create a SharePoint Online List:

• Create a SharePoint Online Column:

• Click on the name of the column, and click Column settings, then choose ‘Format this column’.

Your pane will show on the right side of the screen, click on ‘Advanced Mode‘ and paste this JSON in.

	{
	  "elmType": "div",
	  "style": {
	    "box-sizing": "border-box",
	    "padding": "0 2px"
	  },
	  "attributes": {
	    "class": {
	      "operator": ":",
	      "operands": [
	        {
	          "operator": ">",
	          "operands": [
	            "@currentField",
	            0.7
	          ]
	        },
	        "sp-css-backgroundColor-successBackground sp-field-iconRightAligned sp-field-borderAllBold sp-field-borderAllSolid sp-css-borderColor-green",
	        {
	          "operator": ":",
	          "operands": [
	            {
	              "operator": "<",
	              "operands": [
	                "@currentField",
	                0.7
	              ]
	            },
	            "sp-css-backgroundColor-blockingBackground sp-field-iconRightAligned sp-field-borderAllBold sp-field-borderAllSolid sp-css-borderColor-red",
	            ""
	          ]
	        }
	      ]
	    }
	  },
	  "children": [
	    {
	      "elmType": "span",
	      "attributes": {
	        "iconName": {
	          "operator": ":",
	          "operands": [
	            {
	              "operator": ">",
	              "operands": [
	                "@currentField",
	                0.7
	              ]
	            },
	            "Emoji2",
	            {
	              "operator": ":",
	              "operands": [
	                {
	                  "operator": "<",
	                  "operands": [
	                    "@currentField",
	                    0.7
	                  ]
	                },
	                "EmojiDisappointed",
	                ""
	              ]
	            }
	          ]
	        },
	        "class": {
	          "operator": ":",
	          "operands": [
	            {
	              "operator": ">",
	              "operands": [
	                "@currentField",
	                0.7
	              ]
	            },
	            "sp-css-color-green sp-field-iconPadding",
	            {
	              "operator": ":",
	              "operands": [
	                {
	                  "operator": "<",
	                  "operands": [
	                    "@currentField",
	                    0.7
	                  ]
	                },
	                "sp-css-color-red sp-field-iconPadding",
	                ""
	              ]
	            }
	          ]
	        }
	      }
	    },
	    {
	      "elmType": "span",
	      "style": {
	        "overflow": "hidden",
	        "text-overflow": "ellipsis",
	        "padding": "0 2px"
	      },
	      "txtContent": "@currentField",
	      "attributes": {
	        "class": {
	          "operator": ":",
	          "operands": [
	            {
	              "operator": ">",
	              "operands": [
	                "@currentField",
	                0.7
	              ]
	            },
	            "sp-field-bold sp-css-color-green",
	            {
	              "operator": ":",
	              "operands": [
	                {
	                  "operator": "<",
	                  "operands": [
	                    "@currentField",
	                    0.7
	                  ]
	                },
	                "sp-field-bold sp-css-color-red",
	                ""
	              ]
	            }
	          ]
	        }
	      }
	    }
	  ]
	}

The above JSON checks if the Sentiment score and formats the Column:

• If(Sentiment score)> 0.7(greater than) -> Add a smiling emoji, set background color to green.
• Else -> Add a sad emoji, set background color to red.

---

Part 3: Microsoft Power Automate to Analyze Response and store the feedback in SharePoint List.

Navigate to flow.microsoft.com, select ‘Templates‘, in the ‘Search bar‘ type ‘Record form response in SharePoint‘ select the first template.

Our modified Power Automate(Microsoft Flow) template should look like the screenshot below.

• You will need to select  the ‘Form Id‘ in the ‘When a new response is submitted‘ trigger and select select  the ‘Form Id‘ in the ‘Get response details’ action
• We will  add one step ‘Text Analytics‘ action and in the action add the ‘Connection Name‘ , ‘Account Key‘ and ‘Site URL‘ with reference to link.

• Configuring the Text Analytics Action:
  • Document Id-1 – user the ‘Guid()’ function.
  • Document Text-1 – ‘Please Provide Feedback?‘ the output of the ‘Get Response action‘.
  • Document Language – en

• Configure the ‘Create item‘ action:
  • Site Address – Select the site-collection which hold your list.
  • List Name – Select the List.
  • Tittle – Select the ‘Responder’s Email‘ the output of the ‘Get Response details‘ action.
  • Analyzed Feedback – Select the Score the output of the ‘Detect Sentiment(v2)’ action.
  • Feedback Text – Select the ‘Please provide feedback‘ the output of the ‘Get Response details‘ action.

Note: Once you add the ‘Score‘ Field, it will automatically add the ‘Apply to each‘ loop around the ‘Create item‘ action

• All done! Now it’s time to test our Flow.
  • Open the link\URL for the Microsoft Form in the browser and submit a Form.
  • The Flow will be triggered automatically and you should see a new item with the ‘Score‘ in the SharePoint Online List with proper formatting

I hope after this example you have a better understanding of Text Analytics API, and you’ve enjoyed seeing how you can use it with Power Automate.

Subscribe to this blog for the latest updates about SharePoint Online, Microsoft Flow, Power Apps and document conversion and manipulation