Матрица ролей из AD при помощи PowerShell

#Define class for work xls, xlsx 
Add-Type -AssemblyName Microsoft.Office.Interop.Excel
function CheckUser ([ref]$GroupWithUser,[ref]$AllUsersInAD,[ref]$UserCol)
{
    <#
    .SYNOPSIS
        Check user-member in group 
    .DESCRIPTION
        This function check user-member of the transfered group, if user is not in the group - he wil be add in the group at the end of the list.
        After that, next to each user of the transferes group is put "x", if group includes another group containing the same user-then "x" will be added to the previos "x"
    .EXAMPLE 
        CheckUser ([ref]$Group)([ref]$AllUsers)([ref]$Col), where $Col is the column that transfered group
    #>
    #Assign the transfered data to the function variable
    $AU=$AllUsersInAD.Value
    #Get all member from the passed Group , the class of which is "user"
    Get-ADGroupMember $GroupWithUser.Value | where objectClass -eq "user" | foreach{
        #Check the received user is a member of the OU our Company
        if ($AU.sAMAccountName -notcontains $_.sAMAccountName)
        {
            #Get info about new user for add in list AllUsers
            Get-ADUser $_ -Properties DisplayName | foreach{
                #Add a new field to user (ID)
                $_ | Add-Member -MemberType NoteProperty -Name ID -Value "0" -Force 
                #Set value to new field
                $_.ID=$AU.Count 
                #Add an entry to the list of AllUsers
                $AU +=  $_
                #Write the value of user.DisplayName in the first column
                $GroupMap.Cells.Item($_.ID+10,1)=$_.DisplayName
                #Write the value of user.SamAccountName in the second column
                $GroupMap.Cells.Item($_.ID+10,2)=$_.SamAccountName
                #Check the Enabled state of the user, if the user is turned off the cell is painted in yellow
                $GroupMap.Cells.Item($_.ID+10,1).Interior.ColorIndex = 46
            }
        }
        #Get entry from AllUsers list
        $UserRow=$AU | where sAMAccountName -eq $_.sAMAccountName
        #Set "x" to the user who is a member of transfered group
        $GroupMap.Cells.Item($UserRow.ID+10, $UserCol).Value() += "x"
        #Set the cell color to green, where value is "x"
        $GroupMap.Cells.Item($UserRow.ID+10, $UserCol).Interior.ColorIndex = 43
    }
    #Re-write AllUsers list (without this don`t work)
    $AllUsersInAD.Value = $AU
}

function Expand-ADGroup ([ref]$GroupExpand, [ref]$ColExpand, [ref]$RowExpand, [ref]$ColIncrement, [ref]$AllUsersInFunc)
{
    <#
    .SYNOPSIS
    This function is used to expand the group 
    .DESCRIPTION
    This recursive function is used to retrive the nested groups from the incoming group and introducing them to roles map
    .EXAMPLE 
    Expand-ADGroup ([ref]$Group)([ref]$Col)([ref]$Row)([ref]$ColFirst)([ref]$AllUsers)
    #>
    #Assign the transfered data to the function variable
    $RowInFunc=$RowExpand.Value
    $ColInFunc=$ColExpand.Value 
    $ColIncrementInFunc=$ColIncrement.Value
    #Get all member from the passed Group , the class of which is "group"
    Get-ADGroupMember -Identity $GroupExpand.Value | where objectClass -eq "group" | Get-ADGroup -Properties Name,Description,sAMAccountName | foreach {
        #Go to the next line
        $RowInFunc++
        #Check the cell, f it is not empty, then move the column one position
        if ($GroupMap.Cells.Item($RowInFunc, $ColInFunc).Value() -ne $null) {$ColInFunc++}
        #Write the group.SamAccountName Value in the cell
        $GroupMap.Cells.Item($RowInFunc, $ColInFunc).Value() = $_.sAMAccountName
        #Check the description of the group, if the description is not empty then thr description is written as a comment to the cell
        if ($_.description -ne $null) {[void]$GroupMap.Cells.Item($RowInFunc, $ColInFunc).AddComment($_.description)}
        #Otherwise cell painted in red
        else {$GroupMap.Cells.Item($RowInFunc, $ColInFunc).Interior.ColorIndex = 3}
        #Assign the transfered data to the function variable
        $AllUsersInFuncToFunc=$AllUsersInFunc.Value
        #Pass the group to the function "CheckUser" to check whethet it has member-user
        CheckUser ([ref]$_)([ref]$AllUsersInFuncToFunc)([ref]$ColInFunc)
        #If the group has an object where the class is equal to "group", then send the group to the "ExpandGroup" function
        if ((Get-ADGroupMember $_.sAMAccountName | where objectClass -eq "group") -ne $null)
        {
            #Save the column value to be checked after returning from the function and, if necessary, merge the cells of the same group
            $ColFirst=$ColInFunc
            #Send the group to the "ExpandGroup" function
            Expand-ADGroup ([ref]$_.sAMAccountName)([ref]$ColInFunc)([ref]$RowInFunc)([ref]$ColIncrementInFunc)([ref]$AllUsersInFuncToFunc)
            #If the group has more than 2 nested groups, merge the cells in which the groups
            if ($ColFirst -ne $ColInFunc)
            {
                $MergeCells = $GroupMap.Range(($GroupMap.Cells.Item($RowInFunc,$ColFirst)),($GroupMap.Cells.Item($RowInFunc,$ColInFunc-1)))
                $MergeCells.Select()
                $MergeCells.Merge()
            }
        }
        #Re-write AllUsers list (without this don`t work)
        $AllUsersInFunc.Value=$AllUsersInFuncToFunc
        #Go to the cell above
        $RowInFunc--
    }
    #If previous cell is busy then change the number of the passed column up 1 (re-write [ref])
    if ($GroupMap.Cells.Item($RowInFunc+1, $ColInFunc).Value() -ne $null){$ColExpand.Value=$ColInFunc+1}
    #Otherwise re-write the nuber of the passed column
    else {$ColExpand.Value=$ColInFunc}
    #Rwrite the number of the passed row
    $RowExpand.Value=$RowInFunc
} 

#Get the curent date value for save file
$Cur_date = Get-Date -Format d
#Run a script that collects data about AD groups that not in other groups (PrimaryGroups), and sends result to a file
$SecondaryMembers=@()
$PrimaryMembers=@()
$AllGroups = Get-ADGroup -Filter * -SearchBase "OU=Company,dc=example,dc=org"  
$AllGroups | foreach {
Get-ADGroupMember $_.sAMAccountName | where objectClass -eq "group" | foreach {
        if ($SecondaryMembers -notcontains $_.sAMAccountName)
        {
            $SecondaryMembers+=$_.sAMAccountName 
        }
    }
}
$AllGroups | foreach {
    if ($SecondaryMembers -notcontains $_.sAMAccountName)
    {
        $PrimaryMembers+=$_.sAMAccountName
    }
}
#Run Excel
$Excel = New-Object -ComObject Excel.Application
#Set Excel parameter visible is true
$Excel.Visible = $true
#Disable error log
$Excel.DisplayAlerts = $false
#Create a new book
$WorkBook = $Excel.Workbooks.Add()
#Assign sheets to variables and rename it 
$GroupMap = $WorkBook.Worksheets.Item(1) 
$GroupMap.Name = "UserGroup"
$GroupMember = $WorkBook.Worksheets.Item(2)
$GroupMember.Name = "GroupUser"
#This sheet not used yet, so we don`t rename it
$Empty = $WorkBook.Worksheets.Item(3)
#Assign a starting cell of 10 because we don`t know the depth of the groups attachments
$Row=10
#Get All users list, which are included in the OU our company
$AllUsers = Get-ADUser -Filter * -SearchBase "OU=Company,dc=example,dc=org" -Properties DisplayName | Sort-Object DisplayName
#Add new field "ID" to all objects
$AllUsers | Add-Member -MemberType NoteProperty -Name ID -Value "0" -Force
#Add a new parameter in entry of list AllUsers, which stands for identifier
$AllUsers | foreach {
    #Set ID value
    $_.ID=$Row-10
    #Write the value of user.DisplayName in the first column
    $GroupMap.Cells.Item($Row,1)=$_.DisplayName
    #Write the value of user.SamAccountName in the second column
    $GroupMap.Cells.Item($Row,2)=$_.SamAccountName
    #Check the Enabled state of the user, if the user is turned off the cell is painted in yellow
    if ($_.Enabled -ne $true) {$GroupMap.Cells.Item($Row,1).Interior.ColorIndex = 27}
    #Go to the next line
    $Row++
}
#Setting the initial column value for groups deployment
$Col = 3
#Seting the initial row value for groups deployment
$Row = 1
#Send to conveyor the values of the top-level groups
$PrimaryMembers | foreach {
    #Check the current cel, if it is nit empty, go to the next column position
    if ($GroupMap.Cells.Item($Row, $Col).Value() -ne $null){$Col++}
    #Write the group.SamAccountName Value in the cell
    $GroupMap.Cells.Item($Row, $Col).Value() = $_
    #Get group properties for checking "description" field
    $CheckDesc = Get-ADGroup $_ -Properties Description
    #Check the description of the group, if the description is not empty then thr description is written as a comment to the cell
    if ($CheckDesc.description -ne $null) {[void]$GroupMap.Cells.Item($Row,$Col).AddComment($CheckDesc.Description)}
    #Otherwise cell painted in red
    else {$GroupMap.Cells.Item($Row,$Col).Interior.ColorIndex = 3}
    #Keep the value of the first cell of group for possible merging if the nested groups are greater than 1
    $ColFirst=$Col
    #Pass the group to the function "CheckUser" to check whethet it has member-user
    CheckUser ([ref]$_)([ref]$AllUsers)([ref]$Col)
    #Send the group to the "ExpandGroup" function
    Expand-ADGroup ([ref]$_)([ref]$Col)([ref]$Row)([ref]$ColFirst)([ref]$AllUsers)
    #If the group has more than 2 nested groups, merge the cells in which the groups
    if ($ColFirst -ne $Col)
    {
        $MergeCells = $GroupMap.Range(($GroupMap.Cells.Item($Row,$ColFirst)),($GroupMap.Cells.Item($Row,$Col-1)))
        $MergeCells.Select()
        $MergeCells.Merge()
    }
}
#Increase the size of the cells to the maximum value in the column in first sheets
$GroupMap.usedRange.EntireColumn.AutoFit()| Out-Null
#Align cells to center
$GroupMap.Cells.HorizontalAlignment=-4108
#Set the file format to save the result
$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault	
#Save result to file
$Excel.ActiveWorkbook.SaveAs("C:TempADGroupMap_$Cur_date.xls", $xlFixedFormat)
#Clear resourse worksheet
$GroupMap = $null
$GroupMember = $null
$Empty = $null
#Close Excel project
$WorkBook.Close()
#Close procces
$Excel.Quit()
#Clear resourse procces
$Excel=$null
#Reclaim all memory that is inaccessible
[GC]::Collect()
[GC]::WaitForPendingFinalizers()