Bulk group modification

PowerShell Help
1

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:

group,user
group1,user1
group1,user2
group1,user3

Any thoughts?

2

How are they separating the username in the cell ?

3

You could probably do something like this

4

Looks like this

https://i.imgur.com/CVyo93k.png

5

That link I posted seems to work with your data format

6

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.

7

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
group6

8

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.

9

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

10

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”)

11

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
}
$wb.Close()
$xls.Quit()
}

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

12

Thank you Simon! You are a great resource here.

13

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.