Saving a Microsoft Form Response with Power Automate

Previously, we covered sending an automated email when a new appointment is added. In that email, there’s a link to a Microsoft Form that we’d like the client to fill out. As standard, you can only view the responses directly in the browser in Microsoft 365, or download them to Excel.

In this article, we’ll cover taking a response and doing something more intuitive with it, such as saving the records to a data source, sending an email etc. I’ve built this use case more times than any other in Power Automate, especially when Covid hit. It was a quick win for replacing paper-based onboarding scenario’s.

A nice easy one to build with the wife then.

Table of Contents

The requirement

When I go to my Dentist, for a sports massage, or routinely at my Doctors surgery, I’ll be given a health questionnaire to fill out. I’m sure you’ll have filled out similar:

“Do you have a heart condition?“
“Do you have any allergies?“
“High or low blood pressure?“
“Are you taking any medication?“

Those kind of things. The wife needs to capture the same information; it’s a requirement for her insurance and may determine what products she uses for a treatment. Microsoft Forms is quite easy for that, we can create a simple form and embed the unique URL in the confirmation email.

We’d also like to somehow update the clients record in our client database. For that, we’ll ask the client to enter in their email address into the form. We’ll have captured that at appointment stage and this should be a unique identifier.

Get a Microsoft Form URL

For reference, we need the URL of the form to plug into our confirmation email. Access Microsoft Forms and build the form out as required. When done, select Collect responses from the top ribbon:

From the resulting pop-up, we want Anyone can respond as the form will be sent to anonymous users. We can shorten the URL too (not sure why this isn’t just the default??), then copy it to add to our confirmation email:

Here’s the HTML block in our confirmation email where the URL to the Microsoft Form lives:

URL purposely blurred out, as I have a particular set of friends that would likely spam it with responses!

Create a flow

I’m doing this from our solution again, so to create a flow that way it’s New, Automation, Cloud Flow then Automated:

If you’re not using a solution, then it’s a case of going to make.powerautomate.com:

As per the previous article, we’re selecting Automated cloud flow as we want an automation to occur, automatically, when something happens.

Whichever your creation method, the experience thereafter is the same. That means giving the flow a name and in this instance, selecting the When a new response is submitted trigger for Microsoft Forms:

Click Create and away we go.

Configure the trigger

Or, so I thought. You see, I foolishly thought we could make this dynamic using an environment variable. We’ve got 3 forms set up, one each for DEV, UAT and PRD – standard procedure. But the trigger action we are using doesn’t cater for environment variables:

Hhhmmmmm. It’s one for me to investigate further but in the present, time was of the essence. So we just selected the form we’ll use for Production purposes. It made me feel dirty not using an ALM-friendly method, I can’t lie.

In the drop down for the trigger, we’ve selected the relevant form we want latest responses from:

Get response details

The first ever time I build a flow for MS Form responses, this step totally tripped me up. I just assumed all of the response details would be in the body of the trigger, like most other Power Automate triggers? To get a response to an MS Form, you need to do an additional action to get the actual question-by-question responses.

In your flow, click on New step then search for and select the Get response details option. This is the first action we need after our trigger:

For the Form Id property, again select the same form from the drop down that you selected in the trigger. For Response Id, access the dynamic content for the field and select Response Id. This value comes dynamically from the trigger:

Typical data saving pattern

Once the trigger has fired and you’ve got the response details, a very typical design is to match each question and response to a data table. We’ll use SharePoint for this example.

Click on New step and look for the Create item action for SharePoint:

Select the relevant site and list you want to map the data to:

Give it a moment to resolve and it’ll list out all the available field in that list. Use dynamic content for each field to map a response to its corresponding field. You just need to make sure of two things:
> Make sure all mandatory fields are populated.
> Make sure you map the right field types together. For example don’t try to add a date in text format to a date/time field type.

This is a very simple, 3-step process to route a Microsoft Form response to a data source of choice. Have the flow trigger, get the response details, map to the responses to the relevant fields. It’s a super simple method that you can repeat over and over again.

If you’ve visited this article today, purely to get some help with routing an MS Form response to a data source, there you go! I’ll keep this part updated regularly if anything changes or is added that might impact.

For those following along with my wider series of building a business solution with my wife, please read on!

Extending the logic

Yeah, so we’re not going to follow the typical route here. We need some extra functionality for a start, but we’re also going to save the response slightly differently. Might have baffled the wife in a few places, but when she saw the end result she understood. Phew!

Build a JSON object

For our design, we wanted a way to make it easy to present the information. Rather than a separate table, could we save the information in the client database instead? I thought back to my blog around saving data as a JSON string, then using Parse JSON to visualise it in Power Apps. Power Automate responses are in JSON, so I’m putting 2 and 2 together here and thinking the response body of Get response details will just magically have the right structure/formatting for us.

So I ran a test of the flow so far and sent the response of the Get response details actions to myself:

				
					{
  "responder": "anonymous",
  "submitDate": "7/24/2023 7:54:53 PM",
  "r565eeb5e457b446abd11b8c2fbd010a9": "Phil",
  "r9b649234ebee428fa098c7d61804e196": "McCrevis",
  "rba532f9a221a4d62ba5de170fc8ce5ad": "The ghetto",
  "r471e615585f4474b9771680f0e27ea85": "555 444 1010",
  "rb5167115dbda40189c365009a4d6ee43": "phil.mccrevis@gmail.com",
  "raa315a1e82564404a03bfa3d1a0641fe": "Ghetto surgery",
  "rc3c16f478401425b89cdf0da62c1409c": "",
  "rf11765c823a047f8aff3858bae2137b6": "",
  "r40b85ac709124349a44ae88c9b61a2da": "No",
  "r6d393b78cf534fadbcd125b15b4a9830": "No",
  "r9c87adc9b18a4fc89c6bb7de5dacf434": "No",
  "re288ce1fd1164c63bbb933b474c96ec9": "No",
  "r76c33546a4bb4504977ce260be2a6ad1": "No"
}

Not what I was expecting; the questions come across as unique ID’s. Not sure my wife is going to know what response relates to what for the key health-related questions. We can build the JSON object ourselves though, with a couple of minutes work.

We’re going to build this action next, as it’ll useful in multiple outcomes later in the flow. No point in writing it twice.

Click on New step and add the Compose action:

We’re then building out our question & answer schema, few things to note:

The very start and very end are square brackets.
Each row of data starts and ends with curly brackets.
The column headers and answers are bookended with double quotes.
Using dynamic content to populate the responses needed.

Effectively, we are creating something that looks like this:

Question	Response
Name	Phil McCrevis
Date of Birth	01/01/2000
Mobile number	555 444 1010
Email address	phil.mccrevis@gmail.com
Doctors name and surgery	Ghetto surgery
Another form question	Another form response
And another	And another
etc	etc

Check if user exists

When an appointment is added, the client has to give us their email address – it’s mandatory. So if the wife has typed it in correctly, the client will then get the confirmation email that has the link to the consultation form.

We can’t do any fancy parameter pass-through’s or dynamic content in Microsoft Forms. The client therefore needs to provide their email address in the consultation form response. In theory, we should be able to match the response with the related client record but we need a failsafe if not. For example, maybe they type their email address incorrectly.

Our client records are stored in Dataverse, so we need to click New step then add the relevant List rows action:

We can then configure a filter in this action; list the rows where the email address matches the email address in the Microsoft Form response. For this to work, we need the Name of our email field, not the Display Name. We can access the Email address field via dynamic content, making sure it’s bookended with apostrophes:

The Filter rows config in focus:

				
					crb64_clientemail eq 'Email address'

If the count of records from the List rows action is 1, a match has been found, if the count is 0 then no match. We can analyse this by next adding a condition to the flow:

In the left-hand input box, we need the following expression:

We’re then selecting is equal to and adding 0 in the right-hand box:

If no match found

The wife needs the responses emailed to her directly, so she at least has a copy of them for reference.

We can add a Send an email action:

We’ll configure the body with the information needed, along with a sentence to say there’s no match to the responders email address:

We still need the JSON output built earlier though. Maybe the client mistyped something that’s caused the mismatch, but we still need their details saved against their record. Passing the JSON object into the email means we can potentially update the record manually:

If match found

In this case, we’ll want to save the response against the relevant client record. This is so the wife can view it in the app.

We are working with Dataverse in this solution, so need its Update a row action in Power Automate:

We want our Client Database table. The row to update will be the one successfully retrieved in the earlier List rows in Client Database for user action:

Next, we can add the output of our JSON object build to the relevant field for the clients entry:

That’s the flow side of things done. It’s very easy to take a response to a Microsoft Form and build an automation. MS Forms are super quick to put together but their native abilities for viewing responses is lacking. This is a simple yet very common use case and one of many where Power Automate excels.

Viewing responses in our app

As a result of this automation, the wife wanted two additional things in the app:

> Visual indicator for whether a client has completed their form.
> Ability to view the consultation form response in full.

Visual indicator

Our visual indicator sits in the top-right of each appointment block in our calendar. If the related client has completed a consultation form, we’ll set the fill of the indicator to green, else red:

To achieve this, we’ve added a Circle shape to our gallery:

The following properties of the circle need updating:

Property	Value
DisplayMode	DisplayMode.View
Height	20
Width	20

We only want the circle to be visible if the label for Client Name is also visible. The logic for this was defined previously. Therefore, we can set the Visible property of the circle to reference the visibility output of the Client Name label:

For the Fill property, we can lookup to the client database for the client in question. We can then determine whether the related field for the consultation form response is blank, and format accordingly:

				
					With(
    {   
    // Does client have Consultation form response saved or is it blank
        tvClientConsForm: IsBlank(
            LookUp(
                'Client Databases',
                ClientEmail = First(ThisItem.Appointment).crb64_appclientemail
            ).ClientConsForm
        )
    },
    // Conditional formatting based on output from With() 
    If(
        tvClientConsForm,
        gvConfig.Negative, // Red
        gvConfig.Primary // Green
    )
)

View response

We’ve added an additional icon in our client database screen. When clicked, we want to show the consultation form, stored as JSON, in a nicely formatted gallery.

We’ll configure the OnSelect of this icon to build a collection with the data. This requires using the ParseJSON function that I’ve done a separate article about here. When the data is converted, we’d like to be able to see it on screen. Here is the Power Fx in the OnSelect property of the icon:

				
					// Build collection with consultation form response for client
ClearCollect(
    colClientConsultationResponse,
    ForAll(
        ParseJSON(ThisItem.ClientConsForm),
        {
            Question: Text(ThisRecord.Question),
            Response: Text(ThisRecord.Response)
        }));
// Show modal with gallery to display details
Set(gvClientConsForm, true)

For reference, here’s an example of that collection now built. We’ve taken the JSON object previously created in the Flow when handling the MS Form response, and used it to build a typical table structure.

We’ve added a container to the app that will only be visible when the icon is clicked. Within the container, we’ve added a gallery and set the Items property to the collection we’ve just built:

Hopefully this article has demonstrated a couple of different ways you can handle Microsoft Form responses. Granted, the JSON method perhaps isn’t as common place as just adding the responses to a data table, but it shows that as with most Microsoft products – there’s multiple ways to skin a cat.

The next article will bring this series to end, looking back on this case study and sharing findings and opinions.