Power Automate Approval Workflows for SharePoint Provisioning (Part 2)

by

In Part 1 we set up the foundation. Now let’s build the three Power Automate flows that orchestrate everything — plus a nasty bug that will save you hours if you know about it.

⚠️ The Critical Gotcha: SharePoint Newline Bug

Before anything else — this bug cost me hours of debugging. SharePoint Person fields sometimes return values with a trailing newline character (\n). This silently breaks:

  • Get Manager lookups
  • Email To/CC fields
  • Any string comparison

The bulletproof fix — apply this pattern everywhere you read a Person field:

replace(replace(trim(SOURCE), decodeUriComponent('%0A'), ''), 
        decodeUriComponent('%0D'), '')

Best practice: create “clean” variables at the start of your flow rather than repeating this everywhere.

Flow 1: Security Group Validation (Helper)

Trigger: When Power Apps calls a flow (V2)
Inputs: siteAlias, ownersGroupName, membersGroupName, visitorsGroupName

Compose the SG names (with custom override support)

if(empty(triggerBody()?['ownersGroupName']), 
    concat('SG-Sites-', triggerBody()?['siteAlias'], '-Owners'), 
    triggerBody()?['ownersGroupName'])

Query Microsoft Graph (HTTP with Entra ID connector)

GET /v1.0/groups?$filter=displayName eq '@{outputs('OwnersName')}'&$select=id,displayName

Check existence

if(greater(length(body('Get_Owners_Group')?['value']), 0), 'true', 'false')

Return 6 outputs: ownersexists, ownersid, membersexists, membersid, visitorsexists, visitorsid.

💡 Gotcha: Response property names come back lowercase in Power Apps, regardless of how you name them.

Flow 2: Main Approval Flow (Orchestrator)

Trigger: When a SharePoint item is created

Step 1 — Clean Variables (Critical!)

varCleanRequestorEmail = 
  replace(replace(trim(triggerBody()?['PrimaryOwner']?['Email']), 
    decodeUriComponent('%0A'), ''), decodeUriComponent('%0D'), '')

Step 2 — Get Manager

Office 365 Users → Get manager (V2). User: variables('varCleanRequestorEmail')

Step 3 — Manager Found Check

If no manager → notify admins + terminate.

Step 4 — Manager Approval

Start and wait for approval, assigned to the clean manager email.

Step 5 — Admin Approval

Second approval stage routed to your admin approvers group.

Step 6 — Trigger Runbook (HTTP POST)

{
  "requestId": "@{triggerBody()?['ID']}",
  "siteTitle": "@{triggerBody()?['Title']}",
  "siteAlias": "@{triggerBody()?['SiteAlias']}",
  "primaryOwnerEmail": "@{variables('varCleanRequestorEmail')}",
  "ownersGroup": {
    "name": "@{triggerBody()?['OwnersGroupName']}",
    "id": "@{triggerBody()?['OwnersGroupId']}"
  }
}

Step 7 — Notify Provisioning Started

Email the requester (CC manager + admins).

Flow 3: Notification Flow

Trigger: When an HTTP request is received (from the runbook)
Access: Anyone (secured by URL token)

{
  "type": "object",
  "properties": {
    "siteTitle": { "type": "string" },
    "siteUrl": { "type": "string" },
    "requestorEmail": { "type": "string" }
  }
}

Send a success email with the site URL, CC manager + admins.

💡 Gotcha: To combine emails in a CC field, use concat():

concat(variables('varCleanRequestorEmail'), ';', variables('varCleanManagerEmail'))

Do not put semicolons between separate variables() calls in the expression editor.

Coming up in Part 3: The Azure Automation runbook that actually creates the site — including a bank-grade permission model using PnP.PowerShell.

Leave a Comment