Building a SharePoint List – Why?
This question is legitimate if you want to know what is beyond the current functionality of Microsoft Forms. It’s true that capturing data is what Microsoft Forms is all about. However, we may need more than the downloadable spreadsheet with the captured data. It is possible we need something more robust, more automated, more malleable. It could be possible; we are looking at the limits of what Microsoft Forms can do. (See: Form, question, response, and character limits in Microsoft Forms). Another possibility could be we want to automate an entire business process, with the Microsoft Form data as the starting point. The reason could even be a need to build a PowerBI report on the collected data in the SharePoint List without the need for manual intervention. It will be helpful to know your reason.
Soil Enrichment Form
How to Build a SharePoint List to Capture Microsoft Forms Data
In our fictitious company for reforestation, we have a Microsoft Form for Soil Assessment. (See Image Soil Assessment Form). We want to capture the data the form collects and put it in a SharePoint list. Before that can happen, we need to build the actual SharePoint list the data can fit in. In our Soil Assessment form, we can see there are five questions. We need to build a list in SharePoint that mirrors the form. If you never have created a SharePoint list before, be sure to check out my video Creating a Simple List on YouTube to understand the basics.
Creating the List
The simplest way to create a List is by going to M365, clicking on the waffle in the top left corner of the page and selecting Lists app. If it is not there, click on more apps and find it in the list of all M365 apps available to you with your current license model. Be sure to give a clear name to your List using the underscore instead of spaces. (*Best Practice Alert*) You can always take out the underscores and put spaces back in once the list is created in the list settings. Once you have created your blank list, it is time for the next step.
Adding the Columns
With a brand-new blank list in SharePoint, you already have one field. Title is the name of the field. If you want to know about proper building of columns for a SharePoint List or Library, watch my video Best Practices Imperative for the Creation of SharePoint Columns. Feel free to select the first or most recognizable text question from your form. In our example, the Soil Assessment form, question 1 seems our best option. For the rest of the questions, we will need to make a new column. Renaming the Title field is fine. Just remember in Power Platform it will still be referred to as Title despite the name change. Below is a list of how I built my columns for the case of Soil Enrichment. Below that is an image of the Soil Enrichment List Columns.
- Question 1: (text answer) Title –> Renamed to Plot Location
- Question 2: (choice answer) –> Recommended_Enrichment
- Question 3: (number answer) –> Size_in_Acres
- Question 4: (number answer (currency in SharePoint list)) –> Project_Cost
- Question 5: (date answer) –> Start_Date
Soil Enrichment List Columns
Please notice in the image Soil Enrichment List Columns, I took the time to remove the underscore from the column Recommended Enrichment. (*Best Practice Alert*). I used a shortened name for the column and used underscores instead of spaces. This gives us easier column names to work with when we want to work with the SharePoint list through the Power Platform and more. I want to bring your attention to the fact that I ensured to select the correct column type thinking of what information I was gathering with my Microsoft Form Soil Enrichment.
A SharePoint List to Capture Microsoft Forms Data is Complete
Once you have gone through these steps, you have finished the creation of a SharePoint List that is capable to hold the data captured by your Microsoft Form. This will open up many possibilities to use the data you are capturing with the forms you create in Microsoft Forms. Creating a list and the columns within the list utilizing best practices will pave the way for simplifying using data in workflow (Power Automate), reporting (PowerBI), and applications (Power Apps).