Record Microsoft Form choice response to the SharePoint list

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:

SharePointList Demo 
Hi test. when you submit this form, the owner will be able to see y•ur name and email address. 
1. What gift would you prefer? 
Skull Candy 
Google Assistant 
Amazon Alexa 
Submit

  • 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:


ex ・ Ⅳ 'JO 山 マ ロ 
引 55 マ 引 5009 ロ 
年 “ 引 叫 5 ロ


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.


Create item 
Site Address 
• Cist Name 
• Title 
Gift Value - 
Skull Candy 
+ Add new item 
Show advanced options 
Share Point Demo List


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:


{x} 
{x} 
When a new response is submitted 
Delay 
Initialize variab'e Email 
nitialize variab'e Checkbox Array 
Apply to each 2 
out ut from 
L ist of x 
Get a rcpt,' 
Split 
Filter array 
Select 2 
Set variable Check box Array 
Set variable Email Address 
Create item



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.


Whenanewresponseissubmtted


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‘.


Delay 
• Ι—ΙηίΤ


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


{х} 
InitiaIi:e variabIe EmaiI 
EmailA3tress 
[nitiah:e variable Checkbcx Аттау 
SampIe Атгау 
ifiV•' 
о


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.


Apply to each 2 
• Se ect "rom crevious steas 
List of X 
Get a row 
• Loc.ticn 
Document 
File 
• Tan 
• Key Column 
• Key VYIue 
On,orlve for 3usiness 
Tablel 
List of respons„.


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'],';')


uo'ssaldxa 。 Q


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.


Filter array 
INPUTS 
From 
"Skull Candy" , 
"Google Assistant" 
"Amazon Alexa" 
OUTPUTS 
Body 
"Skull Candy", 
"Google Assistant", 
"Amazon Alexa"


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

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


select 2 
• From 
• Map 
Body - Item x 
x 
Enter key 
Enter value

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


Select 2 
INPUTS 
From 
"Skull Candy" , 
"Google Assistant" , 
"Amazon Alexa" 
OUTPUTS 
"Value" : "Skull Candy" 
"Value": "Google Assistant" 
" : Amazon Alexa" 
Os


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


Set variable Check box Array 
Sample Array 
• Name 
• Value



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


• Name 
• Value 
Set variable Email Address 
EmailAdCress 
Email x



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.
Create item 
* Site Address 
List Name 
• Title 
Show advanced options 
SharePoint Demo List 
amaiAddrgss 
Sample Array x


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).



Subscribe to this blog for the latest updates about SharePoint Online, Microsoft Flow, Power Apps and document conversion and manipulation.

2 thoughts on “Record Microsoft Form choice response to the SharePoint list

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s