SharePoint Online: How to Schedule a PowerShell Script using Azure Automation?

Requirement: Schedule PowerShell scripts for SharePoint Online using Azure Automation.

How to use Azure Automation to Schedule PowerShell Scripts for SharePoint Online?

Azure Automation is a great option if you’re looking to schedule your recurring PowerShell tasks directly in the cloud without any dependency on your on-premises server or virtual machines through Windows Task scheduler. All we need to do is create a runbook for your PowerShell script, and set up a schedule. Assuming you have an existing Azure subscription, here are the steps to create an automated scheduled task in Azure at the high-level:

1. Create an Azure Automation Account
2. Setup Credentials to connect to SharePoint Online
3. Import Necessary Modules
4. Create a Runbook and add scripts to run
5. Schedule the PowerShell Script

Step 1: Create an Azure Automation Account

If you don’t have any existing Azure automation accounts created, you’ve to create one first.

• Login to Azure Portal https://portal.azure.com 
• Click on the “Create a resource” button >>  Search and find “Automation” >> Click on “Add” to create a new automation account.

  

• In my case, I’ve created an automation account as “SharePoint-Online-Reporting”. Once created, You’ll be taken into the automation account page that was created.

  

Step 2: Import Necessary PowerShell Modules

We are going to use the PnP PowerShell module for SharePoint Online in our PowerShell script. So, we have to import it first to our environment.

• Open the Automation account created >> Click on “Modules Gallery” in the left navigation
• Search and find “SharePointPnPPowerShellOnline” >> Click on the module link >> use “Import” button to import the module.

  

• This will take a moment and import the selected module.

Step 3: Add Credentials for Run as Account

We need to set up credentials that run the PowerShell script placed in the Azure Automation Runbook. So, the idea is our PowerShell script gets the stored credentials from the Azure automation account, Connects to the SharePoint Online tenant, generates the storage report for all sites, and then sends out an email every day.

• From the automation, account Click on the “Credentials” link in the quick launch.
• Click on the “Add a credential” button. Provide any valid credentials that have the necessary access. Here in my case, I’ve used an account with the SharePoint Online Admin role. Click on the “Create” button to create a credential. 
  

  

We can now use this credential in our PowerShell scripts!

Step 4: Create a Runbook with PowerShell Script 

As its name implies, Runbook is the container of the script we are going to run. Runbook lists down all the published Runbooks available for automation.

• Open the Azure Automation account created >> Click on “Runbooks” under process automation.
• Click on “Create a Runbook” >> Assign a name and select its type as PowerShell and click on the “Create” button.

  

Once the PowerShell Runbook is created, it takes you to the page to edit the PowerShell script to run. Write the necessary PowerShell scripts for the automation. In my case, I have written the below PowerShell script to generate a storage report for all sites in the tenant and sends out an email.

#Get Stored Credentials
$CrescentCred = Get-AutomationPSCredential -Name "StorageRptCred"

#Set Variables
$TenantAdminURL = "https://crescent-admin.sharepoint.com"
$EmailTo = "[email protected]"
 
#Connect to PnP Online
Connect-PnPOnline -Url $TenantAdminURL -Credentials $CrescentCred

$CSSStyle = "<style>
table {font-family: Arial, Helvetica, sans-serif; border-collapse: collapse; width: 100%;}
table td, th {border: 1px solid #ddd; padding: 8px;}
table tr:hover {background-color: #ddd;}
table th { padding-top: 12px; padding-bottom: 12px; text-align: left; background-color: #4CAF50; color: white;}
</style>"

#Get the Site collection Storage details
[string]$EmailBody = Get-PnPTenantSite | Select @{Label="Title";Expression={$_.Title}}, 
                                        @{Label="URL";Expression={$_.URL}}, 
                                        @{Label="Allocated (GB)";Expression={$_.StorageMaximumLevel/1MB}}, 
                                        @{Label="Used (MB)";Expression={$_.StorageUsage}}, 
                                        @{Label="Warning Level (GB)";Expression={$_.StorageWarningLevel/1MB}} | 
ConvertTo-Html -Title "Crescent Inc. Storage Report" -Head $CSSStyle -PreContent "Sites Storage Report"

#Send Email
Send-PnPMail -To $EmailTo -Subject "Storage Report" -Body $EmailBody

Save and test the PowerShell script by clicking on “Save” and click “test pane” buttons the code editor. Once fine with the script, click on “Publish” to publish the Runbook as  Runbooks must be published to create schedules.

Step 5: Add a Schedule to your PowerShell Script

As a final step, we’ve to add a schedule to the PowerShell script. Here is how to create a schedule for Azure Runbook.

• Click on “Schedules” from the Runbook created >> Click on the “Add a schedule” link. 
• Choose “Link a Schedule to your runbook” >>  Click on the “Create a new schedule” link. 
• Enter the necessary details for your schedule like name, description, start date, time, time zone, and the recurrence details. In my case, the schedule will run every day at the given time till the expiry date.

Alright, Now we have configured the PowerShell script in Azure automation. Wait for the scheduled job to run and here is my scheduled job emails in action: