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

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 II
Super User II

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 II
Super User II

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

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

MSFTBizAppsLaunchEvent

Experience what’s next for Power Virtual Agents

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Users online (68,163)