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   = "" #SharePoint Admin Center
$username   = ""
$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()
	 $workbooks.Cells($intRow, 2).Interior.ColorIndex = 9
         $workbooks.Cells($intRow, 2).Font.ColorIndex = 2
	 $workbooks.Cells($intRow, 2).Interior.ColorIndex = 45
	 $workbooks.Cells($intRow, 2).Interior.ColorIndex = 44
 $intRow = $intRow + 1


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.


  1. bob said:

    Hi, I ran the script and received: WARNING: More results were found but were not returned. Use ‘-Limit ALL’ to return all possible results. Where do I append -limit all?

    April 29, 2019
    • David Ramalho said:

      Hi Bob,

      The “-Limit ALL” should be used when requesting the $sites Get-SPOSite. The default value is 200. I’ve updated the article with that adjustment. Thanks for the comment.

      David Ramalho

      April 29, 2019
  2. Michaël Van den Steen said:

    Thanks a lot for this!!

    September 23, 2019

Leave a Reply

Your email address will not be published. Required fields are marked *