Bulk group modification

This topic contains 12 replies, has 3 voices, and was last updated by  edwin arlington 7 months, 2 weeks ago.

  • Author
  • #81299


    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?

  • #81302

    Simon B

    How are they separating the username in the cell ?

  • #81305

    Simon B
  • #81310

  • #81314

    Simon B

    That link I posted seems to work with your data format

    • #81317


      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.

  • #81320

    Simon B

    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

    • #81368


      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.

  • #81376

    Simon B

    Glad it worked in the end coffee soles everything 🙂

  • #81385

    Simon B

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

  • #81386

    Simon B

    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

  • #81475


    Thank you Simon! You are a great resource here.

  • #81899

    edwin arlington

    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.

You must be logged in to reply to this topic.