Automate SharePoint Site Provisioning End to End

# Row 1: Length 2–40 characters
@{and(greaterOrEquals(length(triggerOutputs()?['body/SiteURLAlias']), 2),
      lessOrEquals(length(triggerOutputs()?['body/SiteURLAlias']), 40))}

# Row 2: All lowercase — no uppercase characters allowed
@{equals(toLower(triggerOutputs()?['body/SiteURLAlias']),
          triggerOutputs()?['body/SiteURLAlias'])}

# Row 3: No spaces (catches multi-word aliases)
@{not(contains(triggerOutputs()?['body/SiteURLAlias'], ' '))}

# If condition = false → Update Status to "Failed" → Send email "URL alias is invalid" → Terminate

Approval type:  Approve/Reject - First to respond

Title:          Site request: @{triggerOutputs()?['body/Title']}

Assigned to:    @{outputs('Get_manager_(V2)')?['body/mail']}
                # This is the manager's email from the Get manager (V2) action

Details:        Requester: @{triggerOutputs()?['body/Author/DisplayName']}
                URL alias: @{triggerOutputs()?['body/SiteURLAlias']}
                Type: @{triggerOutputs()?['body/SiteType/Value']}
                Purpose: @{triggerOutputs()?['body/BusinessPurpose']}

Item link:      @{triggerOutputs()?['body/{Link}']}
                # Opens the SharePoint list item so the approver can review the full request

Timeout:        P2D
                # ISO 8601 for 48 hours — after this, approval action status = TimedOut
                # Handle the timed-out branch via Configure run after → Has timed out

{
  "requestItemId": 42,
  "siteTitle": "Marketing Hub 2026",
  "siteUrlAlias": "marketing-hub-2026",

  // siteType — THREE valid values only:
  // "CommunicationSite"                  → publishing/intranet site, no group
  // "TeamSiteWithoutMicrosoft365Group"   → collaboration site, no group, no Teams
  // "TeamSite"                           → group-connected, creates M365 Group + optional Teams
  "siteType": "CommunicationSite",

  // template — controls document structure applied by the runbook
  // "General" | "Project Team" | "HR Department" | "IT Service Desk"
  "template": "General",

  "primaryOwnerEmail": "[email protected]",
  "secondaryOwnerEmail": "[email protected]",  // optional, empty string if not set
  "description": "Central hub for the Marketing department",
  "requesterEmail": "[email protected]",
  "tenantName": "contoso"   // subdomain only — "contoso" not "contoso.sharepoint.com"
}

# Graph endpoint — reliable 200/404 for site existence check
# 200 = site exists (URL taken)   |   404 = URL is free to use
GET https://graph.microsoft.com/v1.0/sites/
    @{body('Parse_JSON')?['tenantName']}.sharepoint.com:
    /sites/@{body('Parse_JSON')?['siteUrlAlias']}:

Method:         GET
Authentication: Managed Identity
Audience:       https://graph.microsoft.com

# The Logic App Managed Identity needs "Sites.Read.All" on Graph
# (granted via Entra → Enterprise applications → your Logic App MI → API permissions)

# TOP LEVEL (before the Try scope — Initialize variable MUST be here)
Initialize variable — name: runbookJobId   type: String   value: ""
Initialize variable — name: requestItemId  type: Integer  value: @{body('Parse_JSON')?['requestItemId']}

# SCOPE: Try
  SharePoint — Update item (SiteRequestsListId, Id=requestItemId, Status="Provisioning")
  Azure Automation — Create job (runbook: SP-SiteProvisioning, all site params as key/value)
  Set variable — runbookJobId = @{body('Create_job')?['properties/jobId']}
  SharePoint — Update item (LogicAppRunID = @{variables('runbookJobId')})
  Do Until (exit when job status is Completed/Failed/Suspended, or counter > 20)
    Delay — 30 seconds
    Azure Automation — Get job (@{variables('runbookJobId')})
  Condition — @{equals(body('Get_job')?['properties/status'], 'Completed')}
    TRUE branch:
      Azure Automation — Get job output
      SharePoint — Update item (Status="Provisioned", ProvisionedURL=extracted URL)
      Office 365 Outlook — Send an email (V2) — success notification
    FALSE branch:
      Terminate (Status=Failed, message="Runbook did not complete successfully")
      # Terminate with Failed causes the Try scope to fail → triggers Catch scope

# SCOPE: Catch   (Configure run after: Try scope → Has failed)
  SharePoint — Create item in Site Provision Logs (using result('Try') expressions)
  SharePoint — Update item (Status = "Failed")
  Office 365 Outlook — Send an email (V2) — failure notification

# result('Try') returns an array of all actions in the Try scope.
# Filter to the first failed action to get the most relevant error.
# Use these expressions in the "Create item in Provision Logs" action:

# Title — unique identifier for the log row
ERR-@{variables('requestItemId')}-@{formatDateTime(utcNow(),'yyyyMMdd-HHmmss')}

# Name of the Logic App action that failed
@{first(filter(result('Try'), item()?['status'] == 'Failed'))?['name']}

# Human-readable error message from the failed action
@{first(filter(result('Try'), item()?['status'] == 'Failed'))?['error']?['message']}

# Full error code (e.g. ActionFailed, BadRequest)
@{first(filter(result('Try'), item()?['status'] == 'Failed'))?['error']?['code']}

# Azure Automation job ID stored earlier in the Try scope
@{variables('runbookJobId')}

# Extract provisioned URL from job output (in the Completed branch):
# job output format: "PROVISIONED:https://contoso.sharepoint.com/sites/marketing-hub-2026"
@{replace(body('Get_job_output')?['value'], 'PROVISIONED:', '')}

param(
    # TenantName: the subdomain portion only — e.g. "contoso" not "contoso.sharepoint.com"
    [Parameter(Mandatory=$true)] [string]$TenantName,

    # SiteTitle: the display name shown in the browser tab and SharePoint navigation
    [Parameter(Mandatory=$true)] [string]$SiteTitle,

    # SiteUrlAlias: the path segment after /sites/ — must be lowercase letters, numbers,
    # and hyphens only; max 40 characters; validated in Power Automate before approval
    [Parameter(Mandatory=$true)] [string]$SiteUrlAlias,

    # SiteType: determines which New-PnPSite type is used and whether an M365 Group is created.
    # Exactly three valid values — enforced here AND validated in the Logic App before this runs:
    #   CommunicationSite                → publishing site, no group, -Url and -Owner params
    #   TeamSiteWithoutMicrosoft365Group → collaboration site, no group, -Url and -Owner params
    #   TeamSite                         → group-connected, -Alias param (NOT -Url), creates M365 Group
    [Parameter(Mandatory=$true)]
    [ValidateSet("CommunicationSite","TeamSiteWithoutMicrosoft365Group","TeamSite")]
    [string]$SiteType,

    # Template: controls which lists, libraries, and folders are provisioned
    [Parameter(Mandatory=$true)]
    [ValidateSet("General","Project Team","HR Department","IT Service Desk")]
    [string]$Template,

    # PrimaryOwnerEmail: UPN of the person who owns the site — gets Site Collection Admin rights
    [Parameter(Mandatory=$true)] [string]$PrimaryOwnerEmail,

    # SecondaryOwnerEmail: optional second site collection admin (can be left empty)
    [string]$SecondaryOwnerEmail = "",

    # Description: shown on the SharePoint site card in the home page and search results
    [string]$Description = "",

    # RequestItemId: ID of the originating row in the Site Requests list —
    # included in all log output so errors can be traced back to the request
    [Parameter(Mandatory=$true)] [string]$RequestItemId
)

# Convert all non-terminating errors to terminating so the catch block always fires.
# Without this, some PnP cmdlet failures only write to the error stream and continue.
$ErrorActionPreference = 'Stop'

# Build both URLs up front — used multiple times and must be consistent
$adminUrl    = "https://$TenantName-admin.sharepoint.com"
$siteFullUrl = "https://$TenantName.sharepoint.com/sites/$SiteUrlAlias"

Write-Output "INFO: RequestID=$RequestItemId | Provisioning started | Target: $siteFullUrl"

try {
    # ── Establish two named connections ──────────────────────────────────────
    #
    # WHY TWO CONNECTIONS?
    #   - Set-PnPTenantSite calls the Tenant Admin REST API at *-admin.sharepoint.com
    #     It MUST be called using the admin connection, not the site connection.
    #     Calling it on a site connection silently fails or returns 403.
    #   - Add-PnPSiteCollectionAdmin, New-PnPList, Add-PnPFolder all operate on
    #     the site itself and must use the site connection.
    #
    # -ReturnConnection stores the connection object in a variable.
    # Passing -Connection to each cmdlet explicitly controls which endpoint is called.

    $adminConn = Connect-PnPOnline -Url $adminUrl -ManagedIdentity -ReturnConnection
    Write-Output "INFO: Admin connection established ($adminUrl)"

    # ── Create the site ───────────────────────────────────────────────────────
    #
    # -Wait blocks until the site is fully provisioned and responsive.
    # Without it, Connect-PnPOnline to the new site fails with 404.
    #
    # VERIFIED PARAMETER SETS (PnP PowerShell 2.x docs — common source of bugs):
    #
    #   CommunicationSite:
    #     Valid: -Url (full URL), -Owner (UPN), -SiteDesign (Blank|Topic|Showcase)
    #     NO -Description — passing it throws ParameterBindingException.
    #     Description applied after creation via Set-PnPWeb (see below).
    #
    #   TeamSiteWithoutMicrosoft365Group:
    #     Valid: -Url (full URL), -Owner (UPN)
    #     NO -Description, NO -SiteDesign — both throw ParameterBindingException.
    #     No M365 Group. Gets Owners/Members/Visitors SharePoint groups.
    #
    #   TeamSite (group-connected):
    #     Valid: -Alias (alias only — NOT -Url), -Owner (UPN), -Description
    #     -Description IS supported here (sets both Group and site description).
    #     Passing -Url for this type throws ParameterBindingException.
    #     SharePoint auto-constructs URL as /sites/{Alias}.
    #     Creates M365 Group: Exchange mailbox, Planner, Group email address.
    #     -Alias must be unique across ALL M365 Groups, not just SharePoint.

    if ($SiteType -eq "CommunicationSite") {
        # -Description is NOT in the CommunicationSite parameter set.
        # It is applied via Set-PnPWeb after connecting (see below).
        New-PnPSite -Type CommunicationSite `
            -Title      $SiteTitle `
            -Url        $siteFullUrl `
            -Owner      $PrimaryOwnerEmail `
            -SiteDesign Blank `
            -Wait `
            -Connection $adminConn

    } elseif ($SiteType -eq "TeamSiteWithoutMicrosoft365Group") {
        # Neither -Description nor -SiteDesign exist in this parameter set.
        # Description applied via Set-PnPWeb after connecting (see below).
        New-PnPSite -Type TeamSiteWithoutMicrosoft365Group `
            -Title      $SiteTitle `
            -Url        $siteFullUrl `
            -Owner      $PrimaryOwnerEmail `
            -Wait `
            -Connection $adminConn

    } else {
        # TeamSite (group-connected): -Alias NOT -Url; -Description IS supported here.
        # URL auto-created as: https://{TenantName}.sharepoint.com/sites/{SiteUrlAlias}
        # Also creates M365 Group (Exchange mailbox, Planner, Group email address).
        New-PnPSite -Type TeamSite `
            -Title       $SiteTitle `
            -Alias       $SiteUrlAlias `
            -Owner       $PrimaryOwnerEmail `
            -Description $Description `
            -Wait `
            -Connection  $adminConn
    }
    Write-Output "INFO: Site created at $siteFullUrl"

    # Connect to the new site for all subsequent site-level operations
    $siteConn = Connect-PnPOnline -Url $siteFullUrl -ManagedIdentity -ReturnConnection
    Write-Output "INFO: Site connection established ($siteFullUrl)"

    # Apply description for the two types that do not accept it during New-PnPSite.
    # Set-PnPWeb (not Set-PnPSite) is the correct cmdlet — it sets the web-level
    # Description property. Set-PnPSite does not have a -Description parameter.
    if ($SiteType -ne "TeamSite" -and $Description -ne "") {
        Set-PnPWeb -Description $Description -Connection $siteConn
        Write-Output "INFO: Description applied via Set-PnPWeb"
    }

    # ── Add site collection admins ────────────────────────────────────────────
    #
    # New-PnPSite -Owner already sets PrimaryOwnerEmail as site collection admin.
    # We call Add-PnPSiteCollectionAdmin to also add SecondaryOwner when provided.
    # The Managed Identity account is also an admin via its SharePoint Admin role —
    # that's expected; it won't appear in the SharePoint "Site admins" UI as a named user.
    # -Owners accepts a string (UPN) or array of strings.

    if ($SecondaryOwnerEmail -ne "") {
        Add-PnPSiteCollectionAdmin -Owners @($SecondaryOwnerEmail) -Connection $siteConn
        Write-Output "INFO: Secondary admin set: $SecondaryOwnerEmail"
    }

    # ── Apply template-specific structure ─────────────────────────────────────
    #
    # Each template adds the lists and folder structure appropriate to that team type.
    # -ErrorAction SilentlyContinue on New-PnPList prevents duplicate-name errors
    # if this runbook is re-run on the same site (e.g. after a partial failure).

    switch ($Template) {
        "Project Team" {
            # Project sites: task tracking + risk register + structured document folders
            New-PnPList -Title "Project Tasks" -Template GenericList `
                -ErrorAction SilentlyContinue -Connection $siteConn
            New-PnPList -Title "Risk Register" -Template GenericList `
                -ErrorAction SilentlyContinue -Connection $siteConn
            Add-PnPFolder -Name "Project Plans"  -Folder "Shared Documents" -Connection $siteConn
            Add-PnPFolder -Name "Deliverables"   -Folder "Shared Documents" -Connection $siteConn
            Add-PnPFolder -Name "Meeting Notes"  -Folder "Shared Documents" -Connection $siteConn
            Add-PnPFolder -Name "Archive"        -Folder "Shared Documents" -Connection $siteConn
        }
        "HR Department" {
            # HR sites: job postings list + policy-focused document structure
            New-PnPList -Title "Job Postings" -Template GenericList `
                -ErrorAction SilentlyContinue -Connection $siteConn
            Add-PnPFolder -Name "Policies"   -Folder "Shared Documents" -Connection $siteConn
            Add-PnPFolder -Name "Templates"  -Folder "Shared Documents" -Connection $siteConn
            Add-PnPFolder -Name "Archive"    -Folder "Shared Documents" -Connection $siteConn
            # HR sharing is set to Disabled further below using $adminConn
        }
        "IT Service Desk" {
            # IT sites: tickets + known issues + operational runbooks
            New-PnPList -Title "Tickets"      -Template GenericList `
                -ErrorAction SilentlyContinue -Connection $siteConn
            New-PnPList -Title "Known Issues" -Template GenericList `
                -ErrorAction SilentlyContinue -Connection $siteConn
            Add-PnPFolder -Name "Runbooks"   -Folder "Shared Documents" -Connection $siteConn
            Add-PnPFolder -Name "Procedures" -Folder "Shared Documents" -Connection $siteConn
        }
        default {
            # General: minimal structure — two folders, no extra lists
            Add-PnPFolder -Name "Working Documents" -Folder "Shared Documents" -Connection $siteConn
            Add-PnPFolder -Name "Archive"           -Folder "Shared Documents" -Connection $siteConn
        }
    }
    Write-Output "INFO: Template '$Template' applied"

    # ── Set sharing policy ────────────────────────────────────────────────────
    #
    # IMPORTANT: Set-PnPTenantSite must use $adminConn — it calls the SPO Tenant
    # Admin REST API at *-admin.sharepoint.com. Calling it with $siteConn (the
    # regular site connection) will throw "Access denied" or silently do nothing.
    #
    # SharingCapability values:
    #   Disabled                        = no external sharing whatsoever
    #   ExistingExternalUserSharingOnly = existing guests only, no new invites
    #   ExternalUserSharingOnly         = authenticated external users
    #   ExternalUserAndGuestSharing     = anyone (including anonymous links)

    if ($Template -eq "HR Department") {
        # HR sites must never be accessible to anyone outside the organisation
        Set-PnPTenantSite -Url $siteFullUrl `
            -SharingCapability Disabled `
            -Connection $adminConn
        Write-Output "INFO: External sharing disabled (HR policy)"
    } else {
        # All other templates: existing guests may access, but no new external invites
        Set-PnPTenantSite -Url $siteFullUrl `
            -SharingCapability ExistingExternalUserSharingOnly `
            -Connection $adminConn
        Write-Output "INFO: Sharing set to ExistingExternalUserSharingOnly"
    }

    # ── Signal success ────────────────────────────────────────────────────────
    #
    # The Logic App reads the job output stream via "Get job output", which returns
    # all Write-Output lines as a single newline-delimited string in body('Get_job_output')?['value'].
    # Because the output contains multiple INFO: lines, a simple replace() would corrupt it.
    # The Logic App expression to extract only the URL is:
    #
    #   @{trim(last(split(body('Get_job_output')?['value'], 'PROVISIONED:')))}
    #
    # This splits the output on "PROVISIONED:" and takes the last segment — the URL.
    # trim() removes any trailing newline characters.
    #
    # The PROVISIONED: prefix must be unique in the output — do not use it in any
    # other Write-Output statement in this runbook.

    Write-Output "PROVISIONED:$siteFullUrl"

} catch {
    # $_.Exception.Message contains the PnP / SharePoint REST error text.
    # Re-throwing the exception causes the Automation job to end with status
    # "Failed", which the Logic App Do-Until loop detects and routes to the
    # Catch scope — which logs the error and sends the failure email.

    $msg = $_.Exception.Message
    Write-Error "FAILED | RequestID=$RequestItemId | $siteFullUrl | $msg"
    throw "Provisioning failed for $siteFullUrl — $msg"
}

# Title column — unique, sortable identifier for each log row
ERR-@{variables('requestItemId')}-@{formatDateTime(utcNow(),'yyyyMMdd-HHmmss')}

# RequestID column — links back to the originating Site Requests list row
@{variables('requestItemId')}

# FailedStep column — name of the specific Logic App action that failed
# result('Try') returns ALL actions; filter() isolates the first one with status=Failed
@{first(filter(result('Try'), item()?['status'] == 'Failed'))?['name']}

# ErrorMessage column — human-readable message from the failed action
@{first(filter(result('Try'), item()?['status'] == 'Failed'))?['error']?['message']}

# RawError column — full error code for deep debugging
@{first(filter(result('Try'), item()?['status'] == 'Failed'))?['error']?['code']}

# RunbookJobID column — paste this into Azure Automation → Jobs for the full PS output
@{variables('runbookJobId')}

# LogLevel column — hardcode to "Error" for all Catch scope entries
Error