Manage Office 365 Distribution Groups via Excel spreadsheet or CSV

A consultant friend of mine posed an interesting question to me this week--one of his customers wanted to be able to let his users administer a cloud-managed Office 365 distribution group by uploading a CSV or Excel spreadsheet.  From an administration perspective, I have done an incredible amount of directory management tasks using CSVs, so this didn't seem like that difficult of a task.

Handing control of it over to a user, however--that seemed daunting.  Thus, began my first real foray into PowerShell forms apps.  Yes, I'm way behind, mainly because I haven't had a need to do this.  I picked up a copy of PowerShell Studio and got to work learning how to build forms apps.

Of course, as with most projects, as soon as I'm halfway through, I come up with more ideas, so I'm going to keep tinkering with this until I have something that I think is really cool.  In the meantime, I'd love to hear what you think about it and ideas for features or changes.

The basic idea of the tool is this:

  1. Launch the tool, and go to File | Connect to Office 365.

  2. Enter credentials when prompted.  After you have successfully entered a credential, it populates the Username: area with the identity of the logged-in credential.

  3. Click the Refresh Group List button to retrieve a list of groups.  It uses the "ManagedBy" property on distribution groups to determine what groups you have the ability to manage (since it's designed for use by end-users who don't have administrative privileges).

  4. Click File | Open File... and browse to either a CSV or XLS(x) file that has at least one column with the header EmailAddress.

  5. The Filename field has been populated and the Members area shows the number of lines in the file.

  6. Click a group name in the Groups that you can manage list, and then click Refresh Members button.
    The Current Group Members list is populated by running Get-DistributionGroupMember on the group selected in the Groups that you can manage column.  The Users to Remove and Users to Add are populated via hash tables--by converting both the input list file and the results of Get-DistributionGroupMember in hash tables, I can quickly perform a -notin both directions and export those lists to new arrays:

     # Build the lists of users
    # $ExistingGroupMembers will contain all of the members of the currently select group, represented by $SelectedItem
    # $NewGroupmembers will contain all of the users imported from the CSV/XLS file
    
    $ExistingGroupMembers = Get-DistributionGroupMember $SelectedItem
    $ExistingGroupMembersHash = @{ }
    $NewGroupMembers = Import-Csv $FileName
    $NewGroupMembersHash = @{ }
    
    # Build the ExistingGroupMembersHash table
    ForEach ($obj in $ExistingGroupMembers)
        {
            $ExistingGroupMembersHash[$obj.PrimarySmtpAddress] = $obj.PrimarySmtpAddress
        }
    
    # Build the NewGroupMembersHash table    
    ForEach ($obj in $NewGroupMembers)
        {
            $NewGroupMembersHash[$obj.EmailAddress] = $obj.EmailAddress
        }
    # Users to Remove
    [array]$UsersToRemove = $ExistingGroupMembersHash.Values | ? { $_ -notin $NewGroupMembersHash.Values }
        
    # Users to Add
    [array]$UsersToAdd = $NewGroupMembersHash.Values | ? { $_ -notin $ExistingGroupMembersHash.Values }
    
  7. The result is that $UsersToRemove has the list of users that were in Get-DistributionGroupMember but not in the import file $FileName, and $UsersToAdd has the users in $FileName that were not in the results of Get-DistributionGroupMember.

  8. Click the Update Group Membership button to run the Remove-DistributionGroupMember and Add-DistributionGroupMember operations on the group, adding or removing the appropriate names.

  9. Click File | Exit to log out of the PowerShell session and exit the application.

Ideas that I'm kicking around:

  • Exporting group membership (in case you need to go back to one)
  • Managing Office 365 Groups (Unified Groups)
  • Adding / removing users in the list boxes
  • Allowing for header-less files

I look forward to hearing your comments and ideas. :-)

You can download the tool at https://gallery.technet.microsoft.com/Office-365-Distribution-756ebab7.