The Likert question type is a great option in Forms, which gives users a matrix of sorts, to provide a valuable answer. Check out this example I use for our user group’s feedback survey:
Some fun facts about the Likert scale on Wikipedia.
Using Microsoft Flow, I’d like to perform some automation when this survey is filled out. I want to send a nice, clean email off to the user group organizers sharing when a new result is submitted. Unfortunately, it’s not now that easy.
Using Likert in Flow is SO MUCH EASIER NOW!
Big thanks to Zhongzhong from Microsoft Forms team for leaving a comment on my post. Microsoft has fixed this headache, now you can just pull the value from the form without any of the nasty JSON parsing.
Done! That simple.
The old stuff
I am keeping the rest of this post because 1) it took me a while to write it and 2) there’s still some good stuff in here for other use cases outside of likert fields ;)
Simply putting the field in the body of the email renders a nasty result. We get the JSON result for each of the row values, like:
Topic: { “id”: 6, “order”: 1518727860286.0, “answerOrder”: 0, “displayText”: “Excellent”, “invalid”: false, “url”: null, “description”: null, “symbol”: null, “symbolColor”: null, “isCustomChoice”: false, “customProperties”: null, “key”: null, “image”: { “altText”: null, “contentType”: null, “fileIdentifier”: null, “originalFileName”: null, “resourceId”: null, “customProperties”: null, “resourceUrl”: null } }
Speaker Presentation: { “id”: 6, “order”: 1518727860286.0, “answerOrder”: 0, “displayText”: “Excellent”, “invalid”: false, “url”: null, “description”: null, “symbol”: null, “symbolColor”: null, “isCustomChoice”: false, “customProperties”: null, “key”: null, “image”: { “altText”: null, “contentType”: null, “fileIdentifier”: null, “originalFileName”: null, “resourceId”: null, “customProperties”: null, “resourceUrl”: null } }
Office 365 Updates: { “id”: 6, “order”: 1518727860286.0, “answerOrder”: 0, “displayText”: “Excellent”, “invalid”: false, “url”: null, “description”: null, “symbol”: null, “symbolColor”: null, “isCustomChoice”: false, “customProperties”: null, “key”: null, “image”: { “altText”: null, “contentType”: null, “fileIdentifier”: null, “originalFileName”: null, “resourceId”: null, “customProperties”: null, “resourceUrl”: null } }
Community Updates: { “id”: 6, “order”: 1518727860286.0, “answerOrder”: 0, “displayText”: “Excellent”, “invalid”: false, “url”: null, “description”: null, “symbol”: null, “symbolColor”: null, “isCustomChoice”: false, “customProperties”: null, “key”: null, “image”: { “altText”: null, “contentType”: null, “fileIdentifier”: null, “originalFileName”: null, “resourceId”: null, “customProperties”: null, “resourceUrl”: null } }
Venue: { “id”: 6, “order”: 1518727860286.0, “answerOrder”: 0, “displayText”: “Excellent”, “invalid”: false, “url”: null, “description”: null, “symbol”: null, “symbolColor”: null, “isCustomChoice”: false, “customProperties”: null, “key”: null, “image”: { “altText”: null, “contentType”: null, “fileIdentifier”: null, “originalFileName”: null, “resourceId”: null, “customProperties”: null, “resourceUrl”: null } }
Food: { “id”: 6, “order”: 1518727860286.0, “answerOrder”: 0, “displayText”: “Excellent”, “invalid”: false, “url”: null, “description”: null, “symbol”: null, “symbolColor”: null, “isCustomChoice”: false, “customProperties”: null, “key”: null, “image”: { “altText”: null, “contentType”: null, “fileIdentifier”: null, “originalFileName”: null, “resourceId”: null, “customProperties”: null, “resourceUrl”: null } }
Yea, not cool. So a little Binging (yes, I Bing) helped me find a post on this, but the post was a little incomplete. Let’s dive into what we need to do with this value set.
Parse the JSON
If you don’t know what that means: “parse the JSON”; that’s okay. Basically, we need to take that ugly value, with the { and the : and the }, and make it friendly and usable. We have two options to do this, and I’ll walk through both in Microsoft Flow:
Which one? If you will be using the data more than once in the Flow, across actions, or using the data to create conditions in the Flow, use an action. If you are simply using the values once, say for a quick email or save to a data source, then the second option might be faster and easier and messier.
Using an action – Data Operations – Parse JSON
Fortunately, there’s an action for parsing JSON. After you get the Form’s response data, drop in the action Data Operations – Parse JSON.
For the content field, select one of the likert row values. In my example above, I’d select Topic, Speaker Presentation, etc.
For the Schema, click the link below the textbox, “Use sample payload to generate schema”. In the new dialog window, paste in the JSON output. If you don’t have it, you should be able to use mine here:
{ "id": 6, "order": 1518727860286.0, "answerOrder": 0, "displayText": "Excellent", "invalid": false, "url": null, "description": null, "symbol": null, "symbolColor": null, "isCustomChoice": false, "customProperties": null, "key": null, "image": { "altText": null, "contentType": null, "fileIdentifier": null, "originalFileName": null, "resourceId": null, "customProperties": null, "resourceUrl": null } }
Click OK and it should provide a nice Schema value.
I highly suggest naming this action appropriately like I did above. It’s a best practice and when you have multiple values to parse, this is the only way to keep track of what’s going on.
Now you can use this variable wherever you’d like. You’ll use the displayText property, as that is the display value of the row field. Unfortunately, there is no way to get the question value from the form in Flow.
Using an inline expression
The above method is pretty easy, but will add a lot more actions to your flow, if you care (and you shouldn’t by the way). If you want to suck out the value quickly since you’re using it once, you can replicate the above but in a single expression.
You will find towards the end that this method is also messier, but it still works so I thought I’d share.
This can be a little tricky, so let’s go through it slowly. Basically, the end result for the expression should be:
if(empty(body('Get_response_details')?['r468c6af2b38e4ba89167953ced7aefba']),'N/A',json(body('Get_response_details')?['r468c6af2b38e4ba89167953ced7aefba'])['displayText'])Where the body(…)[‘…’] is the likert row value, i.e. Topic. Here’s how we get there (it’s easier than it looks).
Click in the spot of your Flow where you want the value. In my example, I’m emailing the details of the survey, so I click in the body of the email:
Using the fly out window on the right, that has Dynamic Content and Expression menu at the top. Start by clicking Expression (make sure you press Expression first!). In the expression box, copy and paste in this value
if(empty(X),'N/A',json(X)['displayText'])
This fun expression is checking if the field is empty, if so then it will show ‘N/A’. If it’s not, then write the value.
Now, go to the second X, in json(X), and delete the X. Click Dynamic Content and click on the likert row value name, in my example Topic.
Do the same for the first X, in empty(X), and delete the X, then click the same field again.
Your expression should look like my example, except for you unique strings. Click OK.
Rinse and repeat as necessary. Here’s what my email looks like now:
As noted before, kinda unwieldy, all those if(..) values. I can get away with that here since the label is right next to it.
Done!
Push yourself to do the first method I mention, as it’s cleaner and easier to understand. Also, if you were to pass this Flow off to a teammate, the first method would be easier for them to understand, with less assumptions.
‘Til next time, Happy Flowin’!