Check external sharing on your Office 365

External sharing is an important subject inside of the organizations because all days, we requiring to share a document or multiple documents. However, if there when managing multiple sites inside SharePoint this task can be challenging since you can have different setting for each site. On this article, we’ll export to an Excel the External Sharing of your organization from the tenant.

To begin with we need to understand the external options that we’ve available:

• ExternalUserAndGuestSharing – Users can share files and folders using links that don’t require sign-in.
• ExternalUserSharingOnly – Guests must sign in or provide a verification code.
• ExistingExternalUserSharingOnly – Only guests already in your organization’s directory.
• Disabled – No external sharing allowed

The next PowerShell script will create an Excel that will store the information about your tenant External Sharing.

$loginUrl   = "https://contoso-admin.sharepoint.com" #SharePoint Admin Center
$username   = "contoso@sharepoint.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

$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 -Limit All
$createExcel = $excel.Workbooks.Add()
$excel.ActiveSheet.Name  = "SharePoint"
$workbooks = $excel.Worksheets.Item(1)
$workbooks.Cells.Item(1,1) = "Site URL"
$workbooks.Cells.Item(1,2) = "External Sharing"

$header = $workbooks.UsedRange
$header.Interior.ColorIndex = 19
$header.Font.ColorIndex = 11
$header.Font.Bold = $True

$tenant = Get-SPOTenant

$workbooks.Cells.Item(2, 1) = $loginUrl
$workbooks.Cells.Item(2, 2) = $tenant.SharingCapability.ToString()
$workbooks.Cells(2, 1).Interior.ColorIndex = 16
$workbooks.Cells(2, 2).Interior.ColorIndex = 16

$intRow = 3

foreach ($site in $sites){
 $external = $site.SharingCapability.ToString()
 $workbooks.Cells.Item($intRow, 1) = $site.Url
 $workbooks.Cells.Item($intRow, 2) = $site.SharingCapability.ToString()
  switch($external)
   {
    ExistingExternalUserSharingOnly{
	 $workbooks.Cells($intRow, 2).Interior.ColorIndex = 9
         $workbooks.Cells($intRow, 2).Font.ColorIndex = 2
	}
	ExternalUserSharingOnly{
	 $workbooks.Cells($intRow, 2).Interior.ColorIndex = 45
	}
	ExternalUserAndGuestSharing{
	 $workbooks.Cells($intRow, 2).Interior.ColorIndex = 44
	}
	default{
		continue
	}
   }
 $intRow = $intRow + 1
}
$workbooks.Columns.AutoFit()

Conclusion

After running this script, we’ll have an overview of the External Sharing on your SharePoint tenant in an Excel. If it’s required, you can then go on each site/tenant/one-drive and adjust that External Sharing.