Turn multiple choices in Microsoft Forms into multiple rows in Excel

(0) Share

Report

Posted on by CU16121921-0

content removed

Categories:

Building flows

I have the same question (0)

All responses (17)

Answers (1)

Verified answer

wskinnermctc 6,519 Moderator on at

Like (2)

Report

This should be relatively simple depending on your experience. All you need to do is put the form response with the multiple responses into an Array. Then you will use an Apply to Each on each item in the array (options selected). For each one of the items (options selected) add a row into a table.

To put the responses into an array use the json() function around the form response.

Overview of the Flow

Use the Json() function on the Form Response dynamic content to put into the ArrayCreate an Apply to Each and put the Array Variable outputs as the source - then select the Current Item dynamic content in the Excel field

Here is an example form I made and the outputs when I ran the flow.

Form ExampleExcel Table Results

The screenshots I added should help you get the flow built. Let me know if this works for you.

Was this reply helpful? Yes No
CU16121921-0 on at

Like (0)

Report

Hey! Thank you so much for this. I almost have it! I am stuck at the "Apply to Each" part since no action or operation is showing up in it. I think my "Get Response Details" might be incorrect too because I am unsure what "Response Id" to select. What are your thoughts?

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (0)

Report

There should only be 1 Response Id to select and it comes from the trigger.

I don't understand what part you are stuck. What do you mean no action or operation is showing up? You will have to show a screenshot.

Was this reply helpful? Yes No
CU16121921-0 on at

Like (0)

Report

This is your screenshot that I am trying to do:

Here is the screenshot of my Apply to each. I just don't see "Current Item" available in dynamic content

A further scrolled view
searching for it

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (0)

Report

Do me a favor and switch over to the Classic Designer View instead of your current view which is Co-Pilot.

My screenshots are from Classic Designer view and it would be easier to follow if they are the same.

To get into the Classic View - click the three dots ... over by the Test button and select Switch to classic designer.

Once you get into classic designer, see if you still have issues finding things.

Was this reply helpful? Yes No
CU16121921-0 on at

Like (1)

Report

That's way better and all corrected! Thank you!!

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (0)

Report

Glad it works for you @abee1 ,

Can you scroll up to the post I made that has the instruction pictures and click "Accept as Solution" that way this post will be closed out and it will float the Solution to the top of the post.

Was this reply helpful? Yes No
CU-19081224-4 39 on at

Like (0)

Report

Hi @wskinnermctc - thanks for this solution as you have also helped me solve my problem! But I am wondering if it is possible to extend this further. I have a form where I would like to be able to select multiple names and multiples of another question which is Products as this is for a training request.

Essentially a manager would submit multiple names to be trained on multiple products and I would like these to come through as individual lines.

In OPs post - what if there were 2+ names to choose (Tom Hanks and Stevie Wonder) and they were all to be joining BCY, ATL and NCC. That way I wouldn't have to submit a new form for each individual person.

Can I use the same process to isolate the names as well as the LERT's? That way each has their own row in excel.

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (0)

Report

Hi @qphalen , if I'm understanding your question correctly, the process would be relatively the same except you would need an additional array in the beginning for the names, then apply to each the names array similar steps from the example.

However, I don't understand how the multiple names are going to be identified? They are selecting the multiple names from the form (why would names be available for selection)? Or will they enter the multiple names via text?

I think if they are entering the names via text then you will have an issue with errors trying to splice out individual names. Then you will start running into problems dealing with actions on single names vs multiple names.

I think they should just submit a single form per-person. It is not an unreasonable request functionally. A form should take no more than 30 seconds or a minute to complete, so that is like 2 minutes for 2 employees at most.

Meanwhile you will spend hours trying to construct a flow that can be flexible and prevent errors, just so they can input multiple people at once. The cost benefit will be to have a single form submission per person.

If you need more assistance, you should submit a new post to the forum. Solvers/SuperUsers don't really look at older posts that were solved, so you would get an answer with a new post that is unsolved.

Was this reply helpful? Yes No
CU16121921-0 on at

Like (0)

Report

Was this reply helpful? Yes No