Freigeben über

Manage Office 365 Distribution Groups via Excel spreadsheet or CSV

  • Artikel

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.

Comments

  • Anonymous
    November 01, 2017
    Is there any way to export Office 365 Distribution Groups in vCard files without need of third party utility. I appreciate for all helps.
  • Anonymous
    November 01, 2017
    I would start here by looking at Dmitry’s quite excellent blog on creating VCF’s.https://dmitrysotnikov.wordpress.com/2007/11/07/out-vcard-exporting-outlook-address-book/
  • Anonymous
    December 12, 2017
    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.
  • Anonymous
    February 09, 2018
    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?
  • Anonymous
    February 27, 2018
    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
  • Anonymous
    June 25, 2018
    Thank you for your utility but it's not working with me because I'm using MFA.
    • Anonymous
      July 08, 2018
      You can try using an app password in place of your normal password.
  • Anonymous
    August 27, 2018
    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?