Unanswered

Power Automate Flow to filter out blanks from MS Form submissions and only include answered questions - Not working

(0) Share

Report

Posted on by trt79

Hello,

I am attempting to build a Power Automate Flow that will email only the answers from questions and not blank or empty fields.

I have looked at other threads on here that appear to have the answer but it will not work for me. Attached screen shots showing what I am attempting to acheive:-

Flow.png - The Flow in detail

email.png - What is emailed to me once a MS Form is submitted

The 'Compose' variables inputs in the flow are:-

if(equals(outputs('Get_response_details')?['body/ra8c7bc18d28b4d069116548b45a6454e'],''),null,concat('Question1: ',outputs('Get_response_details')?['body/ra8c7bc18d28b4d069116548b45a6454e'],'<br>'))

if(equals(outputs('Get_response_details')?['body/r7c31be52dcc74bbb8d24591d7045fccd'],''),null,concat('Question2: ',outputs('Get_response_details')?['body/r7c31be52dcc74bbb8d24591d7045fccd'],'<br>'))

if(equals(outputs('Get_response_details')?['body/rc46a442e84ad4e4e80833653bfe07da5'],''),null,concat('Question3: ',outputs('Get_response_details')?['body/rc46a442e84ad4e4e80833653bfe07da5'],'<br>'))

Thanks

Tim

Categories:

Building flows

email.png

Flow.png

I have the same question (0)

All responses (7)

Answers (0)

Chriddle 8,436 Super User 2025 Season 2 on at

Like (0)

Report

https://powerusers.microsoft.com/t5/Building-Flows/MS-Forms-filtering-blank-answers/m-p/2497392/highlight/true#M279267

Was this reply helpful? Yes No
trt79 6 on at

Like (0)

Report

Hi Thanks for the link, however, I still do not understand how to fix this issue. Surely its just an easy bit of code?
Cheers
Tim

Was this reply helpful? Yes No
ChadVKealey 1,393 on at

Like (0)

Report

Is there an Azure AD or M365 group that all the Contractors are members of? If so, you could customize the permissions of the list to give that group the appropriate access. If not, you could create a group. If you'd only need to use/reference this group in that SharePoint site, then you could create it as a "SharePoint group". If there's a chance you (or someone else) will use it somewhere else, then it'd be better to create an M365 group.

In either case, you can use Power Automate to add users to the group. For a SharePoint site group, see https://powerusers.microsoft.com/t5/Building-Flows/Adding-users-to-a-SharePoint-Group/m-p/333727#M36059. For an M365 group, you can use the "Add member" action in the Office 365 Groups connector.

Was this reply helpful? Yes No
trt79 6 on at

Like (0)

Report

Hi Chad,
Sorry I don't understand. I just need a solution to what I think is a very simple issue...

Surely someone knows how to generate only the questions and their answers (no blanks) from a completed MS Form and insert this into the body of an email.....?!!

Thanks for your help thus far 🙂

Tim

Was this reply helpful? Yes No
ChadVKealey 1,393 on at

Like (0)

Report

Tim,

I'm sorry for that reply that has nothing to do with your issue. To be honest, I composed that as a reply to a post on Facebook, so I have no idea how it got here.

Anyway, since I'm here, I'll take a shot. The first thing to understand is that things are almost never as simple as they seem in Power Automate. Most people underestimate the complexity of a process they're trying to automate.

Now, to your specific situation, the "Get response details" action in Power Automate will return an output ("body") that contains, well, the details of the response in the format of a JSON object. JSON objects are made of key : value pairs. This is the body of a submission to a form with 5 questions:

{
  "responder": "ckealey@zf2rm.onmicrosoft.com",
  "submitDate": "5/1/2024 10:57:31 AM",
  "r2736b586f325499b8ac187a976b49572": "Arthur of Camelot",
  "r6c15f37c4a124bef9bbf55f881255557": "I seek the Grail",
  "rad2d2a97ade240ad9314f8a353ca6d6f": "Yellow",
  "re1f4b0d6028243aeb502e77c62df0f44": "",
  "r8665fcd605e14215acad86b870d19d09": ""
}

So, you'll notice the text of the questions isn't there. Just the "internal question ID" (that big ugly string of characters). Also, every question's response is shown, including those that weren't answered (the blanks, which are the last two in the example above).

While there is a built-in action to filter array data, this data is in object format, so that wouldn't work. So, what you would need to do is use an expression to "collect" each question's response, if it exists, into an array. There are various ways to do this, but all of them require that you "handle" each question individually. That is, there's no simple "return all the non-blank answers" action.

Was this reply helpful? Yes No
trt79 6 on at

Like (0)

Report

Hi Chad,
Thanks for your reply and information. I can already collect each response, but what I am struggling with is collecting the question associated with the answer. If I can just collection the question and the answer too, then that would be problem solved. How would I collect the question?

Thanks again,
Tim

Was this reply helpful? Yes No
ChadVKealey 1,393 on at

Like (0)

Report

Unfortunately, that's the thing...you can't. When you're looking in the "Dynamic content", you'll see the text of the question, but in terms of the data that's actually available in the flow (for example, to populate an email), the question text just isn't there. Each question is identified using that big ugly alphanumeric string.

Actually, there is a way to get the question text, but it's not trivial. You'd need to use an HTTP request action. See https://mofumofupower.hatenablog.com/entry/formsapi for details. Disclaimer: I have not tried this myself, so if you have issues doing it, I'm not the best person to help.

Was this reply helpful? Yes No