by One of the most used triggers in Power Automate if the Microsoft Forms is the “When a new response is submitted.” It will trigger when someone submits response in Microsoft Forms, but this will only return the response id. If you want the details of that response, you need the “get response details” action, and that’s what we’ll explore today.
Where to find it?
There are two ways to find the “get response details” action. The first is to go to “Standard.”
Find Microsoft Forms.
Then select the action.
Another way and a bit easier are to search for it. Then, if you start typing the “get response,” it will show up on the top of the results.
Power Automate tends to save the most common actions on the main screen, so check there before going through the full hierarchy. Also, you can use the search to find it quickly.
Usage
99.9% of the time, you’ll use it the same way. You’ll have a trigger for a new response and get the details of that response.
The only exception that I can think of is when you store the IDs of the responses for future reference. Then, when you need the data, you’ll do a “get response details” with that ID to fetch the data and parse it. But even this one I strongly encourage you not to do (see below in the recommendations).
Limitations
A huge limitation that confuses people is that all data is fetched as a string. So even if you define the field in Microsoft Forms as something else, Flow will only get strings. Here’s an example of a Form:
Now let’s look at the data returned:
{
"responder": "manuel@manueltgomes.com",
"submitDate": "7/28/2021 10:41:26 AM",
"r917f27f11e4b433c93bfad308c133b94": "[\"IT\",\"Finance\"]",
"rbce4c992fc4f4453b01567fe21e17099": "2021-07-28",
"re2c5f1cc0fc14413ad38347f3d5ef9c4": "4",
"r9d6a6441c0244a269fcafc4fdb879c16": ""
}
This is problematic for several reasons:
- You need to convert the data – data conversion is hard and may result in edge cases where you get invalid data. Please be sure to validate the data once converted and warn people or deal with errors when there is an issue.
- Arrays are strings – I’m not too fond of this, but arrays are also strings, as you can see above. Converting them is not that complex, but it would be a lot nicer to have an object returned where we could do a quick “Apply to each” action and fetch the information.
- Dates – dates are hard to parse. There are hundreds of variations on how the date is displayed, and there’s a lot of room for mistakes. Although the data is always returned the same way, storing it after may return in invalid conversations. Please be sure that you’re providing the correct date to where you’re storing it.
Another big limitation, but for a good reason, is that the fields have an ID and not a name. As you can see above the “Department” part is “r917f27f11e4b433c93bfad308c133b94”. Understandably it’s hard to generate fields since the question can be quite big and have special characters. But IDs make formulas harder to read and debug, so be very careful when dealing with the information.
Recommendations
Here are some things to keep in mind.
Name it correctly
The name is super important to know at a glance where we are getting the details from. Always mention somehow what is the Form that you’re using. Always build the name so that other people can understand what you are using without opening the action and checking the details.
Always add a comment.
Adding a comment will also help to avoid mistakes. Indicate the Form and why you’re getting the data. Knowing where the data comes from and how you’ll use it will make your life a lot easier, especially if you’re debugging your Flow.
Never generate the ID.
IDs may change, so don’t generate them. Storing them is also dangerous (see below) since, if things change, you lose access to your source data. I would strongly recommend that you use the references to previous actions to fetch the ID and use that in the “get response details” action.
Store it first and then use it
I’m not particularly eager to rely on Forms to store the information. Not because it’s unreliable but because it’s not meant to store data long-term. The information is there temporarily and may be deleted at any time. I see many people storing the Form’s ID and then fetching the data when they need it. There are two great disadvantages with this:
- You need to convert the data each time you need it. However, if you store it somewhere, you only need to convert it once.
- You need to validate the data each time you need it. Same as above, you only need to do it once.
- Once you receive the information, you don’t know when it will be available. For example, if someone deletes the Form or the answers, the data is lost.
- IDs may change. If they do, there goes the link to your data.
So my recommendation is always the same. When you receive the data, parse and validate it and store the converted values somewhere (SharePoint, Dataverse, database, etc.). The idea is to have it stored in a more permanent place where you can reference it later.
Always deal with errors.
Have your Flow fail graciously when the ID or some part of the data doesn’t exist. I recommend notifying someone that something fails so that the error becomes more visible. It’s horrible to have failing Flows in Power Automate since they may go unlooked for a while or generate even worse errors. I have a template that you can use to help you make your Flow resistant to issues. You can check all details here.
Back to the Power Automate Action Reference.
Photo by Kelly Sikkema on Unsplash
Nice intro into this, thanks.
Now I have a list with several entries of type Choice (inventory of computers and such, e.g. there is a vendor field with possible values like “Apple” or “Dell”). When such an entry is added, the response string (e.g. “Dell”) will create a new choice element “Dell”, even if the list already has one of the same name.
This happens only once, the next form with same reply will not create a third “Dell” entry. But in the end, all choice entries in the list would get duplicated. That’s quite a bummer, and I wonder how to fix this.
So the reason is that the returned string values have a “\n” appended, while the original values in the SharePoint list don’t.
This sucks! My workaround is to use a dropdown fro the choices, then it does not seem to happen.
And I was wrong, it still doesn’t work. Although other Choice fields work well, and I do not see any difference. But the trim() function seems to fix it.
Caveat: You can’t use the template to get a Microsoft Forms response and add it to SharePoint without ending up with the “List of response IDs” action that puts you in the middle of an “Apply to each” – even though you only have ONE response you are looking at. So don’t use the template. Do Automatic Cloud Flow and select “When a new response is submitted” for Microsoft Forms. After putting in the Form ID (or selecting your form from the dropdown), your “New Step” would be this “Get response details” action. Because the trigger gets set up with “Split On” and the triggerOutputs()?[‘body/value’], it will show you just the basic “Response ID” in the Dynamic Content like is shown above and won’t create the “Apply to each” condition. From there, you can assign the values to variables using the Dynamic Content – you don’t have to worry about the underlying IDs of the fields.