<div class="blog"><main class="content"><nav class="topbar topbar-light blog"><div class="topbar-logo"><a class="topbar-logo-link" href="/"><img class="topbar-logo-image" src="https://cdn.cadenzasoft.com/electric/images/logo.svg"></a></div><nav class="topbar-menu"><button class="topbar-toggle">Menu</button><ul class="topbar-list"><li class="topbar-item"><a class="topbar-link " href="/" target="undefined"><span>Home</span></a></li><li class="topbar-item"><a class="topbar-link " href="/what-we-deliver" target="undefined"><span>What We Deliver</span></a></li><li class="topbar-item"><a class="topbar-link " href="/why-cadenza" target="undefined"><span>Why Cadenza</span></a></li><li class="topbar-item"><a class="topbar-link " href="/clients" target="undefined"><span>Clients</span></a></li><li class="topbar-item"><a class="btn btn-accent btn-sm" href="/" target="undefined">Talk to us</a></li></ul></nav></nav><div class="blog"><article class="container"><header><small>By Thomas Berman <span>| February 5, 2019</span></small><h1>SharePoint Online PnP PowerShell Provisioning using Flow and Azure Automation</h1></header><div class="content"><article><p>Modern SharePoint gives us a number of options for using the PnP Provisioning Engine. One of the most common is to trigger a Microsoft Flow which triggers a resource in Azure (in our case an Automation Runbook) which then applies a PnP Provisioning Template. With the introduction of site designs and site scripts, we have a number of ways to trigger Flows in provisioning scenarios.</p><p>Common flow triggers in provisioning scenarios include:</p><ul><li>Site Designs & Site Scripts</li><li>Creating an item in a SharePoint List</li><li>Manually triggering via user custom actions (or similar)</li></ul><p>For an excellent guide to site designs and site scripts and how to set them up to trigger Flows, I highly recommend Laura Kokkarinen’s blog post: <a href="https://laurakokkarinen.com/the-ultimate-guide-to-sharepoint-site-designs-and-site-scripts/">The Ultimate Guide To SharePoint Site Designs And Site Scripts</a>.</p><p>This blog post will focus on setting up a <em>Flow which triggers PnP PowerShell code in an Azure Automation Runbook</em>. You will be able to apply the steps in this post to any Flow trigger.</p><h3>Table of Contents</h3><ol><li><a href="#why-azure-automation-and-not-azure-functions">Why Azure Automation and not Azure Functions?</a></li><li><a href="#setting-up-azure-automation">Setting Up Azure Automation</a><ol><li><a href="#create-azure-automation-account">Create Azure Automation Account</a></li><li><a href="#add-pnp-powershell-module">Add PnP PowerShell Module</a></li><li><a href="#create-runbook">Create Runbook</a></li><li><a href="#create-azure-automation-variables">Create Azure Automation Variables</a></li><li><a href="#create-azure-automation-credentials">Create Azure Automation Credentials</a></li><li><a href="#receiving-parameters-from-flow">Receiving Parameters (from Flow)</a></li></ol></li><li><a href="#setting-up-microsoft-flow">Setting Up Microsoft Flow</a><ol><li><a href="#set-up-flow-trigger">Set Up Flow Trigger</a></li><li><a href="#set-up-azure-automation-runbook-job-trigger">Set Up Azure Automation Runbook Job Trigger</a></li></ol></li><li><a href="#solution-and-code">Solution and Code</a><ol><li><a href="#site-script-trigger-apply-pnpprovisioningtemplate">Site Script Trigger – Apply PnPProvisioningTemplate</a></li></ol></li></ol><p><a name="why-azure-automation-and-not-azure-functions"></a></p><h3>Why Azure Automation and not Azure Functions?</h3><p>Mainly because in this scenario, we want to run PnP PowerShell.</p><ol><li>PowerShell is not available as a language option in the Azure Functions V2 runtime, and is only available as an “experimental” language in the V1 runtime</li><li>Azure Automation gives us an extremely simple way to import the SharePointPnPPowerShellOnline module</li><li>Azure Automation allows us to encrypt credentials and variables and decrypt them at runtime – securing our app-secret/service account credentials/Azure Storage key etc.</li><li>Role-based Access Control (RBAC) offers granular security ensuring that the accounts we designate have only exactly the level of access we assign</li><li>Azure Automation allows our scripts to run for up to 3 hours vs 10 mins for Azure Functions. This could be an issue with very long-running provisioning scenarios requiring you to design around the limitation in Azure Functions (“function chaining”, for example)</li><li>Azure Automation offers a pay-for-use model similar to Azure Functions (they’re cheap)</li></ol><p>Azure Automation is very feature rich and has a lot to offer outside of what we will discuss, however, for what we’re going to achieve in this blog post, we will effectively view them as “Functions for PowerShell”.</p><p><a name="setting-up-azure-automation"></a></p><h3>Setting Up Azure Automation</h3><p><a name="create-azure-automation-account"></a></p><h5>Create Azure Automation Account</h5><p>In the Azure Portal, go to “Create Resource” and search for “Automation”. It should show up as the first result with publisher Microsoft and category “Developer Tools”. Give it a name and resource group then “Create”.</p><figure><img class="blog-img-shadow" src="https://i1.wp.com/spmaestro.com/wp-content/uploads/2019/02/CreateAutomationResource-1.png?w=1500&ssl=1"></figure><p><a name="add-pnp-powershell-module"></a></p><h5>Add PnP PowerShell Module</h5><p>Go to your newly created Azure Automation Account and scroll down and choose “Modules” under the “Shared Resources” heading.</p><p>In the right-hand panel, choose “Browse Gallery” and search “SharePointPnPPowerShellOnline”. Choose the first one (published by Erwin van Hunen, of course), and click “Import” then “Ok” in the next dialog. It should take a few minutes to import the module. After that, you’re ready to create a Runbook which will contain your PowerShell script and use PnP PowerShell cmdlets. Pretty simple!</p><figure><img class="blog-img-shadow" src="https://i0.wp.com/spmaestro.com/wp-content/uploads/2019/02/importPnPPowerShell.png?w=1500&ssl=1"></figure><p><a name="create-runbook"></a></p><h5>Create Runbook</h5><p>Go back to your Azure Automation Account and scroll down to "Runbooks" under the "Process Automation" heading.</p><p>Choose "Create Runbook". Give it a name and select "PowerShell" from the "Runbook type" drop-down then "Create".</p><figure><img class="blog-img-shadow" src="https://i2.wp.com/spmaestro.com//wp-content/uploads/2019/02/createRunbook.png?ssl=1"></figure><p>Once the Runbook is created, you will see it in your list of Runbooks on your Automation Account. To add code, simply click on the Runbook name and click "edit" at the top to edit code directly in the browser. Another blog post will discuss better options for editing code for your Azure Automation Runbooks.</p><p><a name="create-azure-automation-variables"></a></p><h5>Create Azure Automation Variables</h5><p>Go back to the Automation Account (the top level for the resource, not the Runbook level) and scroll down to "Variables" under "Shared Resources". Give your variable a name and a data type then choose if you want to encrypt the variable or not.</p><p>Depending on what kind of information you are storing, you may want to encrypt. For example: Client Secret (for SharePoint registered App-Only authentication), Storage Account Connection strings (if you are using an Azure Storage Account to store a PnP Template). If you are running your script with a service account, you will want to use the <a href="#create-azure-automation-credentials">Azure Automation Credentials</a>. An example of these scenarios and code are given later <a href="#solution-and-code">in the solution and code section</a>.</p><figure><img class="blog-img-shadow" src="https://i1.wp.com/spmaestro.com/wp-content/uploads/2019/02/createAutomationVariable-2.png?w=1500&ssl=1"></figure><p>Encrypting a variable prevents it from being read except by your Runbook at runtime.</p><figure><img class="blog-img-shadow" src="https://i1.wp.com/spmaestro.com/wp-content/uploads/2019/02/encryptedVariable.png?w=1500&ssl=1"></figure><p>To read an encrypted variable at runtime in your PowerShell runbook, use the Get-AutomationVariable cmdlet and pass in the name of your variable. You can assign it to a variable in your PowerShell code for use in your script.</p><div class="code-container"><button class="btn btn-sm btn-copy"><span class="icon-12-overlap"></span></button><pre><code class="code" data-mode="text" ref="code">$appSecret = Get-AutomationVariable -Name "ClientSecret"</code></pre></div><p><a name="create-azure-automation-credentials"></a></p><h5>Create Azure Automation Credentials</h5><p>If you are using a service account to authenticate your script against SharePoint (or need account credentials for some other authentication), you can certainly store your account credential values as encrypted variables. Better yet, you can save yourself a step and save them as an Automation Credential and return a PSCredential object in one line in your script.</p><p>Go back to the Automation Account (the top level for the resource, not the Runbook level) and scroll down to "Credentials" under "Shared Resources". Give your credential a name and fill in the credential fields with your account information.</p><figure><img class="blog-img-shadow" src="https://i1.wp.com/spmaestro.com//wp-content/uploads/2019/02/createAutomationCredential.png?ssl=1"></figure><p>You can then retrieve your Automation Credential as a PSCredential object for use in your script (for example: passing it into the Connect-PnPOnline) using Get-AutomationPSCredential and passing in the name of your credential to the -Name parameter</p><div class="code-container"><button class="btn btn-sm btn-copy"><span class="icon-12-overlap"></span></button><pre><code class="code" data-mode="text" ref="code">$serviceAccountCred = Get-AutomationPSCredential -Name 'SPServiceAccount' ... Connect-PnPOnline -Url https://contoso.sharepoint.com -Credentials $serviceAccountCred</code></pre></div><p><a name="receiving-parameters-from-flow"></a></p><h5>Receiving Parameters (from Flow)</h5><p>Receiving parameters to your Runbook script are basically the same as receiving parameters in any PowerShell script. You define the parameters and name the variables in which they will be stored. When <a href="set-up-flow-trigger">setting up your flow</a> using the “Azure Automation – Create Job” connector, you will see these parameters and be able to pass in values.</p><div class="code-container"><button class="btn btn-sm btn-copy"><span class="icon-12-overlap"></span></button><pre><code class="code" data-mode="text" ref="code">Param ( [Parameter (Mandatory= $true)] [String] $siteUrl )</code></pre></div><p><a name="setting-up-microsoft-flow"></a></p><h3>Setting Up Microsoft Flow</h3><p>Regardless of whether your provisioning scenario calls for your Flow to be triggered by a Site Script, List Item creation, or some other trigger, the Flow itself will look largely the same. We'll set up a Flow as if it is triggered by a Site Script.</p><p>First go to Microsoft Flow at <a href="http://flow.microsoft.com/">flow.microsoft.com</a>.</p><p>Go To "My Flows" and "New" > "Create from blank"</p><p><a name="set-up-flow-trigger"></a></p><h5>Set Up Flow Trigger</h5><p>In this scenario, where the Flow is triggered by a Site Script, search for the "When a HTTP request is received" trigger and paste in the following JSON into the "Request Body JSON Schema". This will give your Flow access to the "webUrl" property which is automatically passed in by the Site Script.</p><div class="code-container"><button class="btn btn-sm btn-copy"><span class="icon-12-overlap"></span></button><pre><code class="code" data-mode="text" ref="code">{ "type": "object", "properties": { "webUrl": { "type": "string" }, "parameters": { "type": "object", "properties": { "event": { "type": "string" }, "product": { "type": "string" } } } } }</code></pre></div><figure><img class="blog-img-shadow" src="https://i0.wp.com/spmaestro.com//wp-content/uploads/2019/02/flowTrigger-1.png?ssl=1"></figure><p><strong>NOTE:</strong> <em>After you create the next step (Azure Automation -- Create Job) and save, the "HTTP POST URL" field will be automatically populated with a URL. You will use this value in your Site Script to trigger this Flow. For full details on how to create a Site Script that triggers your Flow see <a href="https://laurakokkarinen.com/the-ultimate-guide-to-sharepoint-site-designs-and-site-scripts/#creating-a-site-script">Laura Kokkarinen's blog post</a>.</em></p><p><a name="set-up-azure-automation-runbook-job-trigger"></a></p><h5>Set Up Azure Automation Runbook Job Trigger</h5><p>Next add a New Step. Search for "Azure Automation" and choose "Create Job". From the drop-down lists, select the Subscription and Resource Group you used to create your Automation Account, after which the "Automation Account" drop-down will be populated with the name of your Automation Account. Select the Runbook you created in the next drop-down.</p><p>Here we can see that it grabs the SiteUrl parameter which I set as required Click inside the "Runbook Parameter" field to bring up the "Dynamic Content" pane and select "webUrl".</p><p>Depending on your scenario, you will want to decide what to do with with the "Wait for job" option. If you set this to "Yes", your Flow will wait for your Runbook job to complete, which would allow you to take further action in the Flow.</p><figure><img class="blog-img-shadow" src="https://i0.wp.com/spmaestro.com/wp-content/uploads/2019/02/flowCreateAutomationJob-1.png?fit=750%2C583&ssl=1"></figure><p>Now you're all set to use a Flow to trigger your Azure Automation Runbook and run some PnP PowerShell code! We'll look at a scenario in the next section with code and configuration samples.</p><p><a name="solution-and-code"></a></p><h3>Solution and Code</h3><p><a name="site-script-trigger-apply-pnpprovisioningtemplate"></a></p><h5>Site Script Trigger -- Apply PnPProvisioningTemplate</h5><p>In this scenario, a user creates a site via the self-service portal. A Site Design has been set up which contains a Site Script which will call our Flow and pass in the URL of the newly created site. Our Flow will then call an Azure Automation Runbook to apply a PnP Provisioning Template to the site. The provisioning template is stored in an Azure Storage Account File Share. We will be using SharePoint App-Only authentication with a Client ID and Client Secret.</p><p>First you will want to <a href="https://laurakokkarinen.com/the-ultimate-guide-to-sharepoint-site-designs-and-site-scripts/#creating-a-site-script">set up your Site Design and Site Script</a>. Next, set up your Flow to <a href="#set-up-flow-trigger">receive the "webUrl" property from the Site Script</a> and <a href="#set-up-azure-automation-runbook-job-trigger">create an Azure Automation Runbook Job</a>. Remember to set up your parameter (siteUrl) and "Publish" so that it is available when you set up your Flow.</p><p>Now we need to set up an Azure Storage Account and File Share to store our PnP Provisioning Template. Depending on your scenario you might want to create an entirely new Storage Account in the Azure portal, or add a File Share to an existing storage account.</p><p>Here, I've created a new File Share in a Storage Account and uploaded my PnP Provisioning Template:</p><figure><img class="blog-img-shadow" src="https://i0.wp.com/spmaestro.com/wp-content/uploads/2019/02/filesharetemplate-1.png?w=1500&ssl=1"></figure><p>Next, you’ll need to copy the connection string for this Storage Account. Go back to the Storage Account level, and find “Access Keys” under the Settings heading. Copy this connection string then move over to your Automation Account.</p><figure><img class="blog-img-shadow" src="https://i0.wp.com/spmaestro.com/wp-content/uploads/2019/02/connectionString.png?w=1500&ssl=1"></figure><p><a href="#create-azure-automation-variables">Create a new encrypted variable</a> in your Automation Account. Give it a name and paste in the Connection String.</p><p>Create an unencrypted variable in your Automation Account for the name of the File Share you uploaded your PnP Provisioning Template to.</p><p>Create an unencrypted variable in your Automation Account for the name of your PnPProvisioning Template file including the extension (demotemplate.xml in my case).</p><p>Next we'll need to <a href="https://docs.microsoft.com/en-us/sharepoint/dev/solution-guidance/security-apponly-azureacs">register an application in SharePoint and get a Client ID and Client Secret and set up App-Only permissions.</a> Copy both values and create variables for both of them (encrypted variable for the Client Secret).</p><p>You're now ready to write some PowerShell to get the webUrl, authenticate against SharePoint, get the PnP Provisioning Template from your File Share, and Apply the Provisioning Template!</p><p>Our Automation Variables at this point should be:</p><ul><li><code>clientId</code></li><li><code>clientSecret</code></li><li><code>storageConnectionString</code></li><li><code>fileShareName</code></li><li><code>provisioningTemplateFileName</code></li></ul><p>Go to your Runbook and click "Edit" to get to the code editor. Remember to "Publish" in order for this code to run.</p><div class="code-container"><button class="btn btn-sm btn-copy"><span class="icon-12-overlap"></span></button><pre><code class="code" data-mode="text" ref="code">Param ( [Parameter (Mandatory= $true)] [String] $siteUrl ) $appId = Get-AutomationVariable -Name "clientId" $appSecret = Get-AutomationVariable -Name "clientSecret" $storageConnectionString = Get-AutomationVariable -Name "storageConnectionString" $fileShareName = Get-AutomationVariable -Name "fileShareName" $provisioningTemplateFileName = Get-AutomationVariable -Name "provisioningTemplateFileName" try { $tempFile = New-TemporaryFile $storageContext = New-AzureStorageContext -ConnectionString $storageConnectionString Get-AzureStorageFileContent -Context $storageContext -ShareName $fileShareName -Path "/$provisioningTemplateFileName" -Destination $tempFile -Force $tempFileContents = Get-Content $tempFile -Raw -ErrorAction:SilentlyContinue $inMemoryProvisioningTemplatePath = $tempFile.FullName Connect-PnPOnline -Url $Url -AppId $appId -AppSecret $appSecret Apply-PnPProvisioningTemplate -Path $inMemoryProvisioningTemplatePath } catch { Write-Output "Error occured: $PSItem" } finally { Disconnect-PnPOnline }</code></pre></div></article><input type="hidden" value="SharePoint Online PnP PowerShell Provisioning using Flow and Azure Automation"><input type="hidden" value="Cadenza"></div></article><div class="posts-list"><div class="container"></div></div></div></main></div>