How To Get The External Columns From SharePoint Online Lists Using PnP PowerShell

In this blog, you will see how to get the external columns by looping through all the lists from root site as well as sub sites using PnP PowerShell.

You can download setup files from the releases section of the PnP PowerShell repository.

Copy the below script and paste it in a notepad. Save the file as GetExternalColumns.ps1. 

  1. # Input Parameters  
  2. $siteURL="https://c986.sharepoint.com/sites/dev"  
  3.   
  4. # Loop through all the lists and check if the field is external column  
  5. Function GetExternalColumns($web)  
  6. {  
  7.     $listColl=Get-PnPList -Web $web  
  8.     foreach($list in $listColl)  
  9.     {                     
  10.         $fieldColl=Get-PnPField -List $list.Title -Web $web  
  11.         foreach($field in $fieldColl)  
  12.         {           
  13.             if($field.TypeAsString -eq "BusinessData")  
  14.             {                  
  15.                 write-host -ForegroundColor DarkYellow "List Name: " $list.Title " Field Name: " $field.Title  
  16.             }                                                    
  17.         }          
  18.     }      
  19. }  
  20.   
  21. # Get the root web and call the GetExternalColumns function  
  22. Function GetRootWeb()  
  23. {  
  24.     $rootWeb=Get-PnPWeb  
  25.     write-host -ForegroundColor Magenta "Getting information from the website - " $rootWeb.Title " - " $rootWeb.Url  
  26.     GetExternalColumns($rootWeb);  
  27. }  
  28.   
  29. # Get the all the sub webs and call the GetExternalColumns function  
  30. Function GetSubWebs()  
  31. {  
  32.     $webColl=Get-PnPSubWebs -Recurse  
  33.     foreach($web in $webColl)  
  34.     {      
  35.         write-host -ForegroundColor Magenta "Getting information from the website - " $web.Title " - " $web.Url  
  36.         GetExternalColumns($web);  
  37.     }  
  38. }  
  39.   
  40. Connect to SharePoint Online site  
  41. Connect-PnPOnline –Url $siteURL –Credentials (Get-Credential)  
  42.   
  43. # Call the functions  
  44. GetRootWeb  
  45. GetSubWebs  

Open PowerShell window and run the following command.

 

  1. >cd "<folderlocation>"  

folderlocation – GetExternalColumns.ps1 file location

Run the following command,

>.\GetExternalColumns.ps1

 

Thus in this blog, you saw how to get the external columns from SharePoint Online lists using PnP PowerShell.