Record Microsoft Form choice response to the SharePoint list

[CFernandes]

A few days back, I was working with one of our customers. His requirement was very simple and he wanted to record Microsoft Forms choice response in a SharePoint List.

I quickly googled and found  a template which exactly meets the customer requirements but he said that the template did not work for him. To be very honest, the customers form was very simple with a multi choice field but when he tried updating the SharePoint list, Power Automate complained. At that point I realized that something simple can also turn into a complicated scenario.

So lets quickly understand the problem statement and resolve it.

Microsoft Form:

• The Form has a Single Question with a Multi Choice Input.
• The Data-Source for the Form is OneDrive for Business Excel.
• The Data in Excel is save in the Format below.

SharePoint List:

Problem in Power Automate:

If you have a close look at the Power Automate screenshot you will see that the SharePoint Column Gift in Drop down and accepts an array but Power Automate return the values from Excel as a string.

To work around the problem, we need to Convert the individual items as an array element and pass it to our SharePoint Control.

From a High-Level our Power Automate would look like the screenshot below:

Without wasting any further time, lets start building our Power Automate Solution:

Step 1: We will use the trigger ‘When a new response is submitted’.

• This means that, whenever a new response gets submitted on the configured Microsoft Form, the Power Automate will get triggered automatically.
• For the ‘Form Id’ in the image below, choose the correct Microsoft Form from the drop down menu.

Step 2: Add the ‘Delay‘  action to your Power Automate so that we can wait for responses to be updated in Excel.

• Set the ‘Count‘ value on 1.
• Unit as ‘Minute‘.

Step 3: Initial a variable of type ‘String‘ and Name is ‘Email‘ and another variable of type ‘Array‘ and Name ‘Sample Array‘.

Step 4: Add the Excel Online (Business) ‘Get a row’ action.

• Location: From the drop down select ‘OneDrive for Business‘.
• Document Library: Select the Document Library which holds the file.
• File: Choose the Excel File path.
• Table: From the dropdown select the ‘Table1‘.
• Key Column: From the dropdown select ‘ID‘.
• Key Value: ‘Response Id’ the output of the Microsoft Forms ‘ When a new response is submitted‘ action.

Note: It will automatically add a ‘Apply to each‘ loop around the action.

Step 5: Transform the String to Array. Add the Set() function to Convert the String to Array.

split(outputs('Get_a_row')?['body/What gift would you prefer?2'],';')

Step 8: Add the ‘Filter array‘ action.

• From: Select the ‘Outputs‘ of the ‘Split Choices‘ action.
• Condition:
  • empty(item()) is equal to false.

Explanation:

• The ‘Split‘ function returns and array with an empty value.
• With the ‘Filter array‘ action we will remove the empty value from out array.

Step 9: Add the ‘Select‘ action to the.

• From: Select the ‘Outputs‘ of the ‘Filter array‘  action.
• Map: Value to the function ‘item()‘.

Explanation: This will transform the array in format that is accepted by SharePoint Multiple Choice .

Step 10: Set the ‘Sample Array‘ variable value to output of the ‘Select‘ action.

Step 11:  Set the ‘EmailAddress‘ variable value to ‘Email‘ of the output of the Excel ‘Get a row’ action.

Step 12 : Outside the ‘Apply to each loop‘, add the SharePoint ‘Create item‘ action.

• Site Address: Set to the target SharePoint site.
• List Name: Select the list from the dropdown(it should auto-populate) the Columns.
• Title: Set the ‘Email Address‘ variable.
• Gift(Choice Input): Set it to ‘Sample Array‘ variable.

All done!

•  Fill in the Microsoft Form, this will Trigger the Power Automate.
• After few seconds, a new item should be created in your list with Multiple Choice column populated(screenshot below).

Thank you for reading 🙂