cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
brock
Frequent Visitor

Pull MS Forms Question

 

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 

1 ACCEPTED SOLUTION

Accepted Solutions
Demaul
Frequent Visitor

@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':

snip_form_flow_httpad.JPG

Make sure you choose Invoke HTTP request

snip_form_flow_questions.JPG

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:

snip_form_flow_parse_and_var.JPG

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:

snip_form_flow_foreach_if.JPG

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

View solution in original post

14 REPLIES 14
AlanPs1
Resident Rockstar
Resident Rockstar

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


Did I answer your question? Mark my post as a solution!

Proud to be a Flownaut!


brock
Frequent Visitor

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     Robot Happy

AlanPs1
Resident Rockstar
Resident Rockstar

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.Capture-413.png

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


Did I answer your question? Mark my post as a solution!

Proud to be a Flownaut!


brock
Frequent Visitor

@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
Resident Rockstar
Resident Rockstar

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Flownaut!


brock
Frequent Visitor

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

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
Regular Visitor

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
Frequent Visitor

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
Frequent Visitor

@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':

snip_form_flow_httpad.JPG

Make sure you choose Invoke HTTP request

snip_form_flow_questions.JPG

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:

snip_form_flow_parse_and_var.JPG

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:

snip_form_flow_foreach_if.JPG

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

View solution in original post

@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! 

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
Frequent Visitor

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)

FullFlowExpanded-Forms Questions and Answers.PNG

 


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

FormAnswersOutput.PNG

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

Invoke HTTP request Output.PNG

 

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.

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

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:

Select QandQ outputs.PNG

 

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.

Create HTML output.PNG

 

Hope this is helpful to someone

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.

 

Nayi94_0-1616103799050.png


Can you help me please? Thank you

Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (61,003)