Parsing Microsoft Forms Likert questions using Flow

%3CLINGO-SUB%20id%3D%22lingo-sub-171944%22%20slang%3D%22en-US%22%3EParsing%20Microsoft%20Forms%20Likert%20questions%20using%20Flow%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-171944%22%20slang%3D%22en-US%22%3E%3CP%3EEarlier%20this%20year%2C%20Microsoft%20announced%20the%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FMicrosoft-Forms-Blog%2FUse-the-Likert-a-new-survey-question-type-in-Microsoft-Forms%2Fba-p%2F147201%22%20target%3D%22_blank%22%3ELikert%20survey%20question%20type%20in%20Microsoft%20Forms%3C%2FA%3E.%26nbsp%3B%20A%20Likert%20captures%26nbsp%3Bmultiple%20choice%20question%20on%20a%20scale.%20%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20847px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F30368iC9B9F14DF31E8BDF%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Likert.PNG%22%20title%3D%22Likert.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EWhen%20the%20survey%20responses%20are%20loaded%20into%20a%20Flow%2C%26nbsp%3Beach%20of%20the%20Likert%20options%20is%20stored%20in%20a%20separate%20dynamic%20content%20value%20as%20shown%20below%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F30369i81C5E4DCB05897BD%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22DynamicContent.png%22%20title%3D%22DynamicContent.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELooks%20simple%2C%20but%20there's%20a%20catch.%26nbsp%3B%20Each%20of%20the%20values%20is%20actually%20captured%20as%20a%20JSON%20object%20rather%20than%20the%20simple%20response.%26nbsp%3B%20To%20get%20to%20the%20actual%20value%2C%20I%20had%20to%20leverage%20the%26nbsp%3B%3CSTRONG%3EParse%20JSON%26nbsp%3B%3C%2FSTRONG%3Eaction%20to%20convert%20the%20value%20into%20properly%20structured%20JSON.%26nbsp%3B%20The%20format%20of%20JSON%20payload%20is%20as%20follows%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%7B%0A%20%20%22id%22%3A%205%2C%0A%20%20%22order%22%3A%201.0%2C%0A%20%20%22answerOrder%22%3A%200%2C%0A%20%20%22displayText%22%3A%20%22Orange%20Juice%22%2C%0A%20%20%22invalid%22%3A%20false%2C%0A%20%20%22url%22%3A%20null%2C%0A%20%20%22description%22%3A%20null%2C%0A%20%20%22symbol%22%3A%20null%2C%0A%20%20%22symbolColor%22%3A%20null%2C%0A%20%20%22isCustomChoice%22%3A%20false%2C%0A%20%20%22customProperties%22%3A%20%22%7B%5C%22IsGenerated%5C%22%3Atrue%7D%22%2C%0A%20%20%22key%22%3A%20null%2C%0A%20%20%22image%22%3A%20%7B%0A%20%20%20%20%22altText%22%3A%20null%2C%0A%20%20%20%20%22contentType%22%3A%20null%2C%0A%20%20%20%20%22fileIdentifier%22%3A%20null%2C%0A%20%20%20%20%22originalFileName%22%3A%20null%2C%0A%20%20%20%20%22resourceId%22%3A%20null%2C%0A%20%20%20%20%22customProperties%22%3A%20null%2C%0A%20%20%20%20%22resourceUrl%22%3A%20null%0A%20%20%7D%0A%7D%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThen%2C%20to%20access%20each%20of%20the%20values%2C%20I%20leverage%20an%20expression%20for%20the%20Parse%20JSON%20actions.%26nbsp%3B%20For%20example%2C%20to%20get%20the%20breakfast%20drink%2C%20I%20used%20the%20expression%20below%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3Ebody('Parse_Breakfast')%5B'displayText'%5D%3C%2FPRE%3E%0A%3CP%3EHere's%20what%20my%20flow%20looks%20like%20to%20parse%20out%20the%20Breakfast%2C%20Lunch%2C%20and%20Dinner%20drink%20responses%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F30370i627EF57CDF6A46E7%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Parse.PNG%22%20title%3D%22Parse.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHope%20you%20find%20this%20trick%20useful.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-171944%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-251635%22%20slang%3D%22en-US%22%3ERe%3A%20Parsing%20Microsoft%20Forms%20Likert%20questions%20using%20Flow%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-251635%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Gregory%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20you%20be%20able%20to%20share%20the%20JSON%20array%20you%20used%20to%20extract%20the%20individual%20multiple%20choice%20response%20options%3F%20I%20have%20a%20similar%20problem%20where%20there%20are%20certain%20responses%20from%20a%20multiple%20choice%20question%20I%20want%20to%20display%20but%20some%20I%20do%20not%20so%20being%20able%20to%20omit%20them%20via%20JSON%20seems%20ideal.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-237847%22%20slang%3D%22en-US%22%3ERe%3A%20Parsing%20Microsoft%20Forms%20Likert%20questions%20using%20Flow%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-237847%22%20slang%3D%22en-US%22%3E%3CP%3EI%20figured%20it%20out.%26nbsp%3B%20The%20multi%20selection%20question%20returns%20a%20json%20string%20array.%26nbsp%3B%20If%20you%20parse%20the%20json%20(action%2Fdata%20operations%2Fparse%20JSON)%20and%20then%20apply%20to%20each.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-234452%22%20slang%3D%22en-US%22%3ERe%3A%20Parsing%20Microsoft%20Forms%20Likert%20questions%20using%20Flow%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-234452%22%20slang%3D%22en-US%22%3E%3CP%3EGreat%20blog%2C%20how%20would%20you%20do%20something%20similar%20with%20a%20multi%20answer%20question%20in%20forms%3F%26nbsp%3B%20Right%20now%20it%20comes%20in%20as%20one%20string%20(csv)%20that%20contains%20all%20the%20answers%20that%20were%20checked.%26nbsp%3B%20I%20need%20to%20split%20those%20out%20and%20treat%20them%20as%20separate%20objects.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-233989%22%20slang%3D%22en-US%22%3ERe%3A%20Parsing%20Microsoft%20Forms%20Likert%20questions%20using%20Flow%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-233989%22%20slang%3D%22en-US%22%3E%3CP%3ESorry%20for%20not%20clarifying%20in%20my%20original%20post.%26nbsp%3B%20The%20%3CSTRONG%3EParse_Breakfast%3C%2FSTRONG%3E%26nbsp%3Bis%20the%20JSON%20output%20for%20the%20Breakfast%20value%20using%20the%20following%20schema%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%7B%0A%20%20%20%20%22type%22%3A%20%22object%22%2C%0A%20%20%20%20%22properties%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%22id%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%22type%22%3A%20%22number%22%0A%20%20%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%20%20%20%20%22order%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%22type%22%3A%20%22number%22%0A%20%20%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%20%20%20%20%22answerOrder%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%22type%22%3A%20%22number%22%0A%20%20%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%20%20%20%20%22displayText%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%22type%22%3A%20%22string%22%0A%20%20%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%20%20%20%20%22invalid%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%22type%22%3A%20%22boolean%22%0A%20%20%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%20%20%20%20%22url%22%3A%20%7B%7D%2C%0A%20%20%20%20%20%20%20%20%22description%22%3A%20%7B%7D%2C%0A%20%20%20%20%20%20%20%20%22symbol%22%3A%20%7B%7D%2C%0A%20%20%20%20%20%20%20%20%22symbolColor%22%3A%20%7B%7D%2C%0A%20%20%20%20%20%20%20%20%22isCustomChoice%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%22type%22%3A%20%22boolean%22%0A%20%20%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%20%20%20%20%22customProperties%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%22type%22%3A%20%22string%22%0A%20%20%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%20%20%20%20%22key%22%3A%20%7B%7D%2C%0A%20%20%20%20%20%20%20%20%22image%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%22type%22%3A%20%22object%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22properties%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22altText%22%3A%20%7B%7D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22contentType%22%3A%20%7B%7D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22fileIdentifier%22%3A%20%7B%7D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22originalFileName%22%3A%20%7B%7D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22resourceId%22%3A%20%7B%7D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22customProperties%22%3A%20%7B%7D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22resourceUrl%22%3A%20%7B%7D%0A%20%20%20%20%20%20%20%20%20%20%20%20%7D%0A%20%20%20%20%20%20%20%20%7D%0A%20%20%20%20%7D%0A%7D%3C%2FPRE%3E%0A%3CP%3EHere's%20what%20the%20Flow%20looks%20like%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20650px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F43312i1340659B23CB5B36%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Likert.PNG%22%20title%3D%22Likert.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-190789%22%20slang%3D%22en-US%22%3ERe%3A%20Parsing%20Microsoft%20Forms%20Likert%20questions%20using%20Flow%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-190789%22%20slang%3D%22en-US%22%3E%3CP%3EI%20did%20not%20you%20clearly%2C%20I%20need%20to%20extract%20the%20value%20of%20Likert%20from%20the%20mircosoft%20form%20while%20forwarding%20the%20notification%20using%20Microsoft%20flow.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20did%20not%20get%20you%20in%20the%20later%20part.%20Please%20explain%20me%20in%20brief%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-185316%22%20slang%3D%22en-US%22%3ERe%3A%20Parsing%20Microsoft%20Forms%20Likert%20questions%20using%20Flow%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-185316%22%20slang%3D%22en-US%22%3E%3CP%3E...%20just%20in%20case%20someone%20else%20is%20looking%20for%20this...%20for%20the%20schema%2C%20you%20just%20need%20to%20select%20%22Use%20sample%20payload%20to%20generate%20schema%22%2C%20and%20copy%20and%20paste%20from%20the%20JSON%20output%20you%20may%20have%20(similar%20to%20the%20example%20included%20in%20the%20post).%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20the%20%22Content%22%20field%20you%20should%20use%20the%20corresponding%20field%20from%20the%20form%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-183242%22%20slang%3D%22en-US%22%3ERe%3A%20Parsing%20Microsoft%20Forms%20Likert%20questions%20using%20Flow%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-183242%22%20slang%3D%22en-US%22%3Ealso%20the%20schema%20%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-183240%22%20slang%3D%22en-US%22%3ERe%3A%20Parsing%20Microsoft%20Forms%20Likert%20questions%20using%20Flow%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-183240%22%20slang%3D%22en-US%22%3EWhat%20is%20the%20content%20in%20'Parse%20Breakfast'%20and%20others%20%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2102378%22%20slang%3D%22en-US%22%3ERe%3A%20Parsing%20Microsoft%20Forms%20Likert%20questions%20using%20Flow%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2102378%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3BAny%20idea%20how%20to%20do%20the%20same%20thing%20in%20a%20SharePoint%20Online%20list%3F%20The%20fields%20are%20text%20(long%20form).%20Ivor%3C%2FP%3E%3C%2FLINGO-BODY%3E
MVP

Earlier this year, Microsoft announced the Likert survey question type in Microsoft Forms.  A Likert captures multiple choice question on a scale.  

 

Likert.PNG

When the survey responses are loaded into a Flow, each of the Likert options is stored in a separate dynamic content value as shown below:

 

DynamicContent.png

 

 

Looks simple, but there's a catch.  Each of the values is actually captured as a JSON object rather than the simple response.  To get to the actual value, I had to leverage the Parse JSON action to convert the value into properly structured JSON.  The format of JSON payload is as follows:

 

{
  "id": 5,
  "order": 1.0,
  "answerOrder": 0,
  "displayText": "Orange Juice",
  "invalid": false,
  "url": null,
  "description": null,
  "symbol": null,
  "symbolColor": null,
  "isCustomChoice": false,
  "customProperties": "{\"IsGenerated\":true}",
  "key": null,
  "image": {
    "altText": null,
    "contentType": null,
    "fileIdentifier": null,
    "originalFileName": null,
    "resourceId": null,
    "customProperties": null,
    "resourceUrl": null
  }
}

 

Then, to access each of the values, I leverage an expression for the Parse JSON actions.  For example, to get the breakfast drink, I used the expression below

 

body('Parse_Breakfast')['displayText']

Here's what my flow looks like to parse out the Breakfast, Lunch, and Dinner drink responses:

 

Parse.PNG

 

Hope you find this trick useful.

9 Replies
What is the content in 'Parse Breakfast' and others ?
also the schema ?

... just in case someone else is looking for this... for the schema, you just need to select "Use sample payload to generate schema", and copy and paste from the JSON output you may have (similar to the example included in the post). 

For the "Content" field you should use the corresponding field from the form

I did not you clearly, I need to extract the value of Likert from the mircosoft form while forwarding the notification using Microsoft flow.

 

I did not get you in the later part. Please explain me in brief

Sorry for not clarifying in my original post.  The Parse_Breakfast is the JSON output for the Breakfast value using the following schema:

 

{
    "type": "object",
    "properties": {
        "id": {
            "type": "number"
        },
        "order": {
            "type": "number"
        },
        "answerOrder": {
            "type": "number"
        },
        "displayText": {
            "type": "string"
        },
        "invalid": {
            "type": "boolean"
        },
        "url": {},
        "description": {},
        "symbol": {},
        "symbolColor": {},
        "isCustomChoice": {
            "type": "boolean"
        },
        "customProperties": {
            "type": "string"
        },
        "key": {},
        "image": {
            "type": "object",
            "properties": {
                "altText": {},
                "contentType": {},
                "fileIdentifier": {},
                "originalFileName": {},
                "resourceId": {},
                "customProperties": {},
                "resourceUrl": {}
            }
        }
    }
}

Here's what the Flow looks like

Likert.PNG

 

 

 

Great blog, how would you do something similar with a multi answer question in forms?  Right now it comes in as one string (csv) that contains all the answers that were checked.  I need to split those out and treat them as separate objects.

I figured it out.  The multi selection question returns a json string array.  If you parse the json (action/data operations/parse JSON) and then apply to each.

Hi Gregory, 

 

Would you be able to share the JSON array you used to extract the individual multiple choice response options? I have a similar problem where there are certain responses from a multiple choice question I want to display but some I do not so being able to omit them via JSON seems ideal.

 

Thanks in advance.

@Haniel Croitoru Any idea how to do the same thing in a SharePoint Online list? The fields are text (long form). Ivor