Skip to content

Let's POWER Automate

From no-code to low-code

Menu
  • Expressions
  • Filters
  • General
  • Problems
  • Triggers
  • Application specific solutions
    • Excel
    • Forms
    • Planner
    • Outlook
    • SharePoint
    • Teams
  • Resources
Menu

Save Forms multiple choice question in separate SharePoint columns with Power Automate

Posted on June 2, 2021February 11, 2022 by Tom

“We are attempting to use Power Automate to separate Microsoft forms multiple choice questions into separate columns within a Sharepoint list to trigger follow-on actions. Responses currently appear within the same cell as default.”


When you save multiple choice questions from MS Forms into SharePoint with Power Automate, it’ll be saved as an array. It won’t process it in any way by itself, it’ll just take the array as a string and store it, e.g. [“OptionA”,”OptionB”,”OptionC”]. But what if you want to split the values for a better overview? To store the choices in separate SharePoint columns? One column for each choice and store the information if user selected it?

Forms multiple choice separate columns Power Automate

Use a composed expression

Like many times before, the solution is to use expressions. The main expression is the if(…) expression as already described in the post on formatting empty dates. Add a condition as the first parameter, and what should happen if it’s true or false as the other parameters.

if(<condition>, <ifConditionIsTrue>, <ifConditionIsFalse>)

In the <condition> you should check if the array from the Forms question contains a specific value. And here comes the second expression: contains(…). Contains(…) in this situation will check if the question response contains the specific value.

contains('["OptionA","OptionB","OptionC"]','OptionA') will return true
contains('["OptionB","OptionC"]','OptionA') will return false

Replacing the fixed array with the output from ‘Get response details’ (the default way to process MS Forms):

contains(outputs('Get_response_details')?['body/r4a2197a370d846029b78cdbf87270043'],'OptionA')

Putting it all together, you can check if the Forms question contains the specific response. If it does, update the <ifConditionIsTrue> value, if it doesn’t, update the <ifConditionIsFalse> value. The example below uses SharePoint choice columns with values ‘Yes’ and ‘No’:

if(contains(outputs('Get_response_details')?['body/r4a2197a370d846029b78cdbf87270043'],'OptionA'),'Yes','No')

Repeat the same expression for all the columns, always changing only the value corresponding to the current column.

OptionA Value: if(contains(...,'OptionA'),'Yes','No')
OptionB Value: if(contains(...,'OptionB'),'Yes','No')
OptionC Value: if(contains(...,'OptionC'),'Yes','No')

Summary

Storing the multiple choice questions from MS Forms into separate SharePoint columns with Power Automate can be challenging. But you won’t avoid additional processing no matter how you decide to use that question’s response. Even if you’d like just to display it in a readable way, you’ll need to convert the array with responses into string anyway.

But the solution using if(…) and contains(…) expressions isn’t much more complicated, and it can make your data much easier to read, search and filter.


Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

2 thoughts on “Save Forms multiple choice question in separate SharePoint columns with Power Automate”

  1. Ryan says:
    September 30, 2021 at 10:45 pm

    This is good but I’m still confused, can you show it step by step in a flow

    Reply
    1. Tom says:
      October 4, 2021 at 6:07 pm

      Hello Ryan,
      take a look on the previous post on storing the Forms data to SharePoint: https://tomriha.com/how-to-store-input-from-ms-forms-into-sharepoint-list/, this post is an extension of that one.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Now available:

The Ultimate Power Automate expressions cheat sheet
Equip yourself with the tool to translate your thoughts into Power Automate expressions!

Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

Hello and welcome!

My name is Tom and I'm a business process automation consultant and Microsoft MVP living in the Czech Republic. I’ve been working with Microsoft technologies for almost 10 years, currently using mainly Power Automate, SharePoint, Teams, and the other M365 tools.

I believe that everyone can automate part of their work with the Power Automate platform. You can achieve a lot by "clicking" the flows in the designer, but you can achieve much more if you add a bit of coding knowledge. And that's what this blog is about.

To make the step from no-code Power Automate flows to low-code flows: using basic coding knowledge to build more complex yet more efficient flows to automate more of your daily tasks.

  • Format results of date calculation in SharePoint columnJune 29, 2022
  • Why is your Power Automate flow creating duplicatesJune 26, 2022
  • How to create a unique identifier in your Power Automate flowJune 22, 2022
  • How to assign custom SharePoint permission level with Power AutomateJune 19, 2022
  • Remove permissions from a specific SharePoint user with Power AutomateJune 15, 2022

Power Automate blogs worth visiting

Damien Bird
Dennis (Expiscornovus)
Paul Murana

© 2022 Let's POWER Automate | Powered by Superbs Personal Blog theme