📦 Series: Exam Auto-Evaluator Part 3 of 3 — SharePoint + Approval + Complete Pipeline

"A pipeline that processes but does not save is a demo. A pipeline that saves, notifies, and waits for human approval is a production system. Today you build the production version."

In Part 2 you built the core: the agent accepts the file, Power Automate reads each question, AI Builder answers them, and answers are collected in a variable. This part completes the pipeline: write answers to a new Excel file, save it to SharePoint, send you an approval email, and handle your response.

What You Add in This Part

[Continuing from Part 2 — after AllAnswers variable is populated]

Parse the AllAnswers string into individual Q+A pairs
        ↓
Create output Excel in SharePoint with two columns: Question | Answer
Write each Q+A pair as a row
        ↓
Get the SharePoint file URL
        ↓
Send approval email to reviewer (you)
        ↓
Wait for approval response
        ↓
If Approved → send confirmation to a notification email
If Rejected → send review request email

Phase 5 — Prepare the SharePoint Output File

Step 20: Create the output Excel template in SharePoint

First, create a template file manually in SharePoint. Power Automate will copy this template and fill in the answers.

  1. Open a browser and go to sharepoint.com → sign in with your work account
  2. Click on your site (e.g. your team's site or a site called "Documents" or your department name)
  3. In the left sidebar, click Documents (or "Shared documents" — your document library)
  4. Click + New → Folder → name it ExamResults → click Create
  5. Open the ExamResults folder → click + New → Excel workbook
  6. The workbook opens in Excel Online. Rename it: click File → Rename → type exam-results-template → press Enter
  7. In cell A1 type: Question
  8. In cell B1 type: Answer
  9. Select A1 and B1 → click Insert → Table → check "My table has headers" → click OK
  10. Name the table: click anywhere in the table → Table Design tab appears → click Table Name field showing "Table1" → type ExamResults → press Enter
  11. Close the tab. The file is saved automatically to SharePoint.
📌 What this template is for Power Automate will copy this template to create a new output file for each exam processed. The template provides the correct table structure. Without a named table, the "Add a row" action fails.

Step 21: Note the SharePoint site URL

  1. In your SharePoint site, look at the browser address bar
  2. Copy the base URL — it looks like: https://yourcompany.sharepoint.com/sites/yoursite
  3. Save this URL — you will need it in the next steps

Phase 6 — Add Steps to the Power Automate Flow

Open Power Automate → My flows → Process Exam Questions → click Edit. You see your flow from Part 2. Add the following steps after the Apply to each loop (after all answers have been collected in AllAnswers).

Step 22: Copy the template to create the output file

  1. Click + below the Apply to each loop
  2. Search: Copy file
  3. Under SharePoint connector, click "Copy file"
  4. Fill in these fields:

Current Site Address: type your SharePoint site URL (e.g. https://yourcompany.sharepoint.com/sites/yoursite)

File to Copy:

  1. Click the folder icon → navigate to Documents → ExamResults → click exam-results-template.xlsx → click Select

Destination Site Address: same SharePoint site URL as above

Destination Folder:

  1. Click the folder icon → navigate to Documents → ExamResults → click Select (select the folder itself, not a file inside it)

New File Name:

  1. Click inside the New File Name field
  2. Click the Expression tab (in the toolbar that appears above the field)
  3. In the formula bar, type: 
    concat('Results-', formatDateTime(utcNow(), 'yyyy-MM-dd-HHmm'), '.xlsx')
  4. Click OK
  5. A purple expression pill appears in the field
📌 What this expression does concat() joins text together. formatDateTime(utcNow(), 'yyyy-MM-dd-HHmm') returns the current date and time (e.g. 2026-06-05-1430). The result is a unique filename like Results-2026-06-05-1430.xlsx. Each exam gets its own file — no overwriting.

Step 23: Add rows to the output file

Now write each Q+A pair as a row in the copied file. We need to loop through the answers we collected. The AllAnswers variable contains all answers as one text string. We use a second Apply to each loop over the original Excel rows so we can write one row per question.

  1. Click + below the Copy file action
  2. Search: Apply to each
  3. Click Control → Apply to each
  4. In the "Select an output" field → click Dynamic content → under "List rows present in a table" → click value
📌 Why a second loop? The first loop (from Part 2) ran AI Builder and collected answers. That loop is now closed. This second loop runs over the same Excel rows again so we can write one output row per question. We could combine them — but keeping them separate makes the flow easier to understand and debug.
  1. Inside this second loop, click +
  2. Search: Add a row into a table
  3. Under Excel Online (Business), click "Add a row into a table"
  4. Fill in:

Location: OneDrive for Business... wait — this output file is in SharePoint, not OneDrive. Click Location dropdown → select SharePoint

Document Library: click dropdown → select Documents

File:

  1. Click the file field → click Dynamic content → under "Copy file" section → click Id (this is the ID of the newly copied file — not the template)
⚠️ Use Id from Copy file — not a static path You must select the Id output from the Copy file action. This dynamically points to the newly created file. If you browse and select a static file path, all runs will write to the same file — overwriting previous results.

Table: type ExamResults directly (the dropdown may not load for dynamically referenced files)

Question column: click Dynamic content → under current item → click Question

Answer column: click Dynamic content → under "Run a prompt" → click text (the AI answer — same one used in Append in Part 2)

⚠️ "Run a prompt" not available in second loop If you cannot see the Run a prompt output in dynamic content inside this second loop, it is because Run a prompt is in the first loop and cannot be referenced from a different loop. Solution: Move the "Add a row" action into the FIRST loop (from Part 2), directly after "Run a prompt" and after "Append to string variable". Delete this second loop entirely. Write to SharePoint in the same loop where AI answers are generated.

Step 24: Get the SharePoint file URL

  1. Click + below the second Apply to each loop (outside the loop)
  2. Search: Get file properties
  3. Under SharePoint connector, click "Get file properties"
  4. Fill in:
    • Site Address: your SharePoint site URL
    • Library Name: type Documents
    • Id: click Dynamic content → under "Copy file" → click Id
  5. This action gives you the file's properties including its SharePoint URL

Phase 7 — Send Approval Email

Step 25: Add the approval action

  1. Click + below "Get file properties"
  2. Search: Start and wait for an approval
  3. Look for the Approvals connector (purple icon) — NOT the Outlook or Email connector. The Approvals connector is a separate connector specifically for approval workflows.
  4. Click "Start and wait for an approval"
  5. Fill in:

Approval type: click dropdown → select "Approve/Reject - First to respond"

Title:

  1. Click inside the Title field
  2. Click Expression tab → type: 
    concat('Exam Results Ready — ', formatDateTime(utcNow(), 'dd MMM yyyy'))
  3. Click OK

Assigned to: type your work email address exactly (e.g. raushan@yourcompany.com). This is who receives the approval email — in this project, that is you.

Details: click inside the Details field and type:

AI has processed the exam questions and generated answers.

Please review the results at the link below:

Then click Dynamic content → under "Get file properties" → look for {Link} or Link → click it. This inserts the SharePoint file URL.

Then type below it:


What to check:
- Are the answers accurate and complete?
- Are any answers missing or unclear?

Approve = answers are correct, proceed to notify
Reject = needs manual review

Item link: Dynamic content → Get file properties → {Link}

Item link description: type: View Exam Results in SharePoint

📌 How "Start and wait for an approval" works This action sends you the approval email AND pauses the flow. The flow waits — for up to 30 days — until you click Approve or Reject in the email or in the Power Automate Approvals centre. When you respond, the flow automatically continues in the correct branch.

Step 26: Add a condition to check the response

  1. Click + below the approval action
  2. Search: Condition
  3. Click Control → Condition
  4. In the condition builder:
    • Left value: click Dynamic content → under "Start and wait for an approval" → click Outcome
    • Operator: leave as "is equal to"
    • Right value: type Approve (exactly this word — capital A, no extra spaces)
⚠️ Spelling matters exactly The Outcome value is Approve with a capital A. If you type "approve" (lowercase) or "Approved" (past tense), the condition will never match and the Yes branch will never run. Type it exactly: Approve

Step 27: YES branch — notify by email

  1. In the Yes branch (left side of the condition), click +
  2. Search: Send an email
  3. Under Office 365 Outlook connector, click "Send an email (V2)"
  4. Fill in:
    • To: type a notification email address. Since we do not collect the student's email in this pipeline, use your own email for now. In a real deployment, you would collect the student's email in the Copilot Studio topic (add another Question node before the file upload asking for their email, store it in a variable, and pass it as a second flow input alongside FileName).
    • Subject: type: Exam Results Ready ✅
    • Body: type your message, then insert the SharePoint link using Dynamic content → Get file properties → {Link}
📌 Collecting student email — how to add it In Copilot Studio → Evaluate Exam Paper topic → add a Question node BEFORE the file upload question. Ask: "What is your email address?" → Identify: Email → Save response as: StudentEmail. Then in the Action node, add a second text input to the flow: StudentEmailAddress = Topic.StudentEmail. In Power Automate trigger, add input: StudentEmailAddress (Text). Use this in the Send email To field.

Step 28: NO branch — send for manual review

  1. In the No branch (right side of the condition), click +
  2. Search: Send an email
  3. Click Office 365 Outlook → Send an email (V2)
  4. Fill in:
    • To: your email (the reviewer)
    • Subject: Exam Results Need Manual Review
    • Body: type: 
      The AI-generated exam answers have been rejected for review.

Reviewer comments:
      Then click Dynamic content → under "Start and wait for an approval" → click Comments (this inserts whatever comment you typed when rejecting). Then type: 
      
Please review the results file manually:
      Then insert Dynamic content → Get file properties → {Link}

Step 29: Save and publish the updated flow

  1. Click Save draft (top right)
  2. Click Publish
  3. Wait for confirmation

Phase 8 — End-to-End Test

Step 30: Full pipeline test

  1. Open Copilot Studio → Exam Evaluator agent → Test panel
  2. Type: evaluate exam
  3. Upload your exam-questions.xlsx
  4. Wait for the confirmation message from the agent
  5. Check your email inbox — you should receive an approval email with the subject "Exam Results Ready — [date]"
  6. The email contains a link to the SharePoint file. Click the link — you should see the output Excel file with Question and Answer columns filled in.
  7. In the email, click Approve
  8. Wait 1-2 minutes → check your inbox for the notification email

Common Errors in Part 3 — Exact Fixes

  • Copy file fails: "File not found"
    Verify the template file exists at the exact path in SharePoint. SharePoint paths are case-sensitive. Open SharePoint → ExamResults folder → confirm exam-results-template.xlsx is there.
  • "Add a row" fails: "Table not found"
    The copied file is a copy of the template. Open the copied file in SharePoint to verify the ExamResults table exists inside it. If not, the template may not have been saved correctly — re-create the template following Step 20 exactly.
  • Approval email not received
    Check your junk/spam folder. Also check the Power Automate Approvals centre: powerautomate.microsoft.com → Approvals (left sidebar) → the approval request should appear there even if email is blocked. You can approve directly from the Approvals centre.
  • {Link} property not available in Get file properties
    Use {FilenameWithExtension} and construct the URL manually using an expression: concat('https://yourcompany.sharepoint.com/sites/yoursite/Shared%20Documents/ExamResults/', outputs('Get_file_properties')?['body/{FilenameWithExtension}'])
  • Condition always goes to No branch
    The Outcome value must be exactly Approve (capital A, no trailing space). Open the condition → check the right-side value field carefully. Delete and retype if needed.

Azure OpenAI Alternative (For Enterprise)

If your organisation has Azure OpenAI, replace AI Builder with an HTTP action:

Action: HTTP
Method: POST
URI: https://[resource].openai.azure.com/openai/deployments/gpt-4o/chat/completions?api-version=2024-12-01-preview
Headers:
  api-key: [your Azure OpenAI key]
  Content-Type: application/json
Body:
{
  "messages": [
    {"role": "system", "content": "You are an expert teacher. Answer exam questions clearly."},
    {"role": "user", "content": "@{items('Apply_to_each')?['Question']}"}
  ],
  "max_tokens": 300
}

Parse the response with a Parse JSON action. Extract: body('Parse_JSON')?['choices'][0]?['message']?['content']

🎯 Final Series Quiz

Q1: Why do we use "Start and wait for an approval" instead of just "Send an email"?

Show Answer

"Send an email" sends the email and immediately moves to the next step — it does not wait for a response. "Start and wait for an approval" sends the email AND pauses the flow until the reviewer clicks Approve or Reject. When they respond, the flow continues in the correct Yes or No branch. This is the human-in-the-loop pattern — a human must verify before the pipeline proceeds.

Q2: The Outcome condition checks for "Approve". What happens if you type "Approved" instead?

Show Answer

The condition will never match the Yes branch. Power Automate's approval action returns the string "Approve" (not "Approved"). If your condition checks for "Approved", it never equals "Approve" and the flow always goes to the No branch — even when you actually clicked Approve in the email. Always type exactly: Approve

Q3: How would you extend this pipeline to collect the student's email and notify them directly?

Show Answer

In Copilot Studio: add a Question node before the file upload node, asking for the student's email address. Set Identify to "Email". Store in a variable named StudentEmail. In the Action node: add StudentEmailAddress as a new text input, mapped to Topic.StudentEmail. In Power Automate trigger: add a new Text input named StudentEmailAddress. In the Yes branch Send email action: use this input in the To field instead of a hardcoded address.

Series Complete — What You Built

  • A Copilot Studio agent that receives Excel file uploads from students
  • A Power Automate Agent Flow that reads questions, gets AI answers, writes to SharePoint
  • An approval workflow where you review answers before the pipeline completes
  • A complete human-in-the-loop enterprise automation using only Microsoft 365
  • A reusable architecture applicable to document processing, HR assessments, and any scenario involving file upload + AI processing + approval
📚 Continue Learning