Forum Discussion
Forms to Sharepoint
I have a Microsoft Form with 20 questions.
I know how to export the answers to a Sharepoint-list but I have a challenge I've been struggling with for a few days.
A simple transfer will give me a list with answer 1-20 in different columns but I want it to based on the information in question 11-20 be different rows in the list.
Row 1: Columns with questions 1-10 and 11 (if not empty)
Row 2: Columns with questions 1-10 and 12 (if not empty)
Row 3: Columns with questions 1-10 and 13 (if not empty)
Row 4: Columns with questions 1-10 and 14 (if not empty)
etc.
Can someone help me with if this is possible?
Thank you!
Hi nima_einarmattsson, a way to do this is with Power Automate (Flow).
First of all create your list with the Title column for the responder's email address and then a single line of text column for each question Q1 - Q10 and then another single of text column for called Q11-20 which will hold the value of whichever of the answers is completed.
1. I've made the first 10 questions to have required answers:
2. The second set of 10 questions are optional:
3. Then in the flow the trigger is a Forms When a new response is submitted. The first action is then to Initialize a string variable. This will later on hold the answer from questions 11 -20. The next item is to get the response that was submitted:
4. The next action is to append to the string variable the answers from the Get response details section in the dynamic content box. What I've done here is also to add the question number so that later you know which question the user has answered.
5. The next action is a SharePoint Create item. Select the site and list and add in the responder's email and the answers to the first 10 questions in the relevant field from the dynamic content. Finally you add the variable into the Q11-20 field.
When the user submits the form with the required answers 1-10 and the answer they've chosen from 11-20 the result will be as follows. It shows which question number has an answer and what that answer is.
Hope that does what you want and that it helps.
Rob
Los GallardosMicrosoft Power Automate Community Super User
Hi nima_einarmattsson, a way to do this is with Power Automate (Flow).
First of all create your list with the Title column for the responder's email address and then a single line of text column for each question Q1 - Q10 and then another single of text column for called Q11-20 which will hold the value of whichever of the answers is completed.
1. I've made the first 10 questions to have required answers:
2. The second set of 10 questions are optional:
3. Then in the flow the trigger is a Forms When a new response is submitted. The first action is then to Initialize a string variable. This will later on hold the answer from questions 11 -20. The next item is to get the response that was submitted:
4. The next action is to append to the string variable the answers from the Get response details section in the dynamic content box. What I've done here is also to add the question number so that later you know which question the user has answered.
5. The next action is a SharePoint Create item. Select the site and list and add in the responder's email and the answers to the first 10 questions in the relevant field from the dynamic content. Finally you add the variable into the Q11-20 field.
When the user submits the form with the required answers 1-10 and the answer they've chosen from 11-20 the result will be as follows. It shows which question number has an answer and what that answer is.
Hope that does what you want and that it helps.
Rob
Los GallardosMicrosoft Power Automate Community Super User
Thank you very much for your answer. In my case some people will just answer question 11, some 11 and 12, some 11 12 13, etc.
Is there a similar code for that? So that if someone answers 11, 12, 13 and 14 I'll get:
Row 1: Q1, Q2, ...., Q10, Q11
Row 2: Q1, Q2, ...., Q10, Q12
Row 3: Q1, Q2, ...., Q10, Q,13
Row 4: Q1, Q2, ...., Q10, Q14
and similar..
But if only 11 and 12:
Row 1: Q1, Q2, ...., Q10, Q11
Row 2: Q1, Q2, ...., Q10, Q12
Thank you!
nima_einarmattsson my solutions copes with that with no problem. We're not restricting the user to answering just 1 question from the optional ones: they might answer answer 1, they might answer 10. The image below shows what you see in the list if they answer questions 11 and 12:
Rob
Los Gallardos
Microsoft Power Automate Community Super User
