Recording Microsoft Form Responses In SharePoint

Introduction

Before creating a flow, first, create a SharePoint list in your SharePoint site collection, with the following columns and data types. Thanks to Reza for explaining each minute detail of the actions. You can learn more from Reza’s youtube channel in the references section.

Recording Microsoft Form Responses in SharePoint

The completed form in SharePoint should look like below,

Recording Microsoft Form Responses in SharePoint

Steps

Please follow the below steps to record the form responses in the SharePoint list using Flow. To keep it a little simple, let's start building the flow using the template.

Step 1

Go to the URL https://flow.microsoft.com using your subscription from your organization or using a subscription from the developer portal.

Step 2

Select the option, create a flow and select ‘Template’.

Step 3

Search for ‘Form’ and select the template ‘Record form responses in SharePoint’.

Recording Microsoft Form Responses in SharePoint

Step 4

It will ask for your consent to connect to forms and SharePoint using your email account. Click on ‘Continue’.

Step 5

Update the title of ‘Flow’ on the top left side. Here I have updated it as ‘Record Workshop Feedback’. If you observed, it created a skeleton of the flow.

Step 6

Configure the flow to collect the responses from the Form, and then save it to the SharePoint list. Select the form that you created before. Here I am selecting ‘Workshop Feedback’ and selecting the list as ‘Workshop Feedback’.

  • Select the ‘Form Id’ in both ‘When a new response is submitted' and in ‘Get response details’ as ‘Workshop Feedback’.
  • Select the site where the response needs to be recorded. Here I am selecting the sites from my developer tenant.
  • Notice that when you select the site and the list, it will have all the properties populated in flow designer dynamically.

Step 7

For the list title, I am selecting the ‘Responder’s email’ – ‘Submission Type’ to create a unique title value. These are dynamic values populated under ‘Get Response Details’.

The final ‘Title’ configuration should look like below.

Note
Each Output from the Form Responses is of type ‘String’. you should be able to configure almost anything of type ‘Single line’ and ‘multiline’ in the SharePoint list.

Notice that the output of the ‘Likert’ datatype response from the form is of string, which you can easily configure the response to record in the SharePoint list.

Step 8

You should be able to configure the SharePoint list item values easily, except for the columns of type choice, Datetime, and Rating.

The reason being the form response output is not understood by SharePoint. As we know, the output coming from ‘Form Responses’ is of type string. Now we need to transform the responses of the type which SharePoint understands.

Column Type Format
Date Time Date Time
Choice (single, multichoice) Array
Rating Number

Let's use compose which comes in handy while transforming the ‘Form Responses’

Step 9

Now for Datetime, let's use compose and copy-paste the value from compose to ‘Create Item’ action field ‘Training Date’.

Step 10

For the ‘Overall rating’ column, since it is expecting the numeric value, let's convert the form responses output to numeric value using the below expression. For this, you need to use another ‘Compose’ action rename it to ‘compose – overall rating’ and configure the expression to use float(form responses for the question ‘your overall rating for the Workskop’.

Click anywhere outside the action and your output should appear like this.

Step 11

Configure this ‘Compose – Overall rating’ outputs to ‘Create Item’ action to record the overall rating response in the SharePoint list.

The final configuration output for ‘overall rating’ should look like below,

Step 12

Similarly, the recommendation for the SharePoint list is expecting numeric. Transform the same way using the above steps for this ‘Recommendation’ column. Here I have used expression int(form outputs of how likely you recommend the workshop to a friend or colleague’?

The final configuration for the ‘Recommendation’ field under the ‘Create list item’ action should look like below.

Step 12

Now for the value ‘Avg Session time feedback’ if you select the drop-down it will have all the existing choice values. We do not want to hardcode, instead, we need to record the values getting from the form. Click on ‘Custom Value’ and select the form output response ‘Average suggested time for the Workshop.

The final configuration for the property Avg Session time feedback value should look like this.

Step 13

Now, let's configure the multichoice responses to the SharePoint list. SharePoint expects the format ‘Array’. But the form response output is of type string.

Let's use another compose and rename it to ‘Compose – best modules’ and since this is a multi-choice field there could be more than one value. convert the output of the best modules form response to JSON using JSON() expression. JSON(outputs from form responses ‘what training modules you liked most’).

Step 14

Now convert the JSON output to of type array, where SharePoint understands. for this use the ‘Select’ action inflow and transform the JSON to array output. Have the ‘From’ field configured to outputs from ‘Compose-Best Modules’?

Enter key as ‘Value’ and the value as expression item().

The final configuration of ‘Select’ should look like this.

Step 15

Go to create an action item, and for the ‘Favorite Modules value’ click on ‘Switch to input entire array’. select the output from the ‘Select’ data operation.

The final configuration for your ‘Favorite Module value’ should look like this.

Step 16

Now we are left with adding attachments to the list of items tracked from the responses. For this use, another compose operation and select the output from the form responses ‘upload the completed lab modules’.

The final compose action for form response attachment outputs should look like below,

As you may have noticed from the previous article, all the form attachments are stored in created users one drive. Now we need to grab the attachments from the OneDrive and then attach to SharePoint lists. Let’s see how to do that.

Step 17

Add an action of type ‘Logic’ after creating list item, and then select ‘Apply to each ‘ rename this to ‘Apply to each – attachments’.

Configure the ‘Select output from previous steps to the outputs from the previous ‘compose – attachments’ action which is a JSON file.

Recording Microsoft Form Responses in SharePoint

Step 18

The JSON contains ‘id’ which is unique to each attachment. You can use this property to get the content from ‘OneDrive for business. Now select the ‘Get file content’ action which is from OneDrive for the Business connector.

configure the ‘File’ property to item()?['id'].

Note: item() gets the context of the current item in the loop.

Recording Microsoft Form Responses in SharePoint

Step 19

Now inside the ‘Apply to each – attachment’ add an action ‘Add Attachment’ from ‘SharePoint’ Connector.

Site Address - select the site

List Name - Select the name from the list.

ID - this will be the ID from the ‘Create Item’ action outputs

Filename - This will be the name value from the attachments. Use expression item()?[‘name’].

Since the JSON output attachments have the value ‘name’ as you might have noticed from JSON output.

Recording Microsoft Form Responses in SharePoint

File Content

This is the output of getting file content action. Below is the screen capture for reference.

Below is the output of how it should look for the ‘Add Attachment’ action.

Testing the Flow

Step 1

Now, select ‘Test’ on the top right side of the flow designer, and manually test the flow.

Step 2

Go to your form and submit the response.

Step 3

Observe that if you have followed all the above steps, your flow should successfully run.

Step 4

Now observe the choice values, single-line text values, and date values, and attachments. It should appropriately record all the responses.

Recording Microsoft Form Responses in SharePoint

Conclusion

Thus, in this article, we have seen how to record the form responses of all supported datatypes in SharePoint lists. Hope you find this helpful. If you get stuck, you can refer to the flow package I have attached to compare with your flow design. 

References

  • https://www.youtube.com/watch?v=kbqXsKPTmZk
  • https://www.youtube.com/channel/UCvBYTqRx-n_8KzFO0MJlUVw
  • https://flow.microsoft.com/en-us/blog/use-expressions-in-actions/