This article is designed for Microsoft 365 (M365) professionals who are already familiar with Microsoft Forms (MS Forms), SharePoint Online (SPO) Lists, and Power Automate (Cloud Flows). It addresses a key question: why transfer MS Form data to a SharePoint Online list when the connected data is already accessible through the attached Excel Online workbook?
The answer lies in flexibility and adaptability. There is no single "right" way to handle MS Form data. The best approach depends on your specific business requirements, continuity plans, available tools, and the capability to use them effectively. By transferring MS Form data to an SPO list, you unlock several strategic advantages that can enhance data management, collaboration, and automation.
This article outlines six key reasons to consider this approach:
- When using attachments in the new SPO list form experience is not feasible.
- To leverage SPO list site columns as metadata for a multilevel approval process.
- To apply tagging, sorting, and filtering through SPO list site columns.
- To save MS Form questions as list items and files as attachments in an SPO list.
- To avoid sharing MS Form file uploads stored in the form creator's OneDrive.
- To eliminate the need to move the MS Form to an M365 group for collaboration.
Let’s explore each of these advantages in detail.
When Using Attachments in the New Spo List Form Experience Is Not Feasible
At the time of writing this article (Q4 2024), it was not YET possible (on some tenants) to add attachments via the new Forms experience directly from the SPO list "Forms" tab. This is due to change very soon with a new release from Microsoft of the "Attachments" form field for adding one attachment field directly to the new form experience.
These releases happen on a tenant-by-tenant basis. Some tenants in certain regions may already have these updates, while others will receive them at different times throughout the year. The available fields are shown in Figure 1 below.
Figure 1: New "Forms" experience for SPO list
Note:
You can add only one attachment field in a form. If your form includes multiple questions or statements requiring attachment fields, you may still prefer to use the MS Forms experience available at https://forms.office.com.
Tip:
MS Forms is an efficient tool for quickly collecting data from surveys, quizzes, and requests within an M365 tenant. It is often simpler and less time-consuming than building a Canvas or Model-driven app, which may require ongoing maintenance and support.
To Leverage SPO List Site Columns as Metadata for a Multilevel Approval Process
Once again, SPO list columns come to the rescue. You can create a column for "Level 1 Approver(s)," another for "Level 2 Approver(s)," and so on, as needed. The number of the level corresponds to the specific stage of the approval process. These columns can then be utilized in an automated Power Automate Cloud Flow to implement a multilevel approval process.
In addition to the approval columns, the other columns should ideally align with the questions you intend to collect from the MS Form. You may use SPO site columns or standard columns, with site columns being reusable across other lists within the same site. This approach is a best practice for indexing, as site column content appears in search results. The columns for the SPO list are illustrated in Figure 2 below.
Figure 2: SPO list - settings
To Apply Tagging, Sorting, and Filtering Through SPO List Site Columns
Metadata is information about data. The best practice is to use SPO list site columns, though normal list columns work too. Tagging each item with relevant data provides comprehensive details. When the right columns are filled, you can easily sort and filter SPO list items, as shown in Figure 3.
Figure 3: SPO list filter panel
To Save MS Form Questions as List Items and Files as Attachments in an SPO List
An automated Power Automate Cloud Flow will be used to save and send file attachments from an MS Form to the respective SPO list item. Before creating the Flow, you must create the SPO list and the relevant columns. This will not prevent or stop the default and automatic behavior of saving the MS Form data to its attached Excel Online workbook.
When you submit MS Forms data, it is stored in the connected Excel Online workbook. To access it, click the "View responses" button at the top of the form in Edit mode, as shown in Figure 4.
Figure 4: MS Forms "View responses"
An automated Power Automate Cloud Flow is needed to process and save MS Forms data into a SharePoint Online (SPO) list item. It should be an Automated Flow within a solution. This allows deployment across environments—Development, Testing, and Production—while following Application Lifecycle Management (ALM) best practices, as shown in Figure 5.
Figure 5: Power Automate > Flow settings
As illustrated in Figure 5 above, the right panel of the settings page displays the "Connection References." The Flow utilizes three main connections:
- Microsoft Forms (MS Forms): Used to receive communications and process data.
- SharePoint: Provides authentication and permissions, and facilitates sending communications and data.
- Mail: A third-party connector that requires authentication in the background.
- OneDrive for Business: Used to copy files from MS Forms based on submission IDs.
- Approvals: Utilized for creating and updating tasks.
Tip:
Connectors are the core of Cloud Flows. They allow workflows to span across different systems.
Note:
The purpose of this article is to provide an overview, rather than a step-by-step guide, on how to create and execute a Cloud Flow in the Power Automate portal.
In the Cloud Flow, the MS Forms connection retrieves data using triggers like "When a new response is submitted," followed by actions such as "Get response details" and "Create item," as shown in Figure 6. The rest of the Flow involves creating variables and setting "Approvers" to manage approval levels, which is beyond the scope of this article.
Figure 6: Power Automate > Cloud Flow design view
The Flow has many steps and actions within it. This is beyond the scope of this article. The full breakdown of the Flow is in the Business Process Model and Notation (BPMN) diagram as illustrated in Figure 7 below.
Tip:
A BPMN enables the business to understand its business processes and procedures graphically, communicate these processes and procedures in a standard manner, help identify bottlenecks, and facilitate improvements.
Figure 7: Cloud Flow - BPMN
To Avoid Sharing MS Form File Uploads Stored in the Form Creator’s Onedrive
There is no need to manually share files uploaded through an MS Forms submission. When a form includes a file upload question (single or multiple files), the attachments are stored in the form creator’s OneDrive. These files are specifically saved in the "My files" > "Apps" > "Microsoft Forms" folder, as shown in Figure 8.
Figure 8: OneDrive > "My files" > "Apps" > "Microsoft Forms"
Storing collaboration files in your OneDrive is normally not good practice. The reason is that it is meant to be your personal drive for files and not for collaboration.
This doesn't mean you should not or cannot share files. When you "Add collaborators" to an MS Form, the users you add will also be granted access to the folder (named after the MS Form) that hosts the files in the creator's OneDrive. As shown in the screen grab above, the "Activity" column will indicate actions such as "You shared this file."
Additionally, you can manually add more users to the OneDrive folder without necessarily including them in the MS Form collaboration. To "Add collaborators," access the "Collaborate and Duplicate" link in the form ellipses, which reveals the "Collaboration" panel while the form is in edit mode, as illustrated in Figure 9 below.
Figure 9: MS Forms "Add collaborators"
To Eliminate the Need to Move the MS Form to an M365 Group for Collaboration
For collaboration, the best practice is to move the MS Form into an M365 group where collaborative efforts can take place. However, if you move an MS Form and plan to use a Flow to process data, you will need to replace the OneDrive connectors in the Flow with "Send HTTP Request" connectors where applicable. This approach is more complex than using standard OneDrive connectors.
Figure 10 below illustrates how to select the MS Form of interest and "Move to a group."
Figure 10: MS Forms "Move to a group"
Important:
M365 Groups is the foundational membership service that facilitates teamwork across Microsoft 365. With M365 Groups, you can provide a group of people access to a collection of shared resources.
Moving the MS Form to an M365 Group is not required. You can keep the OneDrive connectors and save the data to a SharePoint Online (SPO) list. This approach allows you to use site columns to store answers as list metadata, which makes sorting and filtering easier.
Each MS Form submission is represented by an SPO list item, with each item containing the file attachments from the MS Form. The SPO list can exist on any SharePoint Online site, provided you have the necessary permissions for that site.
Final Word
This article has provided valuable insights into key features and best practices for integrating MS Forms with SharePoint Online lists. You’ve explored the new "Attachments" field in the SPO list "Forms" experience, learned how to configure and filter SPO list settings effectively, and understood the role of Cloud Flows in automating processes.
You now know how to view MS Form responses in edit mode, locate attachments stored in OneDrive, add collaborators to your forms, and move forms to a group for seamless collaboration. With these tools and strategies, you can optimize your use of Microsoft 365 for efficient data management and teamwork.
