Bulk group modification

We are in the process of redoing our sharepoint group permissions (yay) and part of that is

  1. Bulk group creation
  2. Removing users from groups
  3. Adding groups/users to groups

I have taken care of bulk group creation with this:

gc C:\Scripts\sp\sp.txt | ForEach-Object {New-adgroup -Groupscope Global -Name $_ -path "OU=SHAREPOINT,OU=blah,DC=blah,DC=blah,DC=blah,DC=blah"}

Where sp.txt just contains the group names

I have taken care of emptying the current groups with:

gc C:\scripts\sp\sp2.txt | % {Remove-ADGroupMember -Identity $_ -Members (Get-ADGroupMember $_ ) -confirm:$false}

Where sp2.txt contains the group names

Where I am a little fuzzy is how to handle the group modification. Currently Sharepoint team gives me a spreadsheet with two columns. One is the group to be modified, and the other is the groups/users that need to be added to that group. Currently they are putting all the users/groups to be added to the group in one cell. I’m not sure of a way to read all the users in the cell into the group.

I know I could format the data like below, and do a for each statement. But that would require me going into the spreadsheet and modifying the columns, I want to do this as automated as possible :slight_smile:


Any thoughts?

How are they separating the username in the cell ?

You could probably do something like this


Looks like this


That link I posted seems to work with your data format

Thanks, that it does. For some reason it outputs the csv with just a bunch of “F”'s. Not sure why, but I am going to dig into it and see what I can figure out.

This is how my test data came out

Group useradd
group1 user3
group2 user5
group3 user27
group4 user15
group5 user56
group6 user11
group6 user15
group6 user99

It worked, problem was not enough coffee :slight_smile:

Real problem:
The test spreadsheet I was using had multiple tabs, and the data I was looking to modify wasn’t the first tab. I created a new spreadsheet with only one tab and the data and it was fine.

Thank you for your help.

Glad it worked in the end coffee soles everything :slight_smile:

Just a further update if you change the line $ws = $wb.Sheets.Item(1) to the number or name of the tab you will not have to create a new spread sheet each time (assuming the data is always on the same tab wit the same name :slight_smile: the example below had 2 tabs with the second one called thisone

function ImportAndCrossJoin($path){
$xls = New-Object -ComObject Excel.Application
$xls.Visible = $false
$wb = $xls.Workbooks.Open($path)
$ws = $wb.Sheets.Item(“thisone”)

one more mod, I have changed the function so you can pass it the tab name

function ImportAndCrossJoin($path,$tab){
$xls = New-Object -ComObject Excel.Application
$xls.Visible = $false
$wb = $xls.Workbooks.Open($path)
$ws = $wb.Sheets.Item($tab)
$lastRow = ($ws.UsedRange.Rows).Count
$lastCol = ($ws.UsedRange.Columns).Count
foreach ($row in (2…$lastRow)){
$ht = [ordered]@{}
foreach ($col in (1…$lastCol)){
$heading = $ws.Cells.Item(1,$col).value2
#split the cell by crlf or comma
$parts = ($ws.Cells.Item($row,$col).value2 -split “,|`n”).Trim()
$ht."$heading" = @($parts)
CartesianProduct $ht

ImportAndCrossJoin C:\test\test.xlsx “tabname” | Export-CSV -NoTypeInformation -path C:\test\seperatedwithtabs.csv
ii C:\test\seperatedwithtabs.csv

Thank you Simon! You are a great resource here.

This can be done through PowerShell

$DNOfManager=dsquery user -o dn -name “Testing Tester”
$GroupList=dsquery group DC=ad,dc=example,dc=local -limit 600
Foreach ($group in $grouplist) {
set-adgroup -Identity $Group -ManagedBy $DNOfManager
add-adpermission -Identity $Group -user $DNOfManager -AccessRights ReadProperty, WriteProperty -Properties ‘Member’

For more information, visit here.