Creating a Week-by-Week Report for Open Planner Tasks

Creating an Overview of Task Closure Over Time

Earlier this year, I discussed how to create a weekly incomplete task report for a project (in our case, the 2027 edition of the Microsoft 365 for IT Pros eBook). I followed up with another article describing how to use a SharePoint Online list to hold weekly statistics for open tasks. In our case, the script runs as an Azure Automation scheduled job so that authors receive email with details of their open tasks every Saturday.

The project coordinators can check the SharePoint list to discover how many open tasks remain for each author, but it would be nicer to give them a concise report showing progress in closing tasks over a period. This article explains how to do the job with PowerShell.

Computing a Count of Open Tasks for Every Saturday

I decided that the lookback period would span the last six weeks and that the report would include a column for each week showing the open tasks at that time plus another column showing the average number of tasks open over the six-week period. This resulted in the following flow for the script.

The first thing to do is to read the task data from the SharePoint list called “Planner Task Burndown” using the Get-MgSiteListItem cmdlet. The code to read the data is covered in previous articles and can be seen in the script (see GitHub link below). The information is stored in an array called $ItemData.

Because the statistics are computed by a scheduled job every Saturday, we need to base the weekly segments on Saturday. This is done by computing the last Saturday (the last time the job ran) and then creating details of the six weeks we’re going to report on. The information is stored in an array. We also compute the start and end dates for the reporting period.

$Today = (Get-Date).Date
$LastSaturday = $Today.AddDays(-(($Today.DayOfWeek - [System.DayOfWeek]::Saturday + 7) % 7))
# Create the six weeks that we’re going to report for and store the data in an array
$Weeks = 5..0 | ForEach-Object {
    $WeekStart = $LastSaturday.AddDays(-7 * $_)

    [PSCustomObject]@{
        WeekNumber = 6 - $_
        ColumnName = $WeekStart.ToString("dd-MMM-yyyy")
        WeekStart  = $WeekStart
        WeekEnd    = $WeekStart.AddDays(7)
    }
}

# Calculate the date window to select items for the six week period: from oldest Saturday to end of latest Saturday-based week
$Weeks = $Weeks | Sort-Object WeekNumber
$StartDate = ($Weeks | Select-Object -First 1).WeekStart
$EndDate = ($Weeks | Select-Object -Last 1).WeekEnd

Creating the Report of Open Tasks

Next, find the list of users to report on. This is done by extracting the unique set of user principal names from the data in the SharePoint Online list.

# Find set of unique users who have had outstanding tasks
$UniqueUsersWithTasks = $ItemData | Sort-Object UPN -Unique | Select-Object UPN, UserName

Now the script extracts the set of data spanning the six-week reporting period from the SharePoint list.

# Find set of items logged for the last six weeks
[array]$SixWeeksTasks = $ItemData | Where-Object {[datetime]$_.RunDate -ge $StartDate -and [datetime]$_.RunDate -lt $EndDate}

The next step is to create a hash table with the user principal name as the key and the value being an array of tasks for users. The idea is that instead of constantly fetching data from the array of tasks, the script makes one pass per user and uses the results stored in the hash table afterwards. It’s a form of pre-indexing that’s intended to allow the script to scale.

# Create a hash table of tasks for each user. Without the hash table, we would scan
# $SixWeeksTasks six times per user or 600 times for 100 users. This way we do the
# scan once and use lookups against the hash table thereafter
$TasksByUPN = @{}
Foreach ($Task in $SixWeeksTasks) {
  If (-not $TasksByUPN.ContainsKey($Task.UPN)) {
     $TasksByUPN[$Task.UPN] = [System.Collections.Generic.List[Object]]::new()
  }
     $TasksByUPN[$Task.UPN].Add($Task)
}

Finally, we create a PowerShell list and populate a record (row) for each user comprised of the user principal name, a column containing the count of tasks for each week, and a column with the average over the six weeks.

$TaskWeeklySummary = [System.Collections.Generic.List[Object]]::new()

Foreach ($User in $UniqueUsersWithTasks) {
    # Pre-define all columns in the desired order
    $Row = [ordered]@{
        UserPrincipalName = $User.UPN
        DisplayName = $User.UserName
    }
    Foreach ($Week in $Weeks) {
        $Row[$Week.ColumnName] = 0
    }
    $Row["Avg. Tasks Per Week"] = 0

    # Fetch tasks for the user for the last six weeks from the hash table
    $UPN = $User.UPN
    $UserTasks = If ($TasksByUPN.ContainsKey($UPN)) {
        $TasksByUPN[$UPN]
    } Else {
        @()
    }

    [int]$TotalTasks = 0
    Foreach ($Week in $Weeks) {
      $WeekRecord = $UserTasks | Where-Object {$_.UPN -eq $UPN -and [datetime]$_.RunDate -ge [datetime]$Week.WeekStart -and [datetime]$_.RunDate -lt [datetime]$Week.WeekEnd } | Sort-Object { [datetime]$_.RunDate } -Descending | Select-Object -First 1

      If ($WeekRecord) {
          $TaskCount = [int]$WeekRecord.Tasks
          $Row[$Week.ColumnName] = $TaskCount
          $TotalTasks += $TaskCount
      }
    }

    $Row["Avg. Tasks Per Week"] = [math]::Round(($TotalTasks / 6), 2)
    $TaskWeeklySummary.Add([PSCustomObject]$Row)
}

# Select output columns in the preferred order
$OutputColumns = @("DisplayName") + ($Weeks | Select-Object -ExpandProperty ColumnName) + "Avg. Tasks Per Week"

Figure 1 shows what the information in the PowerShell list looks like when displayed on screen.

How to Get the Script

You can download the full script from the Microsoft 365 for IT Pros GitHub repository. Remember, this is code that works for me. It might need some changes to work for you. Don’t be shy and suggest changes through GitHub pull requests.

---

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 Microsoft 365 for IT Pros eBook bundle.