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 to 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 "Create a resource" button >>  Search and find "Automation" >> Click on "Add" to create 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 runs the PowerShell script placed in Azure automation Runbook. So, the idea is our PowerShell script gets the stored credentials from Azure automation account, Connects to SharePoint Online tenant, generates the storage report for all sites and then sends out an email every day.

• From the automation account Click on "Credentials" link in the quick launch.
  
• Click on "Add a credential" button. Provide any valid credentials that has necessary access. Here in my case, I've used an account with SharePoint Online Admin role. Click on "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 the 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 "Create" button.
  

Once the PowerShell Runbook is created, it takes you to the page where you can 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 storage report for all sites in the tenant and sends out email.

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

#Set Variables
$TenantAdminURL = "https://crescent-admin.sharepoint.com"
$EmailTo = "Salaudee[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 schedule for Azure Runbook.

• Click on "Schedules" from the Runbook created >> Click on "Add a schedule" link. 
• Choose "Link a Schedule to your runbook" >>  Click on "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: