Execute SharePoint Online PowerShell scripts using Power Automate

Most of us would have used PowerShell for SharePoint to manage SharePoint settings at the organization level and site collection level. SharePoint Online PowerShell commands are very efficient for batch operations for e.g creating multiple sites, list items etc. To use the SharePoint Online PowerShell commands

  • You must have the SharePoint Admin role or Global Administrator role in Office 365
  • Install the SharePoint Online Management Shell module

As you know you must be administrator to install a PowerShell module on your workstation which not everyone will have in corporate environments.

I often use a PowerShell script to enable App Catalog at a site collection level to test the PnP webparts & extensions before deploying at the tenant level app catalog based on requirement. If you are not an SPO admin then the dependency is with the SPO admin. In this blogpost I am going to show you how to automate this process by executing PowerShell script to enable App catlog in Azure using Power Automate.

Pre-requisite & permissions:

  • SPO Admin
  • Azure Subscription to create Automation account
  • Access to Premium connector (Azure Automation) in Power Automate
  • SharePoint List to collect details about the site which needs to have App catalog enabled

To complete this automation process, create the following two components

  1. Automation account in Azure with a Run Book to execute PowerShell script for enabling App Catalog in SP site
  2. Power automate flow to call the Run Book

Automation account in Azure with a Run Book to execute PowerShell script for enabling App Catalog in SP siteAutomation service in Azure is a cloud-based automation and configuration service that supports consistent management across your Azure and non-Azure environments. Go through the documentation from Microsoft to know about this powerfull service in Azure. Let’s use the service in Azure to create a simple Runbook with PowerShell code to enable App catalog in SPO site, you can do much more than this using this service. Refer to this link for the pricing details for the automation service in Azure.

Step 1: Go the Azure portal & create a resource Automation

Enter the name of the automation account, select the Subscription & resource group & click Create

Step 2: After the resource is created, go to the resource & click Modules Gallery under the section Shared Resources as shown below to add the PS SPO module

Search with the keyword “SharePoint” & click “Microsoft.Onlie.SharePoint.PowerShell” and then click Import. This step will the add the SharePoint online PowerShell module for us to use the available PS SPO cmdlets in Runbook.

Now click modules & verify if the SPO PowerShell is added & available.

Step 3: The next step is to add the user credentials (Username & Password) of the SPO admin which is safe & secure by not hardcoding the password on the Runbook. You can also use certificates or AppID AppSecret in PnP online Powershell for creating connection to SPO.

Step 4: Now we are good to create the Runbook, to create it click Runbooks under the section Process Automation and then click Create a runbook. Enter the Name of the Runbook, select the Runbook type to PowerShell and click Create.

Now let’s add the code by editing the runbook to enable app catalog. The section Dynamic Parameters on the code will be passed from flow. To connect to SharePoint Online we are using the SPO admin credentials created in the previous step. Find the code below

# Dynamic Parameters
param(
  [parameter(Mandatory=$true)]
  [string]$SiteURL = "https://domain.sharepoint.com/sites/contosoportal",
  [parameter(Mandatory=$false)]
  [boolean]$enableAppCatalog = 1
)
# Credentials
$myCred = Get-AutomationPSCredential -Name "SPOAdminCred" 
# Parameters
$AdminSiteURL = "https://domain-admin.sharepoint.com"
# Connect to SharePoint Online
Connect-SPOService -Url $AdminSiteURL -Credential $myCred 
# Get the Site Collection
$Site = Get-SPOSite -Identity $SiteURL 
# Enable App catalog
if($enableAppCatalog)
{Add-SPOSiteCollectionAppCatalog -Site $Site}
# Disable App catalog if false
else{Remove-SPOSiteCollectionAppCatalog -Site $Site}
# Get Site Collection Title
Write-Output $Site.Title

The runbook is now created, you can test the script by clicking on Test Pane & pass parameters (Site URL etc) to test it. Click Publish button as shown below to publish so that it can be called from Power Automate. It’s now time to create the flow

Power automate flow to call the Run Book

You can now create a flow with automated trigger from a SharePoint list to get the site url & Boolean value either to enable or disable the app catalog on the site. Here I will be using an Instant flow with trigger “Manually trigger a Flow”

Once the flow is created, add the action “Create Job” under the connector “Azure Automation” which is a premium connector.

Select the Azure Subscription which has the Automation account resource with runbook>Select Resource Group>Select Automation Account>Select the Runbook name which has PS script to enable app catalog. If there is a need to wait until the automation job completes then select Yes on the field “Wait for Job”. For the dynamic parameter, write a JSON to pass the mandatory & optional parameters to the runbook script. On this example I will be passing the Site URL & Boolean value to either enable or disable app catalog using JSON as below

{
  "SiteURL": "https://domain.sharepoint.com/sites/MyFirstTeam",
  "enableAppCatalog": 1
}

If using a SharePoint list, construct the above JSON dynamically with the URL

For the runbook parameters, you might also get an interface as shown below to pass the values (Site Url & enableAppcatalogbooleanvalue).

The flow is ready, run it to test now with parameters.

 Summary: The use case I’ve chosen is a simple one but azure automation can be a more powerful service to perform various automation tasks. Find few below

  • Write Python script in the Runbook
  • Many samples are available within the Runbook gallery (Create AD user, Display All provisioned site collections etc) under the section Process automation.
  • With the PowerShell type Runbook all the PS modules (Azure AD for automating AD account creation, PNP SP Online etc) are readily available for us to import easily.
  • There is a feature by name “Hybrid Runbook Worker Feature” available within Azure Automation account for us to connect Onpremise resources in Azure (e.g SharePoint Onpremise, Onpremise AD etc).
  • Create a Webhook to call the runbook from an External application by making a POST call
  • Call a custom built dll by importing them in to the Modules section
  • Create graphical Runbook with GUI to add cmdlets & to configure the steps
  • Create schedule linking a runbook

Hope you have enjoyed reading this post and find it useful. If you have any comments or feedback, please provide it on the comments section below.

8 thoughts on “Execute SharePoint Online PowerShell scripts using Power Automate

  1. Hello Mohamed,
    I followed your article, but I’m stuck in PowerAutomate where I cannot get the Parameters from the Azure PowerShell runbook.
    For reference, this is what I’m trying to achieve:

    #Dynamic parameters to pass to PowerAutomate
    param(
    [Parameter](Mandatory=$true)][string]$SiteTemplateURL
    [Parameter](Mandatory=$true)[string]$ApplyTemplatetoURL
    )
    #Credentials
    $myCred = Get-AutomationPSCredential -Name “SPOAdminCred”

    Connect-PnPOnline -url $SiteTemplateURL -Credentials $myCred
    #Create new Site
    New-PnPWeb -Title “This PNP Test 1” -Url $ApplyTemplatetoURL -Description “This is a test for PNP templates” -Locale 1033 -Template “STS#3”

    Connect-PnPOnline https://andreir2020.sharepoint.com/sites/BLABLA/$ApplyTemplatetoURL -Credentials $myCred

    Apply-PnPProvisioningTemplate “https://andreir2020.sharepoint.com/sites/BLABLA/PNPTemplate/PNPTemplate.pnp”

    Any help would be greatly appreciated.

    Thanks.
    Andrei

    Like

  2. Are you able to test the Runbook using the TEST PANE in Azure Interface? Make sure the Azure Subscription & the Office 365 account is using the same ID for login, if so you should be able to find the runbook from the resource group you have selected for creating the automation account. Hope it helps

    Like

  3. Hi Mohamed,

    I’m able to see the runbook in PowerAutomate, so I don’t think the issue is related to the O365 account IDs or something. The issue is with the PowerShell code that is in the Runbook.

    So this is the error from the test pane:

    Failed
    At line:3 char:19
    + [SiteTemplate](Mandatory=$true)]
    + ~
    Parameter declarations are a comma-separated list of variable names with optional initializer expressions.

    At line:3 char:19
    + [SiteTemplate](Mandatory=$true)]
    + ~
    Missing ‘)’ in function parameter list.

    and the code used (basically the same logic as yours)

    #Dynamic parameters to pass to PowerAutomate
    param(
    [SiteTemplate](Mandatory=$true)]
    [string]$SiteTemplateURL ,
    [AppyToSite](Mandatory=$true)]
    [string]$ApplyTemplatetoURL
    )
    #Credentials
    $myCred = Get-AutomationPSCredential -Name “SPOAdminCred”

    Connect-PnPOnline -url $SiteTemplateURL -Credentials $myCred
    #Create new Site
    New-PnPWeb -Title “This PNP Test 1” -Url $ApplyTemplatetoURL -Description “This is a test for PNP templates” -Locale 1033 -Template “STS#3”

    Connect-PnPOnline https://andreir2020.sharepoint.com/sites/blabla/$ApplyTemplatetoURL -Credentials $myCred

    Apply-PnPProvisioningTemplate “https://andreir2020.sharepoint.com/sites/blabla/PNPTemplate/PNPTemplate.pnp”

    I wondered why yours worked and mine doesn’t.

    Any ideas?

    Thanks.

    Like

  4. The paremeters section #dynamic parameters to pass to powerautomate is wrong. It should be similar to
    param(
    [parameter(Mandatory=$true)]
    [string]$SiteTemplateURL = “https://mydevashiq.sharepoint.com/sites/contosoportal”,
    [parameter(Mandatory=$true)]
    [string]$ApplyTemplatetoURL = “https://mydevashiq.sharepoint.com/sites/contosositeportal”
    )
    If you follow along this blog post, it should work as expected.
    https://ashiqf.com/2020/06/02/copy-apply-site-template-to-a-sharepoint-site-using-power-automate/
    Based on your requirement you will have to update the PowerShell script, test the script on your workstation first to sort out the error & then you can try running on the runbook.

    Like

    1. OK, So I made the powershell script, works fine from the Runbook:

      #Dynamic parameters to pass to PowerAutomate
      param(
      [Parameter(Mandatory=$true)]
      [string]$SiteTemplateURL,
      [Parameter(Mandatory=$true)]
      [string]$ApplyTemplatetoURL
      )
      #Credentials
      $myCred = Get-AutomationPSCredential -Name “SPOAdminCred”

      Connect-PnPOnline -url $SiteTemplateURL -Credentials $myCred
      #Create new Site
      New-PnPWeb -Title “This PNP Test 11” -Url $ApplyTemplatetoURL -Description “This is a test 11 for PNP templates” -Locale 1033 -Template “STS#3” -ErrorAction SilentlyContinue

      Connect-PnPOnline -url https://andreir2020.sharepoint.com/sites/blabla/$ApplyTemplatetoURL -Credentials $myCred

      Apply-PnPProvisioningTemplate “https://andreir2020.sharepoint.com/sites/NTTTDATA/blala/PNPTemplate.pnp”

      But when I run it from PowerAutomate with the logic:
      – when an item is created in a certain list, that has 2 columns: Site Template URL and Apply Template to URL
      – create job:
      –> Subscription: MySubscription
      –> Resource Group: PNPAutomation
      –> Automation Account: PNPAutomation
      –> Runbook name: OLASITE
      –> Wait for job: YES
      –> Runbook Parameter ApplyTemplateToURL: Site template URL taken from dynamic content
      –> Runbook Parameter SiteTemplateURL: Apply Template to URL taken from dynamic content

      and I get this in the Job Stream Details

      Connect-PnPOnline : Invalid URI: The format of the URI could not be determined. At line:11 char:1 + Connect-PnPOnline -url “$SiteTemplateURL” -Credentials $myCred + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [Connect-PnPOnline], UriFormatException + FullyQualifiedErrorId : System.UriFormatException,SharePointPnP.PowerShell.Commands.Base.ConnectOnline

      Any ideas?

      Thanks.

      Like

  5. Hi Mohammed,

    I seem to be experiencing problems with using credentials. I am getting the error that says the creds do not match any microsoft account. Any help is appreciated!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s