Manage Office 365 Distribution Groups via Excel spreadsheet or CSV

Manage Office 365 Distribution Groups via Excel spreadsheet or CSV

  •  
  •  
  •  
  •  
  •  
  •  
  •  
Rate this post

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.

Reader Comments

  1. For some reason stopped working for me: does not load Groups and does not generate any errors… Is there a way to get a log and see where it failed?

  2. Hi,

    This isn’t returning any groups that I manage (I’m a global admin) after connecting to O365. Is there any way to debug this or find out why it’s not returning any groups?

    The majority of my groups are synced using FIM but I do have some in-cloud that I manage.

    Matt

  3. It’s a great idea and something that’s being requested in my environment. The app however doesn’t seem to work with our federated accounts that authenticate against our CAS service. It does work with my user@company.onmicrosoft.com account though, which I have for testing but our users don’t have. Assuming it’s an authentication problem, any thoughts as to enabling non non-onmicrsoft.com accounts to use this?

  4. Hi Aaron,

    Thanks for the lovely utility you have built. When could we expect it to be compatible with Windows 2016 server OS? I am trying to use it on 2016 server but it doesn’t fetch the Groups after connecting to the O365.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.