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 Name	Column Type
url	text

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.

Migration Hyperlink Progress – List Columns

Column Name	Column Type
SiteTitle	text
PageTitle	text
Ref	text
Match	text

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

# 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"

#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)

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

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.

• portal.azure.com

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

Credentials

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.

Variables

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

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.

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

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

Summary

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).