Your organization just launched a vendor evaluation survey using Microsoft Forms. Responses start trickling in — then flooding in — and someone has to manually copy each submission into a SharePoint list, email the relevant department head, and flag anything that looks like a high-priority concern. That "someone" becomes a bottleneck, responses get delayed, and eventually someone important gets an angry email asking why their feedback disappeared into a black hole.
This is exactly the kind of workflow that Power Automate was built to eliminate. Microsoft Forms and Power Automate are native partners in the Microsoft 365 ecosystem, and together they give you a genuinely powerful pipeline: a form response triggers a flow, the flow reads the response data, routes it based on conditional logic, and stores it wherever it needs to live — SharePoint, Excel, Dataverse, or a combination of all three. No manual copying. No delayed notifications. No lost submissions.
By the end of this lesson, you'll have built a production-ready automated pipeline that captures form responses the moment they're submitted, applies conditional routing logic, sends targeted notifications, and stores structured data in SharePoint. You'll also understand the failure points, the quirks of the Forms connector, and how to make your flows resilient enough to handle real organizational traffic.
What you'll learn:
You should be comfortable with the Power Automate canvas — you know how to create a flow, add actions, and use dynamic content. You have a Microsoft 365 account with Power Automate access, and you own or co-own at least one Microsoft Form. If you've built a basic approval flow or sent an email from Power Automate before, you're in the right place. We won't be explaining what a trigger is or how to log in.
You'll also want a SharePoint site where you have at minimum Contribute permissions so you can create lists and add items.
Before you build anything, you need to understand something that trips up almost every practitioner the first time: the Microsoft Forms trigger doesn't give you the response data directly. It only tells you that a response was submitted and hands you a Response ID.
This is a two-step pattern:
If you skip the second step and try to use the trigger output directly, you'll find there's almost nothing useful in it. The Response ID is an opaque string like r_abc123xyz. The actual answers — the person's name, their rating, their comments — are locked behind that "Get response details" call.
This architecture exists because Forms decouples submission events from data retrieval, which actually makes sense at scale. The trigger fires immediately; the data fetch happens when your flow is ready to process it. For 99% of use cases, these happen within seconds of each other, but it's worth understanding the model so you're not confused when you see the response ID show up as the only output from your trigger.
Here's the full pattern we'll build in this lesson:
[Form Submitted]
→ Get Response Details
→ Parse & Evaluate Response Data
→ Conditional Routing (Switch or Condition)
→ Branch A: High Priority → Email Department Head + Create SharePoint Item (Urgent)
→ Branch B: Standard → Create SharePoint Item (Normal)
→ Confirm Storage Success
We're going to build around a realistic scenario: a post-project retrospective survey used by a consulting firm after each client engagement ends. The form collects structured feedback that flows to project managers and gets stored for quarterly reporting.
Create a Microsoft Form with these fields:
Name the form something clear: "Post-Project Retrospective — Client Feedback."
Important: Once you publish a form and connect it to Power Automate, avoid renaming the form or reordering its questions. Power Automate references questions by their internal ID, not their display name. Reordering questions mid-deployment doesn't break the flow, but it does make your dynamic content confusing to read. Renaming the form entirely can cause connector authentication issues.
After creating the form, submit one test response with realistic data before you build your flow. This gives you real dynamic content to work with in the flow editor instead of empty placeholder tokens.
Open Power Automate and create a new Automated cloud flow. When prompted to choose a trigger, search for "Microsoft Forms" and select "When a new response is submitted."
In the trigger configuration, you'll see a dropdown for Form Id. Click it and your available forms will populate. Select "Post-Project Retrospective — Client Feedback." If your form doesn't appear, make sure you're logged into Power Automate with the same Microsoft 365 account that owns the form.
Once the trigger is configured, add the first action: search for "Microsoft Forms" again and select "Get response details."
This action needs two inputs:
Tip: The dynamic content panel labels this as "List of response notifications Response Id" because the Forms trigger is technically designed around a notification list pattern. There's only ever one Response ID per trigger firing in practice. Don't let the "list" wording worry you — you're working with a single response.
At this point, your flow has two steps and will correctly fetch response data when someone submits the form. Before adding any logic, test this much. Click "Test" in the top right corner, select "Manually", then submit your test form response. After the flow runs, click on the "Get response details" step to expand its output and verify you can see your actual form answers in the result body.
You'll see something like this in the raw output:
{
"responder": "alex.chen@contoso.com",
"submitDate": "2024-11-14T09:23:41.000Z",
"r1a2b3c4": "Northbrook Analytics Migration",
"r5e6f7g8": "Contoso Financial Services",
"r9h0i1j2": "4",
"r3k4l5m6": "The data modeling phase was extremely well-executed...",
"r7n8o9p0": "Communication during UAT could have been clearer...",
"r1q2r3s4": "Yes",
"r5t6u7v8": "No",
"r9w0x1y2": "partner@contoso-financial.com"
}
The question IDs are those opaque strings (r1a2b3c4, etc.). When you use dynamic content in subsequent steps, Power Automate shows you the human-readable question text instead of the ID — but this is what's happening under the hood.
Now that you have the response details, you'll reference individual answers throughout the rest of your flow using dynamic content tokens. Each form question appears as a separate token in the dynamic content panel once the "Get response details" step has been added.
Add a Compose action immediately after "Get response details." Name it "Parse Satisfaction Rating" by clicking the three dots on the action and selecting Rename. In the Inputs field, use the dynamic content token for your "Overall Satisfaction" question.
Here's the thing: form ratings come back as text strings, not integers. A 4-star rating returns as "4", not 4. This matters when you want to do comparisons. If you're routing based on satisfaction score, you need to convert the value:
In the Compose action, use this expression instead of the raw dynamic content token:
int(outputs('Get_response_details')?['body/r9h0i1j2'])
Replace r9h0i1j2 with your actual question ID. You can find the question ID by clicking "Peek code" on the Get response details action, or by looking at the raw output from your test run.
A better approach: Rather than hunting for question IDs in raw JSON, use the dynamic content panel tokens whenever possible. Power Automate resolves them correctly and your flow is much easier to read. Only drop into expression syntax when you need to perform type conversion or string manipulation on the value.
For the "Escalation Required?" field (a choice question with Yes/No options), the token will return the text of the selected option — literally the string "Yes" or "No". Keep this in mind when writing your conditions.
This is where your flow becomes genuinely useful. We want two behaviors:
Add a Condition action after your Compose step.
In the condition editor:
Yes (exact match, case-sensitive)Warning: Microsoft Forms choice question responses are case-sensitive when compared in Power Automate conditions. If your choice option is "Yes" (capital Y), your condition must compare against "Yes" not "yes". If you're getting routing failures, this is usually why.
The condition creates a True branch and a False branch. Let's build out the True (escalation) branch first.
In the True branch, add a Send an email (V2) action from the Office 365 Outlook connector.
Configure it like this:
concat('ESCALATION REQUIRED: ', triggerOutputs()?['body/value'][0]?['resourceData']?['responseId'])
Actually, let's do this more readably by composing the subject from form fields. In the Subject field, mix static text with dynamic content tokens:
ESCALATION — [Project Name Token] / [Client Name Token] — Immediate Review Required
Where you'd click in, then use the dynamic content panel to insert the Project Name and Client Name tokens between the static text. The result reads like a real alert.
A project retrospective has been flagged for escalation.
Project: [Project Name]
Client: [Client Name]
Respondent: [Respondent Email]
Satisfaction Rating: [Overall Satisfaction] / 5
Submitted: [Submit Date from Get response details]
What could be improved:
[What could be improved? token]
Please review this response within 24 hours and contact the respondent directly.
This gives the project lead everything they need without having to log in anywhere.
The False branch doesn't send an email — it just proceeds to SharePoint storage. You could add a lower-priority notification here if needed (like a daily digest pattern), but for now, leave it empty and let both branches converge on the SharePoint step.
Both branches of your flow need to store the response in SharePoint, and this is where many practitioners make a structural mistake: they add the SharePoint create-item action inside each branch, duplicating it. That works but creates a maintenance headache — if you change the list columns later, you have to update two actions.
The cleaner pattern is to put the SharePoint action after the condition block, where it runs regardless of which branch executed. Power Automate flows continue to subsequent steps after a condition finishes both branches.
First, create a SharePoint list named "Project Retrospectives" with these columns:
| Column Name | Column Type | Notes |
|---|---|---|
| Title | Single line of text | Use this for Project Name |
| ClientName | Single line of text | |
| SatisfactionRating | Number | Min: 1, Max: 5 |
| WhatWentWell | Multiple lines of text | |
| WhatToImprove | Multiple lines of text | |
| RecommendUs | Choice | Yes; No; Maybe |
| EscalationRequired | Yes/No (Boolean) | |
| RespondentEmail | Single line of text | |
| SubmitDate | Date and Time | |
| FormResponseId | Single line of text | Store the Response ID for audit purposes |
| IsEscalation | Yes/No (Boolean) | Redundant with above but useful for filtered views |
Tip: Create the SharePoint list before building the Power Automate action. Power Automate discovers list columns at design time — if the list doesn't exist yet, you can't configure the action. And if you add columns to the list after setting up the action, you'll need to delete and re-add the action to pick up the new columns.
After the condition block, add "Create item" from the SharePoint connector.
The column fields will appear. Map them as follows:
int(outputs('Get_response_details')?['body/YOUR_QUESTION_ID'])
if(equals(outputs('Get_response_details')?['body/YOUR_ESCALATION_QUESTION_ID'], 'Yes'), true, false)
The submit date comes back from Forms in ISO 8601 format: 2024-11-14T09:23:41.000Z. SharePoint's Date and Time column accepts this format directly, so you can drop the token in without conversion. However, if you're displaying this date in an email or want it in a local timezone, use this expression:
convertTimeZone(outputs('Get_response_details')?['body/submitDate'], 'UTC', 'Eastern Standard Time', 'MM/dd/yyyy hh:mm tt')
Adjust the timezone string to match your organization. The full list of valid timezone identifiers follows the Windows timezone database naming convention.
If you had a question where respondents could select multiple options (e.g., "Which project phases had issues?" with checkboxes), the response comes back as a semicolon-delimited string: "Discovery;UAT;Go-Live".
SharePoint's Choice column (configured for multiple selections) accepts this format directly. But if you need to split it into individual items — say, to create one record per selected option — use the split() expression:
split(outputs('Get_response_details')?['body/YOUR_MULTI_CHOICE_QUESTION_ID'], ';')
This returns an array you can then iterate with an Apply to each loop.
A flow that silently fails is worse than no automation at all, because you won't know responses are being lost. Let's add basic error handling.
Click the three dots on the "Create item" SharePoint action and select "Configure run after." By default, actions only run when the previous step succeeded. You can change this to also run on failure — or create parallel branches that handle failures differently.
The more useful pattern here is to add a separate error notification branch. Select the "Create item" action, click the plus button that appears below it to add a parallel branch (by clicking the plus and then "Add a parallel branch"). In that parallel branch, add a Send an email action.
In that parallel branch's action, go to the three dots → "Configure run after" → check "has failed" and uncheck "is successful." This email only sends when the SharePoint write fails.
Configure the failure email:
[FLOW ERROR] Project Retrospective Storage FailedThe Power Automate flow failed to write a form response to SharePoint.
Response ID: [Response ID token from trigger]
Flow Run ID: @{workflow()['run']['name']}
Timestamp: @{utcNow()}
Please check the Power Automate run history and manually add the response to SharePoint.
Manual review URL: https://forms.office.com/[your form URL]
The workflow()['run']['name'] expression gives you the exact flow run ID so you can find it in the run history quickly. This transforms a silent failure into an actionable alert.
Now you're going to extend the flow you've built to handle a second routing scenario: satisfaction-based routing that goes beyond the binary escalation flag.
The scenario: When a respondent gives a satisfaction rating of 2 stars or below, the flow should automatically create a SharePoint task (in a separate "Follow-Up Tasks" list) assigned to the account manager. Ratings of 4 or 5 stars should trigger a "thank you" email to the respondent.
Step 1: Create the Follow-Up Tasks list in SharePoint
Create a new list called "Follow-Up Tasks" with these columns:
Step 2: Add a satisfaction routing condition
After your existing condition block (and after the SharePoint Create Item action), add a new Switch action. A Switch is cleaner than nested conditions when you have more than two branches.
1, 2, 3, 4, 5Actually, for this exercise, use a Condition with an expression instead of individual cases:
lessOrEquals(int(outputs('Get_response_details')?['body/YOUR_RATING_QUESTION_ID']), 2)
Step 3: True branch (low satisfaction)
Add a "Create item" action for the Follow-Up Tasks list:
concat(outputs('Get_response_details')?['body/PROJECT_NAME_ID'], ' — ', outputs('Get_response_details')?['body/CLIENT_NAME_ID'], ' — Low Satisfaction Follow-Up')addDays(utcNow(), 5) to set a due date 5 business days out (note: addDays doesn't skip weekends, which is a known limitation — for production use, you'd implement a custom business day calculation or use a different approach)Step 4: False branch (high satisfaction)
In the False branch, add another condition checking if the rating is 4 or greater:
greaterOrEquals(int(outputs('Get_response_details')?['body/YOUR_RATING_QUESTION_ID']), 4)
If True, send a thank-you email to the respondent's email address collected in the form, thanking them by name if you collected it, referencing the project name.
Step 5: Test with multiple submissions
Submit three test responses: one with rating 1 and Escalation = Yes, one with rating 5 and Escalation = No, one with rating 3 and Escalation = No. Verify that:
Check your SharePoint lists and email inbox to confirm all paths executed correctly.
This usually means the "Get response details" action hasn't been tested successfully yet, or you renamed the form after connecting it. The dynamic content panel in Power Automate is populated based on the schema from the last successful test run of each action. If "Get response details" hasn't run yet in a test, no tokens appear.
Fix: Run a manual test of the flow by submitting a real form response, then reopen the flow editor. The tokens should appear.
Check for a case sensitivity mismatch between your form choice text and your condition value. Also check for trailing whitespace — it's surprisingly common when copying and pasting condition values.
Diagnostic trick: Add a Compose action right before your condition that outputs the exact value you're comparing: outputs('Get_response_details')?['body/YOUR_QUESTION_ID']. Run a test and inspect that output. Copy the exact value shown and paste it into your condition.
You've probably mapped the wrong dynamic content token. In the dynamic content panel, tokens from "Get response details" have labels that match your question text — but if you have similar question wording, it's easy to grab the wrong one.
Fix: Hover over tokens in the dynamic content panel — the tooltip shows the underlying expression including the question ID. Cross-reference with the question ID visible in the raw output from a test run.
The int() conversion is failing silently. Rating questions in Forms can return empty strings for respondents who skip optional rating questions. int("") returns 0, not an error.
Fix: Use a null-coalescing expression:
if(empty(outputs('Get_response_details')?['body/YOUR_QUESTION_ID']), 0, int(outputs('Get_response_details')?['body/YOUR_QUESTION_ID']))
This stores 0 for unanswered ratings, which at least signals a missing value rather than corrupting your data.
This is a known behavior with the Forms connector when the form is owned by a group or shared mailbox. The trigger may fire once per owner in some configurations.
Fix: Use the Response ID to implement idempotency. Before creating the SharePoint item, query the SharePoint list using "Get items" with a filter: FormResponseId eq 'YOUR_RESPONSE_ID_TOKEN'. If the query returns any items, skip the create action using a condition. This prevents duplicate records even if the trigger fires multiple times.
This happens when your Microsoft 365 license or the form ownership has changed — for example, if the form was created by someone who left the organization, and their account was deactivated.
Fix: The form must be owned by an active account. Either transfer form ownership (Microsoft 365 admin can do this) or recreate the flow connection under a service account that will remain active. For production flows, always use a service account, not a personal user account, as the flow connection identity.
The Forms trigger is designed for moderate volume — think dozens to low hundreds of submissions per day. Power Automate processes flows sequentially with some parallelism depending on your licensing tier. If you're running a form with thousands of responses in a short window (like an all-hands company survey sent to 5,000 employees at once), you may hit throttling.
For high-volume scenarios:
For the typical organizational survey use case — 10 to 200 responses per day — the pattern in this lesson handles it without issues.
You've built a production-ready Forms response pipeline that does something genuinely useful: it captures structured survey data the moment someone submits a form, makes routing decisions based on response content, sends targeted notifications to the right people, and creates organized records in SharePoint. You also added error handling so failures don't go unnoticed.
Let's recap the core patterns you've internalized:
Where to go from here:
The next natural progression is approval workflows — instead of just routing notifications, you route the response to a human decision-maker who must approve or reject something before the flow continues. The Power Automate Approvals connector integrates with Teams and Outlook and pairs naturally with Forms data collection.
After that, explore Dataverse as a storage target instead of SharePoint. Dataverse offers relational data modeling, proper column types, row-level security, and much better performance for high-volume scenarios. If your Forms pipeline is feeding a reporting dashboard in Power BI, Dataverse is the better persistence layer.
Finally, once you're comfortable with conditional routing, revisit this flow and extend it with adaptive cards in Teams. Instead of a plain email notification, you can send an interactive Teams message that lets the project lead acknowledge the escalation, add a note, or trigger the next step — all without leaving Teams.
The pipeline you built today is the foundation for all of that.
Learning Path: Flow Automation Basics