Manage your SharePoint Team Sites

On SharePoint Team Sites can be hard to do understand who and what are the permission of certain users. If they belong to an Office 365 group they will have the permissions of that group. They can also be added manually to the site and they will have the permissions set when added. This structure can leave users without the correct permissions on the SharePoint sites.

It’s important to understand the difference between Office 365 Groups and SharePoint team sites, below is a section of references where you can get more deep on this question.

Basically, when creating an Office 365 group, you will have multiple channels of collaboration including the SharePoint Team Site, Microsoft Teams Team, and more tools.
If you create a SharePoint Team Site, you will just have that collaboration site without Microsoft Teams and all other tools.

With the PowerShell of Microsoft Teams and SharePoint Online, we were able to get multiple information about the organizational structure. Below what we gonna extract.

Microsoft Teams Teams/Channels on Tenant
Permissions of the SharePoint Team Sites on Tenant
SharePoint Team Sites URL

$loginUrl   = "https://CONTOSO-admin.sharepoint.com" #SharePoint Admin Center
$username   = "CONTOSO@CONTOSO.onmicrosoft.com"
$password   = "password"

$encpassword = convertto-securestring -String $password -AsPlainText -Force
$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $username, $encpassword

Connect-SPOService -Url $loginUrl -Credential $cred
Connect-MicrosoftTeams -Credential $cred

$excel = New-Object -comobject Excel.Application
$excel.Visible = $True 
Add-Type -AssemblyName Microsoft.Office.Interop.Excel
$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault

$sites = Get-SPOSite
$createExcel = $excel.Workbooks.Add()
$excel.ActiveSheet.Name  = "SharePoint"
$workbooks = $excel.Worksheets.Item(1)
$workbooks.Cells.Item(1,1) = "Admin"
$workbooks.Cells.Item(1,2) = "Tenant URL"
$workbooks.Cells.Item(1,3) = "Microsoft Teams groups on Tenant"

$teams = Get-Team
$workbooks.Cells.Item(2,1) = $username
$workbooks.Cells.Item(2,2) = $loginUrl

$intRow = 2
foreach($team in $teams){
 $workbooks.Cells.Item($intRow , 3) = $team.DisplayName
 $intRow += 1
}

foreach ($site in $sites){
 $admins = @()
 $members = @()
 $visitors = @()
 $groups = ""
 
 if($site.Template -match "GROUP#0|STS#3|STS#0"){
  
  $workbooks = $excel.Sheets.Add()
  $excel.ActiveSheet.Name = $site.Title
  
  $workbooks.Cells.Item(1,1) = "Site Name"
  $workbooks.Cells.Item(1,2) = "Site URL"
  $workbooks.Cells.Item(1,3) = "Site Admins"
  $workbooks.Cells.Item(1,4) = "Site Members"
  $workbooks.Cells.Item(1,5) = "Site Visitors"
  
  $Cell = $workbooks.UsedRange
  $Cell.Interior.ColorIndex = 19
  $Cell.Font.ColorIndex = 11
  $Cell.Font.Bold = $True
  
  try{
   $groups = Get-SPOSiteGroup -Site $site.Url
  }
  catch{
   $admins = "No Permissions to Access Groups on site."
   $members = "No Permissions to Access Groups on site."
   $visitors = "No Permissions to Access Groups on site."
  }
  foreach($group in $groups){	
   if($group.LoginName -match "Owners"){
    foreach($user in $group.Users){
     if($user -match "@"){
      $admins += $user 	
     }    
     if($user -match "_o"){
      $groupid = $user -split "_o"
      $admins += Get-TeamUser -GroupId $groupid[0] -Role Owner | Select-Object User -ExpandProperty User
      $members += Get-TeamUser -GroupId $groupid[0] -Role Member | Select-Object User -ExpandProperty User
     }
     if($admins -match "spo-grid-all-users"){
      $admins += "Everyone except external users"    
     } 
    }
   }
   if($group.LoginName -match "Members"){
    foreach($user in $group.Users){
     if($user -match "@"){
      $members += $user 
     }
     if($user -match "spo-grid-all-users"){
      $members += "Everyone except external users"    
     }
    }
   }
   if($group.LoginName -match "Visitors"){
    foreach($user in $group.Users){
     if($user -match "@"){
      $visitors += $user 
     }
     if($user -match "spo-grid-all-users"){
      $visitors += "Everyone except external users"    
     }
     }
    }
  }
  
  $intRow = 2
  $Cell.Cells.Item($intRow, 1) = $site.Title
  $Cell.Cells.Item($intRow, 2) = $site.Url
  foreach($admin in $admins){
   $Cell.Cells.Item($intRow , 3) = $admin
   $intRow += 1
  }
  $intRow = 2
  foreach($member in $members){
   $Cell.Cells.Item($intRow , 4) = $member
   $intRow += 1
  }
  $intRow = 2
  foreach($visitor in $visitors){
   $Cell.Cells.Item($intRow , 5) = $visitor
   $intRow += 1
  }
 }
}

Conclusion

With this PowerShell, we can understand more about our SharePoint team sites and how they are distributed on the organization. The extracted Excel will have per worksheet a SharePoint Team Site name and the permissions. You could also check and combine this script with my last article about Extracting Communication Sites to Excel to have a better understanding of your organization.

References

If your working with Office 365 group and how they can be used on your organization, I would recommend you to have a look at the next articles:

Manage your SharePoint Team Sites

Conclusion

References

David Ramalho

One Comment