Pull MS Forms Question

[brock]

How can I pull an MS Forms Question text with an expression?  I can pull the Answer text from the Get Response body with an expression (Filter null values from a Forms Array ). 

When I run a Flow test, the OUTPUTS of Get Response details has this info, but I don't know how to get at it. I am trying to do Select on the outputs on 

[AlanPs1]

Hi @brock 

Are the questions your form the same all the time?

Using the model you linked to, if Q1 and Q2 were to output like the following:

Q1: Is this a form?
A1: Yes

Q2: How cool is this form?
A2: 7

Would that be what you are after? Just in plain text, and not dynamically accessing the actual questions?

Thanks, Alan

[brock]

The questions in my form may change sometimes (it is a shared form).

I am not completely sure, but I think I am looking to "dynamically access the actual questions".

I want to at least avoid having to type in the question text in my expressions.  Of secondary importance is to be able to update the Questions in Forms without having to then modify the Flow experessions.

I would also be grateful for any partial solution to this...  plain text or otherwise     

[AlanPs1]

Hi @brock ,

I am tring to work on a way to get the dynamic data as Flow displays it but in the "Get response details" action on a successful run. I haven't quite cracked that yet and it may not be possible but I am persevering.

In the mean time, consider the expression below from the other post:

if(equals(not(empty(body('Get_response_details')?['r4b6e28cb8fa748dea068dc564271226e'])), true), concat('Q1: ', body('Get_response_details')?['r4b6e28cb8fa748dea068dc564271226e']), null)

This could become:

if(equals(not(empty(body('Get_response_details')?['r4b6e28cb8fa748dea068dc564271226e'])), true), concat('Date of Birth?:  ', body('Get_response_details')?['r4b6e28cb8fa748dea068dc564271226e']), null)

So basically changing "Q1: " to "Date of Birth?: "

This should get the job done and if I can find a way to get at the actual questions from the Form, I will post back.

If you have found my post helpful, please mark thumbs up.

Any other questions, just ask.

Thanks, Alan

[brock]

@AlanPs1 

Yes, that is what I am doing now (putting the question text into the concat().

thanks for looking at this, I hope there is a way.

[AlanPs1]

Hi Brock.
So, there is a way, it is an advanced hack though that involves editing the JSON on the connector and exposing internal properties. I have it working partly in my environment. Stick with what you have for now and I plan on posting a blog about the advanced method once I have it polished. I’ll let you know once I have the method complete and the post out there.

Thanks, Alan.

[brock]

YYYYYYYY        YYYYYYYY    EEEEEEEEEEEEEEEEEE      SSSSSSSSSSS
YYYYYYYY        YYYYYYYY    EEEEEEEEEEEEEEEEEE     SSSSSSSSSSSSS
YYYYYYYY        YYYYYYYY    EEEEEEEEEEEEEEEEEE    SSSSSSSSSSSSSSS
YYYYYYYY        YYYYYYYY    EEEEEEEEEEEEEEEEEE    SSSSSSSS    SSS
 YYYYYYYY      YYYYYYYY     EEEEEEEE              SSSSSSSS
  YYYYYYYY    YYYYYYYY      EEEEEEEE              SSSSSSSS
   YYYYYYYY  YYYYYYYY       EEEEEEEEEEEEE          SSSSSSSS
    YYYYYYYYYYYYYYYY        EEEEEEEEEEEEE           SSSSSSSSSS
     YYYYYYYYYYYYYY         EEEEEEEEEEEEE            SSSSSSSSSS
      YYYYYYYYYYYY          EEEEEEEEEEEEE               SSSSSSSSS
       YYYYYYYYYY           EEEEEEEE                     SSSSSSSS
        YYYYYYYY            EEEEEEEE                     SSSSSSSS
        YYYYYYYY            EEEEEEEEEEEEEEEEE     SSS    SSSSSSSS
        YYYYYYYY            EEEEEEEEEEEEEEEEE     SSSSSSSSSSSSSSS
        YYYYYYYY            EEEEEEEEEEEEEEEEE      SSSSSSSSSSSSS
        YYYYYYYY            EEEEEEEEEEEEEEEEE       SSSSSSSSSSS

[_TJ]

Hi @AlanPs1 ,

Do you have an update on this? I also need the questions, coming from the Form. It's a bit frustration, because I know the info is in there, but I can't access it.

Kind regards,

Thye

[Duri]

Hi @AlanPs1 

 I have browsed a lot and could not find a solution for this. Have you managed to resolve this?

I would be very grateful if you shared your workaround - it would help me a lot.

I will have multiple questionnaires/forms with multiple sections (some filled, some not) - Each single response (which is filled) will make one record (in a database) with an attribute from the question name.

But instead of GUID response form, I need the actual question name (to map it with the proper attribute) - as the questions will be changing over time.

Thanks a lot!

[brock]

I never found a way to what you want. I ended up putting each question response into a Compose and then building a question/answer table by explicitly stating the question text in my expression. It's problematic when the question text changes or I make a mistake.

[Demaul · Accepted solution]

@brock @Duri @_TJ - I found a way to do this with the 'HTTP AD' connector pointing towards the forms domain.  I inspected the network traffic while browsing the forms website in order to find the right endpoint (same as the 'Forms' connector!) and which fields to query for (/questions)

When authenticating I chose the base and service 'https://forms.office.com':

Make sure you choose Invoke HTTP request

And to make it re-usable:

/formapi/api/forms('@{concat(triggerBody()?['resourceData']?['formId'])}')?$expand=questions

^Paste this into the field, not the expression popup.

In the response, questions is an array of objects, each object is a question from the form and it's related properties - most not useful.  For each object the property 'id' will match the key for it's response used in the builtin forms connector.  Another property of that same object 'title' contains the question text as seen in the form. 

That should get the questions and id relationship you're looking for.

I did not need to act on any of the questions, just wanted to pull them for viewing in an email so this may not be useful from here on depending on usecase:

The full schema I used:

{
    "type": "object",
    "properties": {
        "questions": {
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "id": {
                        "type": "string"
                    },
                    "title": {
                        "type": "string"
                    },
                    "order": {
                        "type": "integer"
                    },
                    "type": {
                        "type": "string"
                    },
                    "required": {
                        "type": "boolean"
                    },
                    "questionInfo": {
                        "type": "string"
                    },
                    "groupId": {},
                    "status": {
                        "type": "string"
                    },
                    "subtitle": {},
                    "allowMultipleValues": {},
                    "trackingId": {
                        "type": "string"
                    }
                }
            }
        }
    }
}

A loop to tie questions and responses together:

For the apply to each, select 'questions' from dynamic content.  This should work but it may become obsolete if the api calls change.  You might have to test run the flow to kick the dynamic content to show up.

The first fx body(...) - this should pull the Response based on the id from the current question.  The goal is to verify it's not empty.

@{body('Get_response_details')?[items('Apply_to_each')?['id']]}

^Paste this into the field, not the expression popup.

The output of the compose action pipes into the conditional left-hand side as dynamic content
The right hand side should be the expression: 

string('')

Leaving the 'if yes' branch empty (meaning 'skip if there is no response to the given question')

I append to my string variable:

"@concat('<b>', items('Apply_to_each')?['title'], '</b>  ', body('Get_response_details')?[items('Apply_to_each')?['id']], '<br><br>')"

^Paste this into the field, not the expression popup.

Again - it depends on the usecase, for me this creates a string to nicely fit into emails where the questions are in bold and the response is not. I have two 'breaks' in there for line spacing for readability. 

Hopefully this makes sense

[Gauravmahajan]

@Demaul Thanks for putting together this amazing solution! The HTTP with Azure AD is a premium connector though. Any thoughts on being able to do this using a non-premium connector? We really just need a one time dump of the question id and question mapping so any other suggestions will be helpful too. I am working with a client and dont have access to their Azure environment otherwise logic apps would also have been an option for a one time mapping. Thoughts on what else I can use to at least get a one time mapping? Thanks again for the nice solution! 

[prateekfulsunda]

This was very helpful, thanks for putting it out! Can you please elaborate on how to create JSON template that MS Flow can parse? I fail at this step. Thanks!

[_Ken]

Adding to @Demaul 's post above and great work by the way, I played around with the Data Operation - Select, and came up with following solution: (going into details below)

Here's the output from the Forms 'Get Response Details' with the question ids and corresponding answers.

Here's the results of the 'Invoke an HTTP request' which shows the array of questions with "id" matching the answer above, and the "title" which is the actual question literal

It may help to think of the "Select" action in a similar way to "Apply to Each" action or "For each" in other scripting languages, but "Select" performs changes across items in the array in one action.

From the the entire outputs above we only need to iterate through "questions" array, so we append  ?['questions'] to the outputs body like so:

We can then address each item() directly so:

Question = item()?['title']

For the Answer, we need to look for the item()?['id'] within the body of the outputs from Get Response Details, so:

Answer = body('Get_response_details')?[item()?['id']]

The outputs of the Select action gives us a nice array of Question and Answer:

This array can be used to Create HTML table or Create CSV table. The output of these can simply be added to an email body or attached as a csv file.

Hope this is helpful to someone

[Anonymous]

Hi @Demaul

I am trying to group the questions by sections, but I can't find the property for that. I found the property "groupId" but I noticed that just the MatrixChoice questions have a value, the others are Null.

Can you help me please? Thank you

[cbathis]

@Demaul Thanks for the solution. This is working fine till 22nd July. Suddenly I'm getting below error.

{

  "error": {

    "code": "",

    "message": "The query specified in the URI is not valid. The property 'questions' cannot be used in the $expand query option."

  }

}

Could you please assist on this?

[ArnieReyes]

I am also getting the same issue. Property 'questions' cannot be used in the $expand query option. Can someone help us with this? 

Thank you

[AHUKBOT]

Yeah me too face the same issue please help on us.

[cbathis]

Temp Fix : It works for me

/formapi/api/[Tenant ID]/users/[Form creator oid]/light/runtimeFormsWithResponses('@{concat(triggerBody()?['resourceData']?['formId'])}')?$expand=questions

[AHUKBOT]

@cbathis Hi, How to get Form creator Oid?