Power Automate: Save multi-choice Microsoft Forms

The team ran into this issue while parsing multi-choice Microsoft Forms elements into SharePoint using Power Automate. The problem is described correctly in the Power Automate Forum Question that asks why can’t we get the value from a multi-select field in Microsoft Forms and send it to SharePoint?

Since we don’t know which options the user will select, we need to use the dynamic field in Power Automate to provide the fields that come from Form and cannot use the dropdown where we pick the options.

The first try

If I have a form like this?

I should be able to do this on Flow, and it would work, right?

Let’s look at the SharePoint column configuration:

I have the same options in SharePoint; when I run Power Automate, all goes well, but then looking at SharePoint, it’s empty. Why?

The issue

The issue is simple, and you can see it right away in Power Automate’s history:

I don’t have an option with the value[\"Option 1\",\"Option 3\"]. To understand the issue, we need to know something about Microsoft Forms. Everything that comes as a reply is a String, regardless of you configure in Microsoft Form. For example, if you set a multi-choice Microsoft Form as we did, Microsoft Forms returns a string with an “array” of elements. It’is not usable for us since we need something that Power Automate understands and can send to SharePoint.

Checking the format

It’s simple to check the format that we need. In the “Create Item” pick two items like so:

… and we get:

Quite a difference, right?

So we have a JSON array with two key/pair elements, and we need to get there.

So now we know the solution. We need to transform:

[\"Option 1\",\"Option 3\"]

into:

[
  {
    "Value": "Option 1"
  },
  {
    "Value": "Option 3"
  }
]

Before we start

I have to warn you that this is a workaround. We need to parse a string to a JSON format, and so we need to do some work. To make it simple, I’ll break it down into steps, so it’s easier to understand. It may look complicated, but once you know each stage it’s quite straightforward.

Here’s the overview of the Power Automate:

Don’t try to build it yourself. Go to my template section and download it.

Get the “clean” options

First, let’s lean the string that we get. To do that we’ll use our friend the “Compose” action:

And the formula is the following:

replace(replace(replace(body('Get_response_details')?['re96ff20a4f3d44ec9485b8250dbbc748'],']',''),'[',''),'"','')

SCARY!!!  🙀

Not really what we’re doing is the following:

  1. Get the value from the body:
[\"Option 1\",\"Option 3\"]
  1. Replace the value “[” with nothing. We get:
\"Option 1\",\"Option 3\"]
  1. Replace the value “]” with nothing. We get:
\"Option 1\",\"Option 3\"
  1. Replace the “ with nothing. We get:
Option 1,Option 3

Not bad. Now we have a comma-separated string, so it’s time to generate an array to parse the information better.

Form an array

Our trusted “Compose” action will allow us to generate an array quickly:

Formula:

split(outputs('Removes_the_Characters'),',')

The formula is quite more straightforward than the previous one. Split the string using the, as the separator. You’ll get something like this:

Looking good. Nice and clean array. Now we can iterate in each element of the list and generate the values in the format we’ve seen previously.

The variable

Before we can generate the information in the format that Power Automate wants, we need a place to store it. To do that, we can create a variable:

Why an array and not a string? Because Power Automate expects an array of objects, so we can use the variable to do the conversion work for use. If we used a string, we would need to concatenate a lot of characters to simulate an array, and that would be quite an error-prone.

The final step – Bringing all together

We need to form the tuple that Power Automat expects:

Value : "Option X"

Here’s the step

And the formula is the following:

json(concat('{"Value":"',items('Apply_To_Each'),'"}'))

We’re concatenating the tuple as requested in a JSON format and then converting it into a JSON object to append to the array. Each value of the array will look like this. It looks familiar, doesn’t it?

Save to SharePoint

Ok let’s save this array to SharePoint:

When we run the Power Automate, we’ll see something that looks a lot better.

Perfect format.

Now let’s check SharePoint. Fingers crossed 🤞

If you’re suspicious that the options are not selected let’s edit the element:

It works 🎉

Take home message

Microsoft Forms is a fantastic tool, but we have to be aware of these tiny topics that can complicate a little bit of our life. Multi-choice Microsoft Forms are widely used, but I’m sure that you ran into this issue in the past. Hopefully, the solution is clear and useful for you.

Again, you don’t want to build all of this yourself! You can jump to my template section and grab the template already done for you.

Have a suggestion of your own or disagree with something I said? Leave a comment or interact on Twitter and be sure to check out other Power Automate-related articles here.

Photo by Nathan Dumlao on Unsplash

Manuel Gomes

I'm a Project Manager with experience in large projects and companies. I've worked in the past for companies like Bayer, Sybase (now SAP) and I'm currently working for Pestana Hotel Group.

View all posts by Manuel Gomes →

Leave a Reply

%d bloggers like this: