Using Power Automate to collect Microsoft Forms data and place it into a SharePoint list will take your business to a new level of data utilization. This post is going to take you through the process of building a Power Automate to collect Microsoft Forms data and place it into SharePoint list. We are making the assumption the Microsoft Form and the SharePoint list is already created using best practices. You can think of this as a level 200 tutorial built off of what we have learned from past posts.
Creating a New Power Automate Flow
When creating a new Power Automate flow, on the home page left navigation bar, click Create. You will be met with several types of flow types to choose from. For this example, we will be selecting the “Automated cloud flow.” (See Image 1) When working with M365 apps (Microsoft Forms and SharePoint Online), this is the most common selection.
Once the “Build an automated cloud flow” screen is available, you have two choices to make. The first is easy, the name of the flow. Give the flow a short but descriptive name. Second you need to select the trigger to cause the flow to start. In our case we want the trigger “When a new response is submitted” for Microsoft Forms. Most often this trigger should be near the top if not in the first spot as you can see from Image 2. This is a testament to the popularity of this type of Power Automate flow.
Inside Power Automate Designer
At the time of my writing this post, Microsoft currently has two designers available to you for use. It will most likely default to the new designer, but you do have the option to switch between the previous designer and the new designer with the toggle in the top right corner. (See Image 3) You will be able to use either, but the location where you will do the configuration will vary slightly depending on which designer you are working it. For this post I will use the new designer.
Connecting Power Automate to Collect Your Microsoft Form Data
You will be greeted with a single box in designer as a visual reference to identify the trigger that is to start the Power Automate flow. Click the box to view the settings of the trigger. In the new designer, a flyout from the left will appear to allow you to make the configurations you need to make. You will be presented a list of Microsoft Forms that are connected to your account. In the case of this post, I have gone with the best practice and tied the Microsoft Form to a Microsoft Team called “Woodology”.
The easiest way to get the Microsoft Form ID is by opening the form in design mode and getting the ID information from the URL. Be sure to click on Custom Value at the base of the drop down to allow you to input the raw ID or the designer will not take the input otherwise. The ID information is at the end of the URL after “&ID=”. Please See Image 4 to understand what you should copy to place in the Power Automate trigger configuration.
Once you capture the ID of the Microsoft Form, place that into the appropriate text box under parameters. This will tie this Power Automate to collect data to the appropriate Microsoft Form and the following steps will be easy to configure. See Image 5 to see what the parameters text box for Form id looks like.
Add the Action to Get the Data
We may have taught Power Automate which form to collect the data from when we configured the trigger, but that is not enough. We need to have Power Automate get the data associated with the Microsoft Form next. To do this we will point just below the trigger box and click the + button that will show when we hover our cursor within the area. Search for the action “Get response details” associated with Microsoft Forms and click to add to your flow. (See Image 6).
Just like we have done with the trigger box, we will click on the action box “Get response details” to open the fly out to allow us to configure the action. Under parameters you will have two text boxes. The first is straight forward. It is looking for the Form ID again. Unless you copied something between the first two steps, the Form ID should be on your clipboard still. Click the drop down and select custom value, once selected, just pasted the Form ID. See Image 7 to see what it should look like.
The second text box is asking for the Response Id. That is also available to you without much difficulty. When you place your cursor in the text box, you will see a blue lightning bolt and function symbol show up at the end of the text box. Select the lightning bolt which will allow you to choose dynamic content. Only one thing will be available. Click Response ID and it will automatically fill in the text box for you. That takes care of the configuration for this action. (See Image 7)
Place the Collected Data into a SharePoint List
This is the final step to configure for this Power Automate to collect data and place it into a SharePoint list. Click on the + found below the previous action Get response details. Search for the SharePoint action Create Item and select it. It will be added to your Power Automate flow and will look like Image 8. As we have done for the previous steps, click the box so the left flyout to configure this action can be seen.
Initially, you will see only two dropdown options. They need to be configured in order. The first dropdown is to select the SharePoint site where the list is located. If you do not see the site, you have the option to select custom value where you can copy and paste the home page URL to the site that contains the SharePoint list. Once the site is selected, the second dropdown will show the lists that are available. Select the appropriate one and wait for a moment for the configuration box to add the columns from the list. (See Image 9)
Within each text field you will now add using the blue lightning bolt to add dynamic content. Each filed in SharePoint will expect the variable type you configured it for. In image 9, you can see Plot Location and Start_Date both were taken directly from the form. Size_in_Acres and Project_Cost both are numbers. In the form we declared them numbers and forced only numbers to be allowed, however, when passed to Power Automate, they are considered strings. We had to use the int() function to revert it from a string back into a number of which SharePoint was expecting. (See Image 9)
A Power Automate Best Practice
Once you create a Power Automate to collect data or any other type of functionality, be sure to share the flow with a Microsoft Team. You want to be sure any flow that is business critical will be able to run beyond your tenure with the company you are working for at the time. This will make sure your parting ways with the company will not leave your team in a bad situation. In this case I have added the team Woodology as a co-owner. (See Image 10)
Conclusion
Utilizing Power Automate to collect data is a creative way to capture data, without relying solely on spreadsheets. Excel has its place, but there are better solutions when dealing with potentially large data sets. Capturing data in a SharePoint list can open up a myriad of other possibilities, such as reports in PowerBI or an application built in Power Apps. Power Automate gives you a multitude of options at your fingertips to propel your business in a data driven world.