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
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Users online (3,156)