Save Forms multiple choice question in separate SharePoint columns with Power Automate
“We are attempting to use Power Automate to separate Microsoft forms multiple choice questions into separate columns within a Sharepoint list to trigger follow-on actions. Responses currently appear within the same cell as default.”
When you save multiple choice questions from MS Forms into SharePoint with Power Automate, it’ll be saved as an array. It won’t process it in any way by itself, it’ll just take the array as a string and store it, e.g. [“OptionA”,”OptionB”,”OptionC”]. But what if you want to split the values for a better overview? To store the choices in separate SharePoint columns? One column for each choice and store the information if user selected it?
Use a composed expression
Like many times before, the solution is to use expressions. The main expression is the if(…) expression as already described in the post on formatting empty dates. Add a condition as the first parameter, and what should happen if it’s true or false as the other parameters.
if(<condition>, <ifConditionIsTrue>, <ifConditionIsFalse>)In the <condition> you should check if the array from the Forms question contains a specific value. And here comes the second expression: contains(…). Contains(…) in this situation will check if the question response contains the specific value.
contains('["OptionA","OptionB","OptionC"]','OptionA') will return true
contains('["OptionB","OptionC"]','OptionA') will return falseReplacing the fixed array with the output from ‘Get response details’ (the default way to process MS Forms):
contains(outputs('Get_response_details')?['body/r4a2197a370d846029b78cdbf87270043'],'OptionA')Putting it all together, you can check if the Forms question contains the specific response. If it does, update the <ifConditionIsTrue> value, if it doesn’t, update the <ifConditionIsFalse> value. The example below uses SharePoint choice columns with values ‘Yes’ and ‘No’:
if(contains(outputs('Get_response_details')?['body/r4a2197a370d846029b78cdbf87270043'],'OptionA'),'Yes','No')
Repeat the same expression for all the columns, always changing only the value corresponding to the current column.
OptionA Value: if(contains(...,'OptionA'),'Yes','No')
OptionB Value: if(contains(...,'OptionB'),'Yes','No')
OptionC Value: if(contains(...,'OptionC'),'Yes','No')Summary
Storing the multiple choice questions from MS Forms into separate SharePoint columns with Power Automate can be challenging. But you won’t avoid additional processing no matter how you decide to use that question’s response. Even if you’d like just to display it in a readable way, you’ll need to convert the array with responses into string anyway.
But the solution using if(…) and contains(…) expressions isn’t much more complicated, and it can make your data much easier to read, search and filter.
This is good but I’m still confused, can you show it step by step in a flow
Hello Ryan,
take a look on the previous post on storing the Forms data to SharePoint: https://tomriha.com/how-to-store-input-from-ms-forms-into-sharepoint-list/, this post is an extension of that one.