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?
Solved! Go to Solution.
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).
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).
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!
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.
Announcing a new way to share your feedback with the Power Automate Team.
Learn to digitize and optimize business processes and connect all your applications to share data in real time.
User | Count |
---|---|
69 | |
27 | |
22 | |
15 | |
13 |
User | Count |
---|---|
132 | |
43 | |
42 | |
34 | |
31 |