Using SharePoint PnP with Azure Automation

In this article I will go over how to use PowerShell PnP with Azure Automation. This will demonstrate how to create the Azure Automation account, Run Book, install modules and configure the variables and credentials required.

The example script will analyse SharePoint pages and place any hyperlinks into a SharePoint list. This example script would be useful if a migration has been complete from an on premise system and you need to analyse what hyperlinks are still pointing to it.

Things to do…

  • Ensure you have an azure portal to use with credits. If you don’t have an azure portal you can sign up for a free trial
  • Install the PowerShell PnP Cmdlets
  • Have an O365 subscription as we need a SharePoint site. If you don’t have a subscitpion you can sign up for a Developer Trial

The SharePoint stuff…

This demo will be based on a script that can be downloaded from my Github. Download this script and store it locally on you machine. This will be what we use in the run book.

We need to create at minimum two sites for this script to work. One of the sites should be called the following:

  • InternalIT

The other sites will contain the pages that will be analysed for hyperlinks.

Internal IT site Configuration

The Internal IT site is the site that needs to have two custom lists created:

  • sitesAutomation
  • Migration Hyperlink Progress

sitesAutomation Configuration – List Columns

Column NameColumn Type

This list should be used to contain the sites that you want the script to analyse. The script and list could easily be amended to use an active field that would filter out any sites that you don’t want the script to be run against.

Example of how the sites list should be populated. The URLs for the sites in my list have been redacted.

Migration Hyperlink Progress – List Columns

Column NameColumn Type

For both lists set the Title value to N/A or set it to not required. The scripts don’t add any information into this column as the

The PowerShell

I’m not going to go into low level detail into what the PowerShell script does but I will provide a summary.

The first section of the script are connecting to the Azure automation account variables and credentials.

# will get the variables / credentials in the azure automation service
$itsite = Get-AutomationVariable -Name 'ITSite'
$cred = Get-AutomationPSCredential -Name 'admin'
$regex = Get-AutomationVariable -Name 'regex'
$list = "SitePages"

The PowerShell script will take all the items in the sites automation list and iterate through each one. Once the site has been connected to the script then connects to the pages list and returns all the pages.

#connection for Internal IT site that contains a list of sites to run the script against

$internalITCon = Connect-PnPOnline -Url $itsite -Credentials $cred
# create object of all the sites
$sitesObj = (Get-PnPListItem -List 'sitesAutomation' -Fields "url" -Connection $internalITCon)

The metadata for each page is then stored in a variable. Each page’s metadata has the following properties captured:

  • Title
  • Fileref
  • CanvasContent1
$page_title = $page.Get_Item("Title")
$fileref = $page.Get_Item("FileRef")
$canvascontent = $page.Get_Item("CanvasContent1")

The script then checks to see if the variable canvas content has any content in. If it does a check is conducted against a regular expression. When a match is found in the canvas content property it is then added into a collection.

if ($canvascontent.Length -gt 0) 
            # hash table of the results that match the href regular expression
            $hrefmatches = ($canvascontent | select-string -pattern $regex -AllMatches).Matches.Value

The final part of the script is each match that occurs is added into the Migration Hyperlink Progress list. If more than one link is found in the script then multiple items are added.

# itterate around each regular expression match and write it out into the output csv that is pipe delimited 
foreach($hrefmatch in $hrefmatches)
     $internalITCon2 = Connect-PnPOnline -Url $itsite -Credentials $cred
     $donotuse = Add-PnPListItem -List "Migration Hyperlink Progress" -Values @{"Title" = "N/A"; "SiteTitle" = $site_title;"PageTitle" = $page_title; "Ref" = $fileref; "Match" = $hrefmatch} -Connection $internalITCon2

When using PnP PowerShell each time a cmdlet is called it has to be captured in a variable even if you don’t require the return value.

In the above block the line: $donotuse = Add-PnPListItem -List “Migration Hyperlink Progress” -Values @{ect….} -Connection $internalITCon2 shows an example of when the return value isn’t required but is still needed.

Azure Configuration

Log into the azure portal with the account that you have.

From here we need to create a azure automation resource. To do this:

  1. Create a resource
  2. search for ‘Automation’
  3. Enter the required information for the Automation account. When selecting the location add a region that is close to your geolocation.

Configure automation account


Create a credential that has access to the SharePoint environment. In my script the credential was named ‘admin‘. If you don’t want to edit the script that ensure that the credential name is admin.


The variables need to be created that the script uses. The variables that we need to create are shown in the table:

Variable NameTypeValue
ITSitestring[[Enter the url to the Internal IT]]
regexstring <a\s+(?:[^>]*?\s+)?href=([“])(.*?)\1>

Module to install

The PnP PowerShell SharePoint module needs to be installed. This is because by default they are not part of an automation account. The module that needs to be installed is:

  • SharePointPnPPowerShellOnline

Create Runbook

The runbook is created as part of the automation account. Multiple runbooks can be created in a single automation account. The name can be anything but the runbook type needs to be PowerShell.

Where to create the runbook

Once it has been created an online editor is visible. From here paste in the PowerShell script that has been downloaded from this github file

Adding the PowerShell script into the runbook.

Once the script has been added it needs to be saved and published. After this the script can be run.

If the script runs successfully then the Migration Hyperlink Progress list will add items to it.

If the script doesn’t check that these are correct:

  • All variables in the azure automation account have been created and when called for in the PowerShell script the names are correct
  • You have added a site into the sitesAutomation list
    • this site contains pages with hyperlinks in it
  • The module has been added for the SharePoint PnP PowerShell cmdlets
  • The credentials have been added in the automation account


The above guide should demonstrate how to get a SharePoint PnP PowerShell script up and running using Azure Automation. I’ll be adding another article in this series on how to schedule runbook runs and also look at how the above can work if your organisation requires MFA (multi-factor authentication).

2 thoughts on “Using SharePoint PnP with Azure Automation

    1. Hi Seb, unfortunately I haven’t had time yet. I would recommend that you take a look at creating an Azure AD App and that is given the appropriate permissions. Don’t think MFA would be possible as there isn’t a user prompt when running.


Leave a Reply

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

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

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.