Using Power Automate To Extend Microsoft Forms

Microsoft Forms is part of the Office 365 suite of applications and is a simple way of putting together online forms to capture information such as polls, surveys or gathering information that would otherwise be received via a paper form. The app has a wide range of field types and can get quite involved so we aren’t going to look at all the features of Forms in this post.

The data for a form is stored within the app and can be exported to Excel. On the one hand, this is a powerful feature that makes analysing data more flexible but there are some limitations.

The main issue is that the Excel file is a point-in-time download of the results to date. For surveys that have a defined start and end date, this may be fine as you would wait until the completion date and then download the full set but if you are using it as a means of collecting information on an ongoing basis this will generate multiple copies of the spreadsheet and what’s more, any calculations or other data will be lost and need to be added back in each time.

There is also the slightly tedious fact that Forms will include data that may be of no use such as the ID of the form and the start and end dates of the submission.

To overcome this we can utilise the Power Automate (previously Flow) technology within Office 365 to drop Form data directly into a custom Excel sheet. As always the usual caveats around features within Office 365 and licences apply and that different tenancy setups may vary.

The first thing to say about creating a workflow such as this is that planning ahead will save a lot of time. Not least because at the step in the process where you add your spreadsheet to the flow you are stuck with the fields in the spreadsheet at that time, i.e. if you go back later and add another question to the form, thereby requiring another field in the spreadsheet, you will need to remove the step and re-add it.

So plan your form first so that you are happy with it, then create the spreadsheet. One shortcut here is to put sample data into the form and then download the results from Forms to give you a starting point.

It is worth saying that only the fields you are storing the form data in cause an issue, you can add other columns and calculations etc to your heart’s content.

For this example, I have created a form (to request firewall changes) and a spreadsheet that includes both the fields I want from that form and a few extras (some of which we will need for the next part of this series).

To join them up we now need to go to our Power Automate within Office 365 and create a new Flow.

In this example, I’m doing it from scratch but if you are unfamiliar with the technology I’d strongly suggest looking at the templates. These are many and varied and there is probably already one that does what you are trying to do (or at least put you on the right track).

To start we select a new automated cloud flow and then give it a name and tell it what triggers the next steps, in this case when a new response is submitted to a form.

Clicking create takes us to the flow creation process. To start with we need to decide which form we are interested in. The dropdown box will be populated with any forms that you have access to within your environment.

We now need to choose what we want to do when the trigger is fired.

Before we get to Excel we need to add another Forms step, namely getting the response details from the form ready for us to manipulate. To do this just click the new step button and search for Forms. This will surface a range of Forms triggers but we are looking for one relating to getting the response details.

As mentioned, the context-sensitive help will kick in giving you sensible options.

Now we have the form data we click +New step to move on.

In this case, we are wanting to deal with Excel so we will select the Excel Online option. It is worth noting that although we are using Excel here for simplicity we could equally insert this data into PowerBI or a SQL database (because yes I know that you shouldn’t use Excel as a database but come on, we all do it!).

Having selected Excel we are presented with a list of actions that we can use with the form data. One of the powerful features of Power Automate is that it provides pretty good context-sensitive suggestions as you go through the process.

In this case, I want to add a row to an existing Excel sheet. Having made that selection we get to choose which sheet is being used.

Although this may look a bit complicated all the drop-downs will be populated with information from your environment so going through the layers is actually pretty straightforward. Once you select the Table element it will automatically pull through the available columns. As mentioned above, to change this later is a bit of a faff so plan ahead.

We now want to decide what to insert into each column for the row (or which to leave blank for later). If we select the first row once again the context-sensitive help pops up with options.

One thing to watch out or here though is that the order in the help isn’t always the same as you have the fields in the spreadsheet so just make sure you know what maps to what.

As you can see, we’ve left some of the fields blank as those are ones we will be using later.

Going back to our context-sensitive help, there are more than just dynamic content options here. We can also access some of the functions from Excel to automate actions we might otherwise do in Excel later, for example, we can join strings together and insert them into a row so if the form asks for First Name and Second Name in separate boxes but we want to generate a formatted string with both, rather than do it in Excel we can do it here.

For now, we are done so all we will do is click Save. This will trigger the flow checker which will try and work through the steps and spot any obvious errors. This time we are all OK and we now have a flow ready to use.

We will look at adding some more steps to our flow in a later post but for now, please explore Power Automate and see what more it can do for you.