Using Microsoft Forms with Power Automate/Flow (and SharePoint, PowerApps, Excel and beyond)

Microsoft Forms is a dead-simple UI interface to allow end-users the ability to generate and distribute surveys to internal and even external users.  It isn't horribly complex and the number of things you can do is vastly dwarfed by the number of things you cannot.  However, it is good enough to get some basic Q&A from users w/o bringing a web developer into the equation.

However, what do you do w/ the survey data?  How do I save this data "somewhere else"?  What if I want to include some higher-level logic?

Once you start to understand the pieces of Microsoft's o365 offering you will start scratching your head about how it all connects together.  There are always questions of how/why you should do to integrate X with Y.  But in almost every single scenario, the answer is: do it in Power Automate (Flow).

Microsoft Forms Basics

Let's take a simple survey that I've created with a single Yes/No/Maybe answer:
'

Did you know that They Live is being re-released in 4k?

When users submit their data you are given a handy-dandy pre-created view into the responses where you get some basic analysis and the ability to export to Excel.

Simple enough for an end-user to do most things

Most simple surveys for basic use are covered by this basic scenario.  People submit their answers and the person who created it can view some simple graphics, breakdowns of %'s, even individual responses in detail can be viewed (and user information if you choose to include that vs. being anonymous).  The data is automatically attached/stored within the application framework and tied to the specific survey you create.

If you want to play w/ the data a bit, then you can open it in an Excel workbook and save it locally or to OneDrive.  This doesn't link new submissions to the workbook, but you can at least view the current submissions and perform data functions in Excel that you might be more familiar with.

Ultimately, everything is ridiculously simple and covers the majority of use-cases.  

However, if you think things through, then you might start wondering what the limitations are on this kind of a design.  Well, the basics are:

• Surveys are limited to 95-98 questions (depending upon the type of answer)
• Total responses allowed are limited based upon your o365 license (1000-50000)
• A "response" consists of a single submission (of up to 95-98 questions/answers)

For more detail you can see some additional Common Questions on Microsoft Forms.

All of that is great, but what if you want to automatically put this data "somewhere else" for other applications or workflows to act on?

Setting up your SharePoint Online list

Most people's first stop for "somewhere else" on Microsoft Data migration when working on m365/o365 is SharePoint Online (unless the Somewhere was SharePoint originally).

It isn't 100% automated on writing this data to SharePoint in that you still have to create a SharePoint list.  You don't have a straightforward method to export/import questions/answers from your survey to generate all of the settings for a SharePoint list.  

So if you have 90 questions you created in Microsoft Forms, then you'd need to manually create 90+ Fields in SharePoint to track those items.  Lame.

One "best practice" kind of thing is that you might be better off in creating your SharePoint list to be completely generic text fields.  For example, if you have a question that allows for answers of Yes/No/Maybe (as in my example), you should probably just create a standard Text field in SharePoint.

That is not to say that you can't do it as a SharePoint | Choice field, but that if you do this, then you need to ensure your choices are always identical and/or allow manual entry of alternate values.  In the end, if this list is just about aggregating information to share elsewhere, then it is best to just make it Text and manage the data inputs via your Forms.

For the most part, you can either create this via Teams using a "Microsoft List" or directly within SharePoint from the main page of your site by clicking New | List.  Either way, what gets created through either approach is a SharePoint List that either has simplified security through Teams (although you can dig into the SharePoint security settings and override them) or through your SharePoint site.

One final note, be careful if you use the Title field (created by default and required for entry by default) to store data.  It is limited on size (255 characters) but due to some special formatting that you cannot (easily) change this takes you down to notably less (~110).  You can either edit this and remove the Title field (not recommended) or make it no longer required (recommended) and simply don't populate it.

Linking Microsoft Forms to Microsoft Power Automate

As I said above, the answer to most integration questions in o365 is:  Power Automate (previously known as Microsoft Flow).

Want your Excel data to go to SalesForce?  Microsoft Power Automate.

Want to post to Teams whenever a person fills out a form to get on the agenda for an upcoming meeting?  Microsoft Power Automate.

Want to SFTP any emails from your boss to secured offsite location to prove they are sexually harassing you?  Microsoft Power Automate.

So many connectors and logic options are already in existence and more are being created.

To connect the original example above to Power Automate, go to https://make.powerautomate.com/ and create a new flow from a template (a Forms -> SharePoint template already exists) or from scratch (and search for Forms as the initial Trigger).  

So many Triggers/Connectors

If you choose the included Template for writing to SharePoint, then it really just comes down to filling in the blanks.

Just pick your options from the drop-downs for the most part

Once your Save your work here, the Flow is live and the next submission on the Form will wind up in SharePoint as well.

Voila!

Important Things to Remember

1. The data in SharePoint is a copy not the original of the survey data in MS Forms.  Deleting things from inside the MS Forms interface does not impact the SharePoint list (same for vice-versa).
2. The original limits on response storage still apply in MS Forms.  While SharePoint can store more entries, you might still need to occasionally remove items from your MS Forms individual responses inside MS Forms.
3. This isn't limited to SharePoint.  As stated above, once this data arrives in Power Automate, you can send it nearly anywhere.  You can use the generic HTTPS connector to send it to a custom application.  Use the Premium SalesForce connector to directly tie survey responses to Customer Records.  There simply are a ridiculous # of connectors inside PAuto so once the data gets there...
4. Hey, isn't this a PowerApps blog?!  Well, what if you wrote this very basic survey information from a survey to SharePoint, then sent the user/customer submitting it an email that included a link to a MS PowerApp with an inline parameter key passed to continue some followup questions that required higher-level logic than a simple MS Form?  Your PowerApp would have full access to the survey answers and could deliver a context-sensitive experience to the user with much more advanced logic.

Final Thoughts

Using MS Forms to provide a generic and simple method to enable business-facing users to create their own surveys and forms is a very easy method to pass off the basic workflow choices to non-IT staff.  They can determine what information is important and how to display it to the recipients. No comment on the wisdom of that in most business environments.

However, it does create a simple transition of role/responsibilities for data gathering, to data reporting on a larger scale.  Simply sharing edit rights w/ another user grants that person the ability to link MS Power Automate to events from your survey and to further enhance the user experience.

I think understanding the role of MS Forms in this scenario is important and a key to getting end-user adoption of the o365 ecosystem.