In Powershell, how do i 'group' files per user?

Question

I am trying to send a mass email to SharePoint users who have checked out files. I have a CSV as my data source. I know this can be done using MailMerge; however, I don't want a user with multiple files checked out to receive multiple emails corresponding to each file. I just want one email for that user listing all his checked out files.

Using a loop, my idea is for every user in the csv (column name is 'Checked Out To'), I would put the files (column name is 'fileName') in an array. Before moving on the next user, I would send an email to the user listing all his checked out files.

What's the best way to implement this? Here's my current attempt (this works but it still sends multiple emails to one recipient):

$CSV = Import-Csv 'C:\Temp\ChecedkOutFiles.csv'

$myArray = @()


#Email
$SMTP = "smtp.ourdomain.com"
$From = "DoNotReply@ourdomain.com"
$Subject = "Checked Out Files"

foreach ($row in $CSV) {

    $master = $row | select 'Checked out to', fileName, 'Site address'
    $users = $row | select 'Checked out to'
    $files = $row | select fileName
    $address = $row | select 'Site address'        
    
    foreach($user in $users) {        
        
        $Username = $user.'Checked out To'


        if (@(Get-ADUser -Filter "Name -eq '$Username'").Count -eq 0) {
               Write-Warning -Message "User $Username not found."
               $Email = "DoNotReply@ourdomain.com"
        }

        Else {

            $UserPrincipalName = Get-ADUser -Filter "Name -eq '$username'"  | select UserPrincipalName
            $Email = $UserPrincipalName.UserPrincipalName
        }
                      
                             foreach($file in $files) {                                        
                             
                             #do {
                      
             
                                    if (($user.'Checked out To') -eq $master.'Checked out To') {
                      
                                        #$myArray += $file.Name | where {($master.'Checked out To') -eq $user.'Checked out To'}                        
                                        $myArray += $file.Name
                                   }

                              #}
                              
                              # while ($Username -eq  $master.'Checked out To')

                                    try {
                    
                                        Send-MailMessage -From $From -Subject $Subject -To $Email `
                                        -body `
                                        "We have identified the following files that are currently checked out to you:
                                        $files" -BodyAsHtml `
                                        -SmtpServer $smtp             
                                    }

                                    catch {
                
                                        $Error        
                                        
                                    }

                             }                      
    

     $myArray.clear()     
      
    }
     

Here's how the CSV looks like:

Answer 1

Fortunately, PowerShell has a builtin Group-Object cmdlet for just this kind of thing!

$filesPerUser = $CSV | Group-Object -Property 'Checked out To'

$filesPerUser will now be an array of objects each with:

• A Name property - the unique username found in the Checked out To column on which we grouped
• A Count property - the number of records with that username
• A Group property - an array containing all the records in question

Now it becomes trivial to process files for one user at a time:

foreach($userFileList in $filesPerUser){
  $username = $userFileList.Name
  $files = $userFileList.Group |Select filename

  # send mail message to $username with list of $files
}

---

Group-Object also has an -AsHashtable parameter if you prefer the results as a dictionary type - each key will be the property value grouped on (the Name of each group), and the value will be the array of associated records.

This might be useful if you need to ask questions like "how many files are checked out specifically to [UserX]":

$targetUser = "user15433"
$filesPerUser = $CSV | Group-Object -Property 'Checked out To' -AsHashTable

# now we can efficiently discover rows associated with a particular user
$checkoutCount = $filesPerUser[$targetUser].Count