Author Posts

October 5, 2017 at 12:22 pm

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 🙂


Any thoughts?

October 5, 2017 at 12:30 pm

How are they separating the username in the cell ?

October 5, 2017 at 1:12 pm

That link I posted seems to work with your data format

October 5, 2017 at 1:28 pm

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.

October 5, 2017 at 1:31 pm

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

October 5, 2017 at 2:47 pm

It worked, problem was not enough coffee 🙂

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.

October 5, 2017 at 3:12 pm

Glad it worked in the end coffee soles everything 🙂

October 5, 2017 at 3:44 pm

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 🙂 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")

October 5, 2017 at 4:04 pm

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

October 6, 2017 at 2:14 pm

Thank you Simon! You are a great resource here.

October 12, 2017 at 11:35 am

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.
Bulk modifications using Set-AdUser