How to Store MS Forms Multiple-Choice Responses in a SharePoint List
Question details
The user needs to properly format and save multiple-choice answers from Microsoft Forms into a SharePoint list without them displaying as JSON arrays.
- Product
- Microsoft Forms, Power Automate, SharePoint
- Device & OS
- not provided
- Scenario
- Automating the transfer of form responses into a SharePoint list for downstream filtering and reporting in Power BI.
- Observed behavior
- Multiple-choice responses are stored as raw JSON-style strings (e.g., ["Option 1", "Option 2"]), which complicates data visualization and filtering.
Ensure you have active Microsoft 365 licenses with access to Power Automate, Microsoft Forms, and edit permissions for the target SharePoint list.
Use Parse JSON and Join Actions in Power Automate
Convert the raw JSON array into a readable, comma-separated string before inserting it into your SharePoint list.
By default, Microsoft Forms exports multiple selections as an array. Power Automate can convert this array into a standard text string using the Parse JSON and Join data operations.
In your Power Automate flow, add a 'Parse JSON' action after 'Get response details'. Insert the dynamic content of your multiple-choice question into the Content field.
Click 'Generate from sample' and paste an example response like ["Option 1", "Option 2"] to automatically create the correct JSON schema.
Add a 'Join' data operation. Select the 'Body' output from the Parse JSON step as the 'From' array, and type a comma followed by a space (, ) in the 'Join with' field.
In your 'Create item' or 'Update item' SharePoint step, map the multiple-choice text column to the 'Output' dynamic content generated by the Join action.
Consult the Power Automate Community for Complex Arrays
If you need to map responses directly into a SharePoint Choice column that allows multiple selections, utilize expert configurations from the community.
Use WPS Forms to Seamlessly Collect and Filter Multiple-Choice Data
Skip complex Power Automate configurations. WPS Forms natively collects and formats multiple-choice responses as clean text directly into WPS Spreadsheet, making filtering and data analysis instantly ready.
- 1. Create a New Form: Open WPS Office, navigate to the 'Apps' section, and select 'WPS Forms' to build your questionnaire with multiple-choice questions.
- 2. Share and Collect Responses: Generate a shareable link to distribute your form to participants and begin collecting data.
- 3. View Data in Spreadsheet: Click 'View Responses' in the form dashboard. WPS Office will automatically compile the data into a cleanly formatted WPS Spreadsheet.
- 4. Analyze Automatically: Use built-in data filtering and visualization tools on the correctly formatted multiple-choice columns, without any manual string conversion.
Frequently Asked Questions
Why does Microsoft Forms format multiple-choice answers as JSON strings?
Microsoft Forms exports multiple selections as an array (a JSON format) so the backend system recognizes them as distinct individual values. However, standard text columns in SharePoint read this raw data verbatim unless specifically parsed by a flow.
Can I directly map Forms multiple-choice responses to a SharePoint multiple-choice column?
Yes, but it requires an advanced Power Automate setup. You cannot map it directly; you must build an array of objects matching the SharePoint Choice column schema (often using a 'Select' action to map the 'Value' property) before passing it to SharePoint.
How does raw JSON formatting affect Power BI visualization?
When Power BI imports raw JSON arrays like ["Option 1", "Option 2"], it treats the entire bracketed string as a single unique value. This prevents you from accurately counting, filtering, or categorizing the individual options chosen by respondents.
