Site icon Office 365 for IT Pros

Use a SharePoint List to Store Weekly Open Tasks Statistics

Tony Redmond
How to use a SharePoint Online list to store open task statistics.
Advertisements

Track Open Task Statistics and Closure Rate Over Time

After I wrote about how to send a weekly reminder email with details of open Planner tasks, a reader asked how best to store details of the open tasks so that they could analyze peoples’ progress in closing tasks over time. That seemed like a good idea, especially to help the noble authors of the Office 365 for IT Pros eBook, so I took up the challenge.

Use a SharePoint Online List

There are many ways to solve the problem but seeing that the script described in the original article uses the Microsoft Graph PowerShell SDK, the simplest approach is to use a list in a SharePoint Online site to store the data. This article describes the basics of interacting with lists.

The first task is to define the list. I decided upon a very simple list with the following fields:

After creating a blank list in a suitable site, I added some code to the script to detect if the site and list are available, and if so, set a flag to let the script know to update statistics for each user. The values of the site URI and list name will differ in your tenant.

# See if a site list is available to add details of each user to
$UpdateList = $false
$Uri = "https://office365itpros.sharepoint.com/sites/O365ExchPro"
$SiteId = $Uri.Split('//')[1].split("/")[0] + ":/sites/" + $Uri.Split('//')[1].split("/")[2]
$Site = Get-MgSite -SiteId $SiteId
$List = Get-MgSiteList -SiteId $Site.Id -Filter "displayName eq 'Planner Task Burndown'"   
If ($List) {
    $UpdateList = $true
}

The script sends email to users to tell them how many open tasks they have and what those tasks are. In this example, I am only interested in reporting open tasks for a specific plan, so I added this code to extract the open tasks for that plan and construct a request body (hash table) containing values for the list fields to update:

# Extract data that we want to report on 
[array]$PlanData = $UserTasks | Where-Object {$_.Plan -eq 'MAC Tasks'}
$NewItemParameters = @{
	fields = @{
	 Title                   = (New-Guid).Guid
        Plan                    = $PlanData[0].Plan
        UPN                     = $PlanData[0].UserEmail
        Username                = $PlanData[0].User
        Tasks                   = $PlanData.count
        RunDate                 = Get-Date -Format 'dd-MMM-yyyy HH:mm'
        Averagedaysoutstanding  = [math]::Round(($PlanData | Measure-Object -Property 'Days Outstanding' -Average).Average, 2)
    }
}

The names of the fields in the hash table must match the names used in the list. To see the names of the fields, run this command and use the values from the Name column rather than the DisplayName column:

$Fields = Get-MgSiteList -SiteId $Site.Id -ListId $List.Id -ExpandProperty Columns | Select-Object -ExpandProperty Columns | Select-Object DisplayName, Name

After populating the request body, the script can update the list by running the New-MgSiteListItem cmdlet passing the site identifier, list identifier, and request body:

Try {
    $NewItem = New-MgSiteListItem -SiteId $Site.Id -ListId $List.Id -BodyParameter $NewItemParameters -ErrorAction Stop
    Write-Output ("Created new list item with ID {0}" -f $NewItem.Id)
} Catch {
    Write-Error ("Failed to create new list item: {0}" -f $_.Exception.Message)
}

Viewing Open Task Statistics

Figure 1 shows the list data as displayed by the SharePoint Online browser client. I might not have the most open tasks, but I was disappointed with the average number of tasks my assigned tasks remain open. Obviously, I need to up my game and burn down some open tasks.

Figure 1: Viewing open task statistics stored in a SharePoint Online list

Where to Put the Code and Next Steps

Using a SharePoint Online list to store data generated by a PowerShell script is not unique to open Planner tasks. The same technique can be used in many other situations where you need somewhere to store information created by a script.

Not everyone will want to store open task statistics, so I haven’t updated the original script with the code described above. It’s easy to figure out where to add the code. The check for the site and list go at the start of the script after connecting to the Graph. The code to generate the request body and create the list item go into the section where an email is sent to a user. If you run the script as an Azure Automation runbook, remember to add the Microsoft.Graph.Sites module to the modules available to the runtime environment.

My next step is to add a field to capture burndown progress since the last week. All in the interest of keeping everyone aligned, of course!

Need help to write and manage PowerShell scripts for Microsoft 365, including Azure Automation runbooks? Get a copy of the Automating Microsoft 365 with PowerShell eBook, available standalone or as part of the Office 365 for IT Pros eBook bundle.

Exit mobile version