cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
T_A_M
Helper IV
Helper IV

Can each response to a Form question populate its own row in Excel?

I have a Form with 40-50 questions, and I need to capture the responses in an Excel table. However, each answer in the form needs to populate its own row, rather than each answer populating its own column. I can do this by creating a separate "add row into a table" operation for every single question in my Form, but this gets very time-consuming and unwieldy. Is there a better way to achieve this?

1 ACCEPTED SOLUTION

Accepted Solutions
tom_riha
Super User
Super User

Hello @T_A_M ,

I'd store all the questions in a 'Compose' action separated by ; , that would create a long string with all the values. The use another 'Compose' action with split(...) expression to turn that string into an array using ; as the separator. That will create an array where each item is 1 response.

split(outputs('Compose'),';')

Then you can loop through that array within 'Apply to each' and create a row using the 'Current item' (each of the responses).

image.png



[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ I also blog about Power Automate solutions even for non-IT people. ]

View solution in original post

2 REPLIES 2
tom_riha
Super User
Super User

Hello @T_A_M ,

I'd store all the questions in a 'Compose' action separated by ; , that would create a long string with all the values. The use another 'Compose' action with split(...) expression to turn that string into an array using ; as the separator. That will create an array where each item is 1 response.

split(outputs('Compose'),';')

Then you can loop through that array within 'Apply to each' and create a row using the 'Current item' (each of the responses).

image.png



[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ I also blog about Power Automate solutions even for non-IT people. ]

Your solution worked great, but my situation has gotten a little more complex. Now I need to have some answers create new rows in the Excel document, and other answers create columns. It would look something like this:


In the form I would have the following questions:
Question 1
Question 1a
Question 2
Question 2a
Question 3
Question 3a

Then the answers would have to populate my Excel spreadsheet like this:

| Answer to question 1 | Answer to question 1a |
| Answer to question 2 | Answer to question 2a |
| Answer to question 3 | Answer to question 3a |

Is this something that might be possible? I've tried creating a second array with the 1a, 2a, and 3a answers, then updating my existing Excel row, but I haven't been successful. I'd appreciate it if you have any suggestions. Thanks! 

Helpful resources

Announcements
 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Users online (2,597)