by 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:
- Get the value from the body:
[\"Option 1\",\"Option 3\"]
- Replace the value “[” with nothing. We get:
\"Option 1\",\"Option 3\"]
- Replace the value “]” with nothing. We get:
\"Option 1\",\"Option 3\"
- 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 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
What if I have many multiple select field, do reiterate the above for each?
Hey Melissa,
Indeed, but you can import the template and use it for all of them to not need to replicate the code. Just import the template and use it as a “function” within your Power Automate.
Cheers
Manuel
Thanks… seriously. I take it that the native ‘parsing json’ function wouldn’t remove some of the steps?
Hi Chase,
Honestly, I can’t think of one when I wrote this article, but one may exist.
Do you have an idea? I would be more than happy to improve the article :).
Cheers
Manuel
Hi Manuel,
I have a MS form that has a question in it where the end user can select multiple answers in the question. I’ve created an automated flow that creates a planner task. For each answer in the MS form question I’d like to have a tag applied to it in the Planner task however, because this is a ‘multi answer’ question in the Form, the automated flow isn’t tagging ‘any’ of the answers in the planner task in relation to this MS form question.
Is there a way to set the automated flow so that when someone selects multiple answers in this MS form question, the planner task that’s created will add the relevant tags?, eg if they select 3 of the 8 options (in the MS Form question), then the automated flow will add the 3 relevant tags to the planner task that is created from the form submission.
Hello Manuel,
I seem to have a problem on the ‘apply to each’ section. I keep getting the following error:
The execution of template action ‘Apply_to_each’ failed: the result of the evaluation of ‘foreach’ expression ‘@outputs(‘Generates_the_array’)’ is of type ‘String’. The result must be a valid array.
My form has a choice question with six or seven choice. When it get posted to SharePoint it has all the brackets, quotes, comma and others. I can post the response data with the flow I created, though when I try with your template I keep getting stopped at ‘Apply to each’ with my Flow.
Hello Mark,
It looks like you have a string in the “Apply To Each” action. Can you please check, in the definition of the variable ‘Generates_the_array’, that you’re defining it as an Array and not a String?
Cheers
Manuel
I did this for a while and was baffled – I had copied one of the bits of text in one of the compose steps, but pasted in with the “dynamic content” left menu up instead of the right “expression” where it understands that you are trying to enter code. Cheers Manuel incredibly helpful guide
Hello Mark,
It looks like you have a string in the “Apply To Each” action. Can you please check, in the definition of the variable ‘Generates_the_array,’ that you’re defining it as an Array and not a String?
Cheers
Manuel
I’ve check and it is set up from an Array. There in nothing in ‘Initial Value’ though. Thanks for your Assistance.
Does it work now?
No worries, feel free to ask for any help you need
Cheers
Manuel
Thank you for all the assistance. It work perfectly in SharePoint. My next task is to remove the same characters from the Send Email V2. that I’m sending as a confirmation.
Well done Mark!
Hi Mark!
This is an excellently written solution for single incoming items. I’m inheriting an old SharePoint list which is approaching the 5000 limit. My goal is creating a purge that moves all list records over 90 days to an excel sheet stored in a file container so as not to lose historical data. I have everything working except the columns with multiple choice checkbox arrays. I can’t seem to find a solution that achieves this… do you happen to know how to achieve this? I would gladly send you screenshots of what I have working and I and my associate have worked on a solution by scouring the web and brainstorming for 2 days. (One of which was a 12 hour day of NOTHING but trying to solve this…) In any case I appreciate anything you might offer to help.
-Thank you,
Mike
Apologies Manuel Gomes, looked at the wrong portion of the thread and called you Mark. My bad!
No worries 😀
Hey Mike,
I think we can figure this out. Yeah, please send me the screenshots (if you want, you can email them to me), and we’ll build from what you have.
Cheers
Manuel
Hi Manuel,
I get the following error when trying this (for a selected file in SP adding some options as inputs to the corresponding multi select column).
Unable to process template language expressions in action ‘Removes_the_Characters’ inputs at line ‘1’ and column ‘2515’: ‘The template language function ‘replace’ expects its first parameter ‘string’ to be a string. The provided value is of type ‘Array’. Please see https://aka.ms/logicexpressions#replace for usage details.’. Any ideas?
Thanks
Hi Cheryl,
For some reason, the function is getting an array as the first element. Is the item in the “body(‘Get_response_details’)?[‘….’]” (the field in the Form) is the choice field?
You can remove the “body….” from the formula and select from the list your own and Power Automate will complete with the data.
Please take care also that the “Get response details” is the same form as the trigger.
Can you please check if and let me know if you have any questions?
Cheers
Manuel
Great walk through, thank you. When I get to the sharepoint entry, the array variable defined “ValueToInsert” is not an option. Only “current item” and “outputs,” which are both wrong of course. I double checked all of the entries. Thoughts?
Hi Max,
Indeed it’s not an option. It’s a string formatted as a JSON array.
This is necessary so that SharePoint can “understand” the mapping and add the columns correctly.
Are you getting errors on your side? I’m happy to help if you, please provide me the error you’re getting.
Manuel
I got this too, took awhile but looks like it is a glitch in the GUI. If you go to EXPRESSIONS type in ? and click ok it will say no way many that isn’t a thing. Go back to Dynmic Content and the variable is there. You will have to remove the ? before clicking ok and adding the variable in. I got stuck on this for the better part of an hour.
Hi Manuel,
Thank you so much for this. I am having an issue with:
Generates the array – Error: Correct to include a valid reference to ‘Removes_the_Characters’ for the input parameter of action.
And
When I get to the “Create item” in sharepoint the array variable defined “ValueToInsert” is not an option. I only see “current item” and “outputs,”. Should I use an expression?
Many thanks!
Hi Khan,
For the first, can you please check if you’re using a reference like “variables(‘Removes_the_Characters’)” and if that’s the name of your action?
For the second one, I think the issue is that you’re not using the “advanced mode”. It’s a bit hidden, but here’s the default mode:
And the advanced mode that we want is the following:
Can you please check and let me know?
Cheers
Manuel
An excellent tutorial. I got hung up on the Replace function for a bit. I didn’t realize you need to get your own reference ID for the column that is the multi choice. Once I got the right ID number it all fit into place.
However when I look at my content in my list it looks like this: [{“Value”:”Ability to add a Witness”},{“Value”:”Government ID authentication”}]
I was expecting it to look like: Ability to add a Witness, Government ID authentication
When I look at my output is it nice and clean.
So I know the issue is with my JSON concatenate expression but I am not clear on why as I am a beginner when it comes to JSON and not familiar with it’s syntax.
json(concat(‘{“Value”:”‘,items(‘Apply_To_Each’),'”}’))
Hi Jody,
I would do the following:
Create an “Apply to each” to add the key-value pairs to an array. Something like this:
concat(‘”‘,KEY,'”:”‘,VALUE,'”‘)
To generate the JSON file you can use the join:
concat(‘[‘,join(variables(‘JSON_ELEMENTS’),’,’),’]’)
Cheers
Manuel
Thank you for this, I used most of what you are explaining here but I am running into an issue (new user of Power Automate). I am trying to get the Multiple Choice answers to come out on a separate line in a body of an email (Send an email (V2)). So it would look like this:
Choice 1
Choice 2
or even
Choice 3
Choice 7
I used “removed characters” and “generates array”. But It doesn’t list the choices on a new line for each one selected. What am I missing? Thank you for your help.
Hi Garrett,
You can use a trick. Define a variable with a line break. Then in the place where we do all the “replace” actions, in the last one, add your variable instead of the comma. This way, instead of having:
Option 1, Option 2
You’ll have
Option 1
Option 2
Then you can remove the following steps that generate the JSON since you have the string needed to send in your email.
Why a variable and not doing it in the replace? You can do it, but the variable ensures a lot of stuff like encoding so that you don’t need to worry about it. If you want to go back to commas, replace that variable again and you don’t need to change anything in your replace formula.
Can you please check if and let me know if you have any questions?
Cheers
Manuel
Thank you Manuel for getting back to me. This is what I got to come out. Just not sure what I messed up on, sorry still learning.
Request For:
[”
Option 1
“,”
Option 2
“]
Expression:
replace(replace(replace(outputs(‘Get_response_details’)?[‘body/r406c9d7a1b1c4d14b615a19a6caaf2db’],’]’,”),'[‘,”),'”‘,”)
I am sure it is something simple but I don’t see it.
Thanks again,
Garrett
Hi Garrett,
I think Jody’s solution would help you get the values you want.
You’re almost there, just a few tweaks, and it’s done.
If it’s still not working, please report back and we’ll see how to fix it.
Cheers
Manuel
So after playing around with this for awhile I came to conclusion that the string can be passed into SharePoint without the need to turn it into an array. I was able to simplify the flow by just using the outcome from replace(replace(replace(replace(body(‘Get_response_details’)?[‘re2b1baac73c04d30ad2e66042583b5f2′],’]’,”),'[‘,”),'”‘,”),’/’,”) – I added another replace and the ,’/’,”. This left me with a clean string that I was able to just drop into my sharepoint list by using the Outputs from the “Remove Characters” function.
I was able to remove the Generate Array, Initialize Variable and Apply to each from my flow and still have multiple choices captured and displayed nicely.
Hi Jody,
That’s an awesome solution!!!!!
Quite clever.
Cheers
Manuel
I have a multi-select question in MS Forms that may be empty if the branching in the Form send the user to other questions. How can I make Power Automate simply populate my SharePoint list with nothing if that multi-select question is not used (the value is empty)?