Add Members to Group – a different approach

‘Sup PSHomies,

My Project Manager is slowly becoming a  true PowerShell believer! Of course he doesn’t have to do the actual scripting, that’s where I come in… 😉

So in walks the PM…

PM: “Say Urv, if I gave you an excel worksheet with the user/group relationship, think you’d be able to script it?”

Me:”Gee, this is so sudden, let me think about it and get back to you asap… (Grinning)”

Here’s an impression of the worksheet:


I’ve done this in past by just going through each user adding each group. I remember reading a post by Mike F. Robbins on how this could be done more efficiently! I very much like this approach. I’ve added a lil’ extra to the mix. Let’s dig in!

Well for starters I’ll just get the source directly from the excel file using D. Finke’s ImportExcel Module. No need to convert to CSV first.

$xlsxADGroupMembers = Import-Excel .\source\xlsx\$xlsxFile -WorkSheetname $WorkSheet

The other thing was retrieving the GroupNames from PSCustomObject by selecting MemberType ‘NoteProperty’, I got this as a tip on my own blog by Dirk. This way you’re not depending on the position of where the group names start in the header.

#Select Group names from Object
$Header = $xlsxADGroupMembers |
   Get-Member -MemberType NoteProperty |
   Where-Object{$_.Name -ne  'UserID'} |
   Select-Object -ExpandProperty Name

This makes it just a bit resilient.

I also decided to use hashtable to store the results first before processing the group membership. This way I can also export the results for future reference (Always keep a log)

#Create empty hashtables
$addADGroupMembers = @{}
$delADGroupMembers = @{}

#Get Group membership
$Header |
   $Group = $_
   $addADGroupMembers.$Group  = $xlsxADGroupMembers.Where{$_.$Group -eq '1'} | Select-Object -ExpandProperty 'UserID'
   $delADGroupMembers.$Group = $xlsxADGroupMembers.Where{$_.$Group -ne '1'} | Select-Object -ExpandProperty 'UserID'

Now it’s time to add the members to specified groups

$Header |
         Add-ADGroupMember -Identity $_ -Members $addADGroupMembers.$_
      catch [Microsoft.ActiveDirectory.Management.ADIdentityNotFoundException]{
         Write-Warning "AD Object $($Error[0].CategoryInfo.TargetName) not found"

         Remove-ADGroupMember -Identity $_ -Members $delADGroupMembers.$_ -Confirm:$false
      catch [Microsoft.ActiveDirectory.Management.ADIdentityNotFoundException]{
         Write-Warning "AD Object $($Error[0].CategoryInfo.TargetName) not found"

I’m using Try/Catch to catch any errors on AD Objects not existing. Without it you’d get quite a few errors if the AD objects don’t exists.

Here’s the full script:

I like the fact that you can use the list to add, but also delete if it isn’t necessary. Of course if you fill it in wrong, then there is no fixing that. So be warned!

Another thing is that members can be added manually or another process, so don’t be surprised, when you’re evaulating the group membership….

Tip: Save the  GroupMembership just to be sure…

#Get current Group memberships
$SnapshotADGroupMembers = @{}

$Header |
   $SnapshotADGroupMembers.$($_) = Get-ADGroupMember -Identity $_ | Select-Object -ExpandProperty SamAccountName

Next time I’ll blog about how we could to this in reverse… Stay tuned!

Hope it’s worth something to you,



5 thoughts on “Add Members to Group – a different approach

  1. Arie H.

    Things to improve:
    1. I would use 1 for Add but 0 for not, not an empty cell.
    2. Because group membership is binary, either true or false I would add an if statement before line 35 to check the value and then either add or remove accordingly, just means one less line of code is executed per For-Each cycle.
    3. Since most of the people will actually be in groups then out of groups (youll rarely see a group with one person in it, in a domain with 1000 users), you might reverse the order of checking to see if its not a member or is a 0 value, else its adding the member, if that makes sense 🙂
    4. Think of pre-populating the user names in the excel sheet so no one will enter their name and have a spelling mistake, and it will then be able to show the full name, which is nicer to view then abbreviated usernames that might look funny because naming schema.

    Keep up the good work,

    Arie H.

    Liked by 1 person

    1. Irwin Strachan Post author

      Thanks for the ideas! I agree with nr.1 . Nr2 is about validating the ADGroup. If it isn’t found then it won’t be processed later down… Good one. The try/catch will also resolve any identities missing. I deleted a group and ran the script and got a warning twice… Nr3 is a matter of perspective… I like the idea of nr4, I just don’t know how I’d go about this… Yet! 😉 I’ll definitely look into them…



  2. Dirk

    Hi Irwin,
    interesting. Thanks a lot for sharing. As usual, there are there’s more than one way to skin the cat. Here is another one using Group-Object (hopefully the code doesn’t get messed up too much):
    $groupNames = ($xlsxADGroupMembers | Get-Member -MemberType NoteProperty | where Name -ne UserID).Name
    foreach ($groupName in $groupNames){
    $xlsxADGroupMembers | Group-Object {$_.$groupName} | foreach {
    if ($_.Name -eq 1){
    Add-ADGroupMember -Identity $groupName -Members $
    Remove-ADGroupMember -Identity $groupName -Members $ -Confirm:$false

    Liked by 1 person

  3. Pingback: AD Security Group matrix | pshirwin

  4. Pingback: Verify GroupMembership with Pester | pshirwin

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s