Populate a multi-select choice field from Microsoft Forms into a SharePoint multi-select column via Flow

This blog post has been revised on May 16th, 2022. We have refreshed the content,  ran through the step-by-step guide below to make sure it’s all working and created a video tutorial to accompany the blog. Don’t hesitate to reach out if you have any questions about business automation.

Click here for more blogs about Business Applications.

Scenario:

When you use Microsoft Forms, sometimes you may wish to export responses automatically into a SharePoint list in order to process the information captured. An example might be a Staff Recognition Form which users can complete in order to recommend colleagues for a bonus. This information can be copied across to a SharePoint list automatically so it can then be approved by senior management. One issue I encountered involved pulling the results from a multiple-choice field. The way this data is generated by Forms contains additional formatting which cannot be pushed into a SharePoint choice column.  

"Colors": [
	{
		"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
			"Id": 0,
			"Value": "Red"
	},
	{
		"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
			"Id": 2,
			"Value": "Yellow"
	}	
]

The resulting data therefore needs to cleaned before it can be populated in SharePoint.

I will briefly cover the steps required to create a Flow which takes the multiple-choice Form results and parses the data into a format that is accepted by SharePoint. This Flow has these core steps:

• initialize an array variable
• remove extra symbols from Microsoft Forms string output
• split the previous line into an array (not the same as step 1)
• append to array variable using a formula
• create a SharePoint list item

First create a multi-select question in your Microsoft form.

Then create a multi-select Choice column in your SharePoint list with the exact same options.

In Flow:

1. Select the template “Record form responses in SharePoint” and Add an ’Initialize variable’ as an Array type

2. Add two Compose actions within your ‘Apply to Each’ action after the ‘Get response details’ Form action.

Rename the first Compose action to – Format form output
Rename the second Compose action to – Split previous output into Array
(you rename these so the below expressions do not need any changes – except for the ID)

– The first Compose action takes the string output from the Choice question in your Form and removes the [] and “”
The expression is:

replace(replace(replace(body('Get_response_details')?['rc7b55b06fea64796bae88a58d9425388'],'","','|'), '["',''), '"]','')

To get ‘rc7b55b06fea64796bae88a58d9425388‘ (which is the ID of the multi-select choice field in your Form), run the Flow once.
Look at the Run history for the Flow you just ran.
Under Get response details output section, scroll through until you see the field result for your choice question. It will have a long ID in front of it. Copy this and put this in your expression.

– The second Compose action splits the results obtained via the previous Compose output.
The expression used for this is:

split(outputs('Format_form_output'),'|')

3. Add a second embedded ‘Apply to each’ action and the output will be the result from the split compose action.

4. Add a ‘Append to array variable’ action and input the following into the value field so it looks like the below example

{
	"Value": "[select current item from dynamic content]"
}

5. When you add the output of your variable to the ‘Create Item’ record, ensure you select the option to “Switch to input entire array”. If you do not select this option, you will not be able to see or select the array variable that you created (as shown below)