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

[T_A_M]

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?

[tom_riha · Accepted 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).

[T_A_M]

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!