SharePoint Diary

Sync File Share to SharePoint Online using PowerShell

2020-07-18T18:22:00.001+05:30

Requirement: Sync Fileshare to SharePoint Online using PowerShell.

PowerShell Script to Sync File share to SharePoint Online document Library:

While my other script Migrate File Share to SharePoint Online using PowerShell imports files and folders from network file share to SharePoint Online, this script syncs file share to SharePoint Online document library.

Import-Module SharePointPnPPowerShellOnline

#Function to Import Files from Fileshare to SharePoint Online
Function Import-FileShareToSPO
{
 param
    (
        [Parameter(Mandatory=$true)] [string] $SiteURL,
        [Parameter(Mandatory=$true)] [string] $SourceFolderPath,
        [Parameter(Mandatory=$true)] [string] $TargetLibraryName,            
        [Parameter(Mandatory=$true)] [string] $LogFile
    )

    Try {
        Add-content $Logfile -value "`n---------------------- Import FileShare Script Started: $(Get-date -format 'dd/MM/yyy hh:mm:ss tt')-------------------"   
    
        #Get Number of Source Items from the Source Folder
        $SourceItemsCount =  (Get-ChildItem -Path $SourceFolderPath -Recurse).count

        #Get the Target Library to Upload
        $Web = Get-PnPWeb
        $Library = Get-PnPList $TargetLibraryName -Includes RootFolder
        $TargetFolder = $Library.RootFolder

        #Get the site relative path of the target Folder
        If($web.ServerRelativeURL -eq "/")
        {
            $TargetFolderSiteRelativeURL = $TargetFolder.ServerRelativeUrl
        }
        Else
        {        
            $TargetFolderSiteRelativeURL = $TargetFolder.ServerRelativeURL.Replace($Web.ServerRelativeUrl,"") 
        }  
 
        #Get All Items from the Source
        $SourceItems = Get-ChildItem -Path $SourceFolderPath -Recurse
        $Source = @($SourceItems | Select FullName,  PSIsContainer,
                                     @{Label='TargetItemURL';Expression={$_.FullName.Replace($SourceFolderPath,$TargetFolderSiteRelativeURL).Replace("\","/")}}, 
                                            @{Label='LastUpdated';Expression={$_.LastWriteTimeUtc.ToString()}})

        #Get All Files from the target document library - In batches of 2000
        $TargetFiles = Get-PnPListItem -List $TargetLibraryName -PageSize 2000
        $Target = @($TargetFiles | Select @{Label='FullName';Expression={$_.FieldValues.FileRef.Replace($TargetFolder.ServerRelativeURL,$SourceFolderPath).Replace("/","\")}},
                                                @{Label='PSIsContainer';Expression={$_.FileSystemObjectType -eq "Folder"}},
                                                    @{Label='TargetItemURL';Expression={$_.FieldValues.FileRef.Replace($Web.ServerRelativeUrl,"")}},
                                                        @{Label='LastUpdated';Expression={$_.FieldValues.Modified.ToUniversalTime().ToString()}})

        #Compare Source and Target and upload/update files which are not in the target
        $Counter = 1
        $FilesDiff = Compare-Object -ReferenceObject $Source -DifferenceObject $Target -Property FullName, PSIsContainer, TargetItemURL, LastUpdated
        #$FilesDiff | Export-csv -path "C:\Temp\diff.csv" -NoTypeInformation
        $SourceDelta = @($FilesDiff | Where {$_.SideIndicator -eq "<="}) 
        $SourceDeltaCount = $SourceDelta.Count

        #Check if Source Files are changed
        If($SourceDeltaCount -gt 0)
        {
            Write-host "Found $SourceDeltaCount new differences in the Source!" 
            Add-content $Logfile -value "Found $SourceDeltaCount new differences in the Source!"   
    
            $SourceDelta | Sort-Object TargetItemURL | ForEach-Object {
                #Calculate Target Folder URL for the file
                $TargetFolderURL = (Split-Path $_.TargetItemURL -Parent).Replace("\","/")
                $ItemName = Split-Path $_.FullName -leaf
                #Replace Invalid Characters
                $ItemName = [RegEx]::Replace($ItemName, "[{0}]" -f ([RegEx]::Escape([String]'\"*:<>?/\|')), '_')

                #Display Progress bar
                $Status  = "Importing '" + $ItemName + "' to " + $TargetFolderURL +" ($($Counter) of $($SourceDeltaCount))"
                Write-Progress -Activity "Importing Files from the Source..." -Status $Status -PercentComplete (($Counter / $SourceDeltaCount) * 100)

                If($_.PSIsContainer)
                {
                    #Ensure Folder
                    $Folder  = Resolve-PnPFolder -SiteRelativePath ($TargetFolderURL+"/"+$ItemName) -Includes ListItemAllFields
                    
                    Set-PnPListItem -List $TargetLibraryName -Identity $Folder.ListItemAllFields.Id -Values @{"Modified"=  ([DateTime]$_.LastUpdated).ToLocalTime()} | Out-null
                    Write-host "Ensured Folder '$($ItemName)' to Folder $TargetFolderURL"
                    Add-content $Logfile -value "Ensured Folder '$($ItemName)' to Folder $TargetFolderURL"
                }
                Else
                {
                    #Upload File
                    $File  = Add-PnPFile -Path $_.FullName -Folder $TargetFolderURL -Values @{"Modified"=  ([DateTime]$_.LastUpdated).ToLocalTime()}
                    Write-host "Ensured File '$($_.FullName)' to Folder $TargetFolderURL"
                    Add-content $Logfile -value "Ensured File '$($_.FullName)' to Folder $TargetFolderURL"
                }
                $Counter++
            }
        }
        Else
        {
            Write-host "Found no new Items in the Source! Total Items in Source: $SourceItemsCount , Number Items in Target: $($Library.Itemcount)" 
            Add-content $Logfile -value "Found no new Items in the Source! Items in Source: $SourceItemsCount ,  Number Items in Target: $($Library.Itemcount)" 
        }      
    }
    Catch {
        Write-host -f Red "Error:" $_.Exception.Message 
        Add-content $Logfile -value "Error:$($_.Exception.Message)"
    }
    Finally {
       Add-content $Logfile -value "---------------------- Import File Share Script Completed: $(Get-date -format 'dd/MM/yyy hh:mm:ss tt')-----------------"
    }
}

#Function to Remove Files Delta in SharePoint Online (Files that are no longer exists in FileShare )
Function Remove-FileShareDeltaInSPO
{
 param
    (
        [Parameter(Mandatory=$true)] [string] $SiteURL,
        [Parameter(Mandatory=$true)] [string] $SourceFolderPath,
        [Parameter(Mandatory=$true)] [string] $TargetLibraryName,            
        [Parameter(Mandatory=$true)] [string] $LogFile
    )

    Try {
        Add-content $Logfile -value "`n---------------------- Remove FileShare Delta Script Started: $(Get-date -format 'dd/MM/yyy hh:mm:ss tt')-------------------"   

        #Get Number of Source Items from the Source Folder
        $SourceItemsCount =  (Get-ChildItem -Path $SourceFolderPath -Recurse).count

        #Get the Target Library
        $Web = Get-PnPWeb
        $Library = Get-PnPList $TargetLibraryName -Includes RootFolder
        $TargetFolder = $Library.RootFolder

        #Get the site relative path of the target Folder
        If($web.ServerRelativeURL -eq "/")
        {
            $TargetFolderSiteRelativeURL = $TargetFolder.ServerRelativeUrl
        }
        Else
        {        
            $TargetFolderSiteRelativeURL = $TargetFolder.ServerRelativeURL.Replace($Web.ServerRelativeUrl,"") 
        }        
 
        #Get All Items from the Source
        $SourceItems = Get-ChildItem -Path $SourceFolderPath -Recurse
        $Source = @($SourceItems | Select FullName,  PSIsContainer,
                                     @{Label='TargetItemURL';Expression={$_.FullName.Replace($SourceFolderPath,$TargetFolderSiteRelativeURL).Replace("\","/")}}, 
                                            @{Label='LastUpdated';Expression={$_.LastWriteTimeUtc.ToString()}})

        #Get All Files from the target document library - In batches of 2000
        $TargetFiles = Get-PnPListItem -List $TargetLibraryName -PageSize 2000
        $Target = @($TargetFiles | Select @{Label='FullName';Expression={$_.FieldValues.FileRef.Replace($TargetFolder.ServerRelativeURL,$SourceFolderPath).Replace("/","\")}},
                                                @{Label='PSIsContainer';Expression={$_.FileSystemObjectType -eq "Folder"}},
                                                    @{Label='TargetItemURL';Expression={$_.FieldValues.FileRef.Replace($Web.ServerRelativeUrl,"")}},
                                                        @{Label='LastUpdated';Expression={$_.FieldValues.Modified.ToUniversalTime().ToString()}})

        #Compare Source and Target and remove files which are not in the Source
        $Counter = 1
        $FilesDiff = Compare-Object -ReferenceObject $Source -DifferenceObject $Target -Property FullName, PSIsContainer, TargetItemURL, LastUpdated     
        $TargetDelta = @($FilesDiff | Where {$_.SideIndicator -eq "=>"})
        $TargetDeltaCount = $TargetDelta.Count

        #Check if Target Files Needs to be deleted
        If($TargetDeltaCount -gt 0)
        {
            Write-host "Found $TargetDeltaCount differences in the Target!" 
            Add-content $Logfile -value "Found $TargetDeltaCount differences in the Target!"   
    
            $TargetDelta | Sort-Object TargetItemURL -Descending | ForEach-Object {
                #Display Progress bar
                $Status  = "Removing Item " + $_.TargetItemURL +" ($($Counter) of $($TargetDeltaCount))"
                Write-Progress -Activity "Removing Items in the Target..." -Status $Status -PercentComplete (($Counter / $TargetDeltaCount) * 100)

                If($_.PSIsContainer)
                {
                    #Empty and Remove the Folder
                    $Folder  = Get-PnPFolder -Url $_.TargetItemURL -ErrorAction SilentlyContinue
                    If($Folder -ne $Null)
                    {
                        $Folder.Recycle() | Out-Null
                        Invoke-PnPQuery

                        Write-host "Removed Folder '$($_.TargetItemURL)'"
                        Add-content $Logfile -value "Removed Folder '$($_.TargetItemURL)'"
                    }
                }
                Else
                {
                    $File = Get-PnPFile -Url $_.TargetItemURL -ErrorAction SilentlyContinue
                    If($File -ne $Null)
                    {
                        #Remove the File
                        Remove-PnPFile -SiteRelativeUrl $_.TargetItemURL -Force
                        Write-host "Removed File '$($_.TargetItemURL)'"
                        Add-content $Logfile -value "Removed File '$($_.TargetItemURL)'"
                    }
                }
                $Counter++
            }
        }
        Else
        {
            Write-host "Found no diferences in the Target! Total Items in Source: $SourceItemsCount , Number Items in Target: $($Library.Itemcount)" 
            Add-content $Logfile -value "Found no differences in the Target! Items in Source: $SourceItemsCount ,  Number Items in Target: $($Library.Itemcount)" 
        }
    }
    Catch {
        Write-host -f Red "Error:" $_.Exception.Message 
        Add-content $Logfile -value "Error:$($_.Exception.Message)"
    }
    Finally {
       Add-content $Logfile -value "---------------------- Remove FileShare Delta Script Completed: $(Get-date -format 'dd/MM/yyy hh:mm:ss tt')-----------------"
    }
}

Function Sync-FileShareToSPO()
{
 param
    (
        [Parameter(Mandatory=$true)] [string] $SiteURL,
        [Parameter(Mandatory=$true)] [string] $SourceFolderPath,
        [Parameter(Mandatory=$true)] [string] $TargetLibraryName,            
        [Parameter(Mandatory=$true)] [string] $LogFile
    )

    Try {
        #Connect to PnP Online
        Connect-PnPOnline -Url $SiteURL -UseWebLogin

        #Call the function to Import New Files from Fileshare to SPO
        Import-FileShareToSPO -SiteURL $SiteURL -SourceFolderPath $SourceFolderPath -TargetLibraryName $TargetLibraryName -LogFile $LogFile

        #Call the function to Remove Files in SPO that are moved/deleted in Fileshare
        Remove-FileShareDeltaInSPO -SiteURL $SiteURL -SourceFolderPath $SourceFolderPath -TargetLibraryName $TargetLibraryName -LogFile $LogFile
    }
    Catch {
        Write-host -f Red "Error:" $_.Exception.Message
    }
    Finally {
       DisConnect-PnPOnline
    }
}

#Call the Function to Sync Files from Fileshare to SharePoint Online
Sync-FileShareToSPO -SiteURL "https://crescent.sharepoint.com/sites/Projects" -SourceFolderPath "\\FileServer\Reports" `
                            -TargetLibraryName "Documents" -LogFile "C:\Temp\SyncFileShare-LOG.log"

This script checks the timestamp of each file from the given fileshare and uploads it to SharePoint Online if they are updated in File share. Similarly, any file deleted in Fileshare will be deleted in SharePoint Online as well. It generates a log file also every time it runs. Please note, this script does only one-way sync from Fileshare to SharePoint Online. You can Schedule PowerShell script in Windows Task scheduler , so that it runs on schedule. E.g. for every 10 min.

You can also use this script to sync files from local drive to SharePoint by changing the SourceFolderPath parameter to any folder in your computer. E.g. "C:\Temp\Upload"

Disable Mass Delete Email Notification "Heads up! We noticed that you recently deleted a large number of files from a site" in SharePoint Online

2020-06-11T14:04:00.011+05:30

Requirement: Disable the alert Email notification "Heads up! We noticed that you recently deleted a large number of files from a site" in SharePoint Online.

When you delete files and folders in bulk, SharePoint Online triggers the following Email to the tenant administrators and the user who deleted files to tell about the deleted files and how to recover the files from the Recycle Bin. This feature proactively alerts users when a large number of files are deleted in SharePoint Online or OneDrive for Business.

"Heads up! We noticed that you recently deleted a large number of files from a site

Files are permanently removed from the online recycle bin 93 days after they're deleted

How to Disable Bulk Delete Email Alerts in SharePoint Online?

This Email is causing confusion when users move files as they are get picked up as being deleted. So we wanted to turn-off these alerts. These email notifications are triggers from by the alert policy called "Unusual volume of file deletion" which is enabled by default in your Office 365 tenant. You can turn it off by following below steps:

Login to Office 365 Admin Center >> Open "Security & Compliance" center
Click on "Alert Policies" (URL: https://protection.office.com/alertpolicies),
Search and find the alert policy called "Unusual volume of file deletion" and click on the policy to edit.
Now, you can disable the policy by setting the status to "Off".

This disables any further email notifications. You can also edit the policy to configure recipients of the Emails, disable notifications, etc. so that alerts will still be triggered and logged in the Admin Center for auditing purposes, but users won't receive any Email notifications.

Further information about these policies can be found in the article: https://docs.microsoft.com/en-us/microsoft-365/compliance/alert-policies?view=o365-worldwide

SharePoint Online: Find All Files Exceeding Maximum URL Length Limitation using PowerShell

2020-04-20T01:15:00.002+05:30

Microsoft Office Excel files stored in deep Folder, sub-folder structures with long names often result in an error when they exceed the maximum file URL length of 218 characters as in my other article SharePoint Online: URL Length Limitation of 218 Characters in Excel Files. So, we wanted to proactively scan the entire SharePoint Online environment for Excel files with long URLs.

PowerShell to Get All Excel Files that Exceeds Maximum Character Limit URL
This PowerShell script scans Excel files in all document libraries in a given site collection, Checks if any file URL length is greater than the given limit and export output report to a CSV format.

Import-Module Microsoft.Online.SharePoint.PowerShell -DisableNameChecking

#Function to scan all files with long file names in a site
Function Scan-SPOLongFilePath($SiteURL)
{   
    Try {
        #Setup the context
        $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
        $Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.UserName,$Cred.Password)
 
        #Get the web from given URL and its subsites
        $Web = $Ctx.web
        $Ctx.Load($Web)
        $Ctx.Load($Web.Lists)
        $Ctx.Load($web.Webs)
        $Ctx.executeQuery()
         
        #Arry to Skip System Lists and Libraries
        $SystemLists = @("Converted Forms", "Master Page Gallery", "Customized Reports", "Form Templates", "List Template Gallery", "Theme Gallery",
                            "Reporting Templates", "Solution Gallery", "Style Library", "Web Part Gallery","Site Assets", "wfpub", "Site Pages", "Images")
     
        Write-host -f Yellow "Processing Site: $SiteURL"
 
        #Filter Document Libraries to Scan 
        $Lists = $Web.Lists | Where {$_.BaseType -eq "DocumentLibrary" -and $_.Hidden -eq $false -and $SystemLists -notcontains $_.Title}
        #Loop through each document library
        Foreach ($List in $Lists)
        {
            #Get All Files of Given File type from the document library - Excluding Folders
            $Query =  New-Object Microsoft.SharePoint.Client.CamlQuery
            $CAMLQuery =  "<View Scope='RecursiveAll'><Query><Where><And><Eq><FieldRef Name='FSObjType' /><Value Type='Integer'>0</Value></Eq><Eq><FieldRef Name='File_x0020_Type' /><Value Type='Text'>$FileExtension</Value></Eq></And></Where></Query></View>"
            $Query.ViewXml = $CAMLQuery
            $ListItems= $List.GetItems($Query)
            $Ctx.Load($ListItems)
            $Ctx.ExecuteQuery() 
            
            If($ListItems.count -gt 0)
            {
                Write-host -f Cyan "`t Processing Document Library: '$($List.Title)', Auditing $($ListItems.Count) Item(s)"
 
                $DocumentInventory = @()
                #Iterate through each file and get data
                Foreach($Item in $ListItems)
                {
                    $File = $Item.File
                    $Ctx.Load($File)
                    $Ctx.ExecuteQuery()

                    #calculate the Absolute encoded URL of the File
                    If($Web.ServerRelativeUrl -eq "/")
                    {
                        $AbsoluteURL=  $("{0}{1}" -f $Web.Url, $ListItem.FieldValues["FileRef"])
                    }
                    else
                    {
                        $AbsoluteURL=  $("{0}{1}" -f $Web.Url.Replace($Web.ServerRelativeUrl,''), $Item.FieldValues["FileRef"])
                    }
                    $AbsoluteURL = [uri]::EscapeUriString($AbsoluteURL)

                    If($AbsoluteURL.length -gt $MaxUrlLength)
                    {
                        Write-host "`t`tFound a Long File URL at '$AbsoluteURL'" -f Green
                        #Collect document data
                        $DocumentData = New-Object PSObject
                        $DocumentData | Add-Member NoteProperty SiteURL($SiteURL)
                        $DocumentData | Add-Member NoteProperty DocLibraryName($List.Title)
                        $DocumentData | Add-Member NoteProperty FileName($File.Name)
                        $DocumentData | Add-Member NoteProperty FileURL($AbsoluteURL)
                        $DocumentData | Add-Member NoteProperty CreatedBy($Item["Author"].Email)
                        $DocumentData | Add-Member NoteProperty CreatedOn($File.TimeCreated)
                        $DocumentData | Add-Member NoteProperty ModifiedBy($Item["Editor"].Email)
                        $DocumentData | Add-Member NoteProperty LastModifiedOn($File.TimeLastModified)
                        $DocumentData | Add-Member NoteProperty Size-KB([math]::Round($File.Length/1KB))

                        #Add the result to an Array
                        $DocumentInventory += $DocumentData
                    }
                }
                #Export the result to CSV file
                $DocumentInventory | Export-CSV $ReportOutput -NoTypeInformation -Append
            }
        }  
        #Iterate through all subsites of the current site
        ForEach ($Subweb in $Web.Webs)
        {
            #Call the function recursively
            Scan-SPOLongFilePath($Subweb.url)
        }
    }
    Catch {
        write-host -f Red "Error Scaning Document Library Inventory!" $_.Exception.Message
    }
}

#Set Parameters
$SiteURL= "https://crescent.sharepoint.com/sites/Marketing"
$ReportOutput="C:\temp\LongFileNames.csv"
$FileExtension = "xlsx"
$MaxUrlLength = 259

#Get Credentials to connect
$Cred = Get-Credential

#Delete the Output Report if exists
If (Test-Path $ReportOutput) { Remove-Item $ReportOutput }

#Call the function 
Scan-SPOLongFilePath $SiteURL

For lists and libraries with < 5000 items, this script works absolutely fine. Let's re-code in in PnP PowerShell.

PnP PowerShell to Audit SharePoint Online Site Collection for Long URLs
This PnP PowerShell script scans the given site collection for long files and folders, and exports its findings to a CSV file.

#Parameters
$SiteURL = "https://Crescent.SharePoint.com/sites/docs"
$MaxUrlLength = 218
$CSVPath = "C:\Temp\LongURLInventory.csv"
$global:LongURLInventory = @()
$Pagesize = 2000
 
#Get Credentials to connect
#$Credential = Get-Credential
   
#Function to scan and collect long files
Function Get-PnPLongURLInventory
{
    [cmdletbinding()]
    param([parameter(Mandatory = $true, ValueFromPipeline = $true)] $Web)
 
    Write-host "Scanning Files with Long URL in Site '$($Web.URL)'" -f Yellow
    If($Web.ServerRelativeUrl -eq "/")
    {
        $TenantURL= $Web.Url
    }
    Else
    {
        $TenantURL= $Web.Url.Replace($Web.ServerRelativeUrl,'')
    }
    
    #Get All Large Lists from the Web - Exclude Hidden and certain lists
    $ExcludedLists = @("Form Templates", "Preservation Hold Library","Site Assets", "Pages", "Site Pages", "Images",
                            "Site Collection Documents", "Site Collection Images","Style Library") 
                             
    #Get All Document Libraries from the Web
    Get-PnPList -Web $Web -PipelineVariable List | Where-Object {$_.BaseType -eq "DocumentLibrary" -and $_.Hidden -eq $false -and $_.Title -notin $ExcludedLists -and $_.ItemCount -gt 0} | ForEach-Object {
        #Get Items from List   
        $global:counter = 0;
        $ListItems = Get-PnPListItem -List $_ -Web $web -PageSize $Pagesize -Fields Author, Created, File_x0020_Type -ScriptBlock { Param($items) $global:counter += $items.Count; Write-Progress -PercentComplete ($global:Counter / ($_.ItemCount) * 100) -Activity "Getting List Items of '$($_.Title)'" -Status "Processing Items $global:Counter to $($_.ItemCount)";}
        $LongListItems = $ListItems | Where { ([uri]::EscapeUriString($_.FieldValues.FileRef).Length + $TenantURL.Length ) -gt $MaxUrlLength }
        Write-Progress -Activity "Completed Retrieving Items from List $($List.Title)" -Completed
                
        If($LongListItems.count -gt 0)
        {
            #Get Root folder of the List
            $Folder = Get-PnPProperty -ClientObject $_ -Property RootFolder
            Write-host "`tFound '$($LongListItems.count)' Items with Long URLs at '$($Folder.ServerRelativeURL)'" -f Green

            #Iterate through each long url item and collect data           
            ForEach($ListItem in $LongListItems)
            {
                #Calculate Encoded Full URL of the File
                $AbsoluteURL =  "$TenantURL$($ListItem.FieldValues.FileRef)"
                $EncodedURL = [uri]::EscapeUriString($AbsoluteURL)
 
                    #Collect document data
                    $global:LongURLInventory += New-Object PSObject -Property ([ordered]@{
                        SiteName  = $Web.Title
                        SiteURL  = $Web.URL
                        LibraryName = $List.Title
                        LibraryURL = $Folder.ServerRelativeURL
                        ItemName = $ListItem.FieldValues.FileLeafRef
                        Type = $ListItem.FileSystemObjectType
                        FileType = $ListItem.FieldValues.File_x0020_Type
                        AbsoluteURL = $AbsoluteURL
                        EncodedURL = $EncodedURL
                        UrlLength = $EncodedURL.Length                      
                        CreatedBy = $ListItem.FieldValues.Author.LookupValue
                        CreatedByEmail  = $ListItem.FieldValues.Author.Email
                        CreatedAt = $ListItem.FieldValues.Created
                        ModifiedBy = $ListItem.FieldValues.Editor.LookupValue
                        ModifiedByEmail = $ListItem.FieldValues.Editor.Email
                        ModifiedAt = $ListItem.FieldValues.Modified                        
                    })
                }
            }
        }
}

#Connect to Site collection
Connect-PnPOnline -Url $SiteURL -UseWebLogin
  
#Call the Function for Web & all Subwebs
Get-PnPWeb | Get-PnPLongURLInventory
Get-PnPSubWebs -Recurse| ForEach-Object { Get-PnPLongURLInventory $_ }
Disconnect-PnPOnline
 
#Export Documents Inventory to CSV
$Global:LongURLInventory | Export-Csv $CSVPath -NoTypeInformation
Write-host "Report has been Exported to '$CSVPath'"  -f Magenta

How about getting this inventory for SharePoint Online Tenant?
This time, let's scan all files across all site collections in the entire tenant for all files with long file URLs.

Make sure you have Admin rights to all site collections in the tenant prior executing this script. Otherwise, you'll get access denied "The remote server returned an error: (403) Forbidden" Error! How to Add Site Collection Administrator to All Sites in SharePoint Online Tenant?

#Parameters
$Domain =  "crescent" #Domain Name in SharePoint Online. E.g. https://Crescent.SharePoint.com
$MaxUrlLength = 218
$CSVFile = "C:\Temp\LongURLInventory.csv"
$Pagesize = 2000

#Frame Tenant URL and Tenant Admin URL
$TenantURL = "https://$Domain.SharePoint.com"
$TenantAdminURL = "https://$Domain-Admin.SharePoint.com"
  
#Function to scan and collect long files
Function Get-PnPLongURLInventory
{
    [cmdletbinding()]
    param([parameter(Mandatory = $true, ValueFromPipeline = $true)] $Web)
 
    Write-host "Scanning Files with Long URL in Site '$($Web.URL)'" -f Yellow

    #Get All Large Lists from the Web - Exclude Hidden and certain lists
    $ExcludedLists = @("Form Templates", "Preservation Hold Library","Site Assets", "Pages", "Site Pages", "Images",
                            "Site Collection Documents", "Site Collection Images","Style Library") 
                             
    #Get All Document Libraries from the Web
    Get-PnPList -Web $Web -Connection $SiteConn | Where-Object {$_.BaseType -eq "DocumentLibrary" -and $_.Hidden -eq $false -and $_.Title -notin $ExcludedLists -and $_.ItemCount -gt 0} | ForEach-Object {
        #Get Items from List   
        $global:counter = 0;
        $LongURLInventory = @()
        $ListItems = Get-PnPListItem -List $_ -Web $web -Connection $SiteConn -PageSize $Pagesize -Fields Author, Created, File_x0020_Type -ScriptBlock { Param($items) $global:counter += $items.Count; Write-Progress -PercentComplete ($global:Counter / ($_.ItemCount) * 100) -Activity "Getting List Items of '$($_.Title)'" -Status "Processing Items $global:Counter to $($_.ItemCount)";}
        $LongListItems = $ListItems | Where { ([uri]::EscapeUriString($_.FieldValues.FileRef).Length + $TenantURL.Length ) -gt $MaxUrlLength }                
        If($LongListItems.count -gt 0)
        {
            #Get Root folder of the List
            $Folder = Get-PnPProperty -ClientObject $_ -Property RootFolder
            Write-host "`tFound '$($LongListItems.count)' Item(s) with Long URLs at '$($Folder.ServerRelativeURL)'" -f Green

            #Iterate through each long url item and collect data           
            ForEach($ListItem in $LongListItems)
            {
                #Calculate Encoded Full URL of the File
                $AbsoluteURL =  "$TenantURL$($ListItem.FieldValues.FileRef)"
                $EncodedURL = [uri]::EscapeUriString($AbsoluteURL)
 
                    #Collect document data
                    $LongURLInventory += New-Object PSObject -Property ([ordered]@{
                        SiteName  = $Web.Title
                        SiteURL  = $Web.URL
                        LibraryName = $List.Title
                        LibraryURL = $Folder.ServerRelativeURL
                        ItemName = $ListItem.FieldValues.FileLeafRef
                        Type = $ListItem.FileSystemObjectType
                        FileType = $ListItem.FieldValues.File_x0020_Type
                        AbsoluteURL = $AbsoluteURL
                        EncodedURL = $EncodedURL
                        UrlLength = $EncodedURL.Length                      
                        CreatedBy = $ListItem.FieldValues.Author.LookupValue
                        CreatedByEmail  = $ListItem.FieldValues.Author.Email
                        CreatedAt = $ListItem.FieldValues.Created
                        ModifiedBy = $ListItem.FieldValues.Editor.LookupValue
                        ModifiedByEmail = $ListItem.FieldValues.Editor.Email
                        ModifiedAt = $ListItem.FieldValues.Modified                        
                    })
                }
                #Export Documents Inventory to CSV
                $LongURLInventory | Export-Csv $CSVFile -NoTypeInformation -Append
            }
            Write-Progress -Activity "Completed Exporting Long URL Items from List $($_.Title)" -Completed
        }
}

#Connect to Admin Center
Connect-PnPOnline -Url $TenantAdminURL -UseWebLogin

#Delete the Output report file if exists
If (Test-Path $CSVFile) { Remove-Item $CSVFile }

#Get All Site collections - Filter BOT and MySite Host
$Sites = Get-PnPTenantSite -Filter "Url -like '$TenantURL'"
  
#Iterate through all site collections
$Sites | ForEach-Object {
    #Connect to each site collection
    $SiteConn = Connect-PnPOnline -Url $_.URL -UseWebLogin -ReturnConnection
  
    #Call the Function for Web & all Subwebs
    Get-PnPWeb -Connection $SiteConn | Get-PnPLongURLInventory
    Get-PnPSubWebs -Recurse -Connection $SiteConn | ForEach-Object { Get-PnPLongURLInventory $_ }

    Disconnect-PnPOnline -Connection $SiteConn
}

Please note, Although the Microsoft documentation says its 218 characters, I found up to 259 works fine!

SharePoint Online: Hide Recycle Bin from Quick Launch

2020-04-19T22:02:00.006+05:30

Requirement: Hide Recycle bin from SharePoint Online Quick Launch

How to Hide Recycle bin in SharePoint Online?

Recycle bin link was not relevant for site visitors as they can't delete from site or restore from Recycle bin and we wanted to hide the recycle bin from the Quick launch in SharePoint Online.

Case 1: Hide Recycle bin from quick launch in Classic SharePoint UI

If you are using classic experience sites in SharePoint Online sites, you can hide the recycle bin from quick launch of a particular page with this CSS. Just edit the page, Add a script editor web part and place this CSS in it.

CSS to hide hide recycle bin in SharePoint Online:

<style type="text/css">
#sideNavBox a[title="Recycle Bin"]
{
    display:none!important
}
</style>

This hides the recycle bin link from quick launch navigation of a specific page. If you want to do it at the site level, you can either edit the master page and place the above CSS under:

<asp:ContentPlaceHolder id="PlaceHolderAdditionalPageHead" runat="server" />

Otherwise, you can use "Alternate CSS" from Master page page settings of the site.

Case 2: Remove Recycle bin from quick launch in Modern SharePoint Online Sites
If you have modern site, you can remove "Recycle bin" link from quick launch (or any link in left navigation) with the help of "Navigation" option of SharePoint publishing feature. First make sure you have Publishing server feature enabled at both Site collection and at site level.

Go to Site Settings >> Click on Site Collection Features >> Activate "SharePoint Server Publishing Infrastructure" feature. It takes a moment to activate the feature.
Once activated, Click on "Site Features" link from site settings >> Activate "SharePoint Server Publishing" feature.

Once the publishing feature is enabled, recycle bin is removed from the quick launch automatically! Similarly, you can hide any link such as "Site Contents" from SharePoint Online quick launch bar by:

Go to Site settings >> Click on "Navigation" under "Look and Feel"
Now, you can select and delete any link from the current launch navigation.
Click the OK button to save the changes to the navigation.

That's all! The changes will be saved, and you will be returned to the Site Settings page.

You can also add the link https://yourtenant.sharepoint.com/sites/your-site/_layouts/15/RecycleBin.aspx to the navigation and set audience targetting to show recycle bin only to specific user or group!

How to Promote a Site Page to News Post in SharePoint Online?

2020-03-31T21:58:00.002+05:30

Requirement: Convert Site Page into News post in SharePoint Online.

How to Convert a Site Page into News Post in SharePoint Online?
In modern SharePoint Online sites, any existing site page can be converted into the News page, so that it appears in News web parts. To promote a site page to News page, do the following:

Navigate to the site page >> Click on "Promote" button on the toolbar
Click on "Post as News on this site" button in the tool pane.

It's fairly easy to promote a normal site page, isn't it? Once promoted or demoted, You have to republish the page for the search engine to re-index! Otherwise, You may not find them under places like News hub.

SharePoint Online: Demote a News Post Into Site Page
How about the reverse? demoting news page back to the site page to remove it from news pages? As there is no interface to do this, we can get this done by PnP PowerShell. Setting the "PromotedState" field to "0" demotes the news page.

$SiteURL = "https://crescent.sharepoint.com/sites/Intranet"

#Connect to Site
Connect-PnPOnline $SiteURL -UseWebLogin

#Get all Site pages - along with their IDs
Get-PnPListItem -List "SitePages"

#Demote News page with ID "4" to Site Page
Set-PnPListItem -List "SitePages" -Identity 4 -Values @{"PromotedState"="0"}

BTW, Promoted news posts have the value of "2" for PromotedState field.

SharePoint Online: How to Make a List Read-Only?

2020-03-28T18:36:00.006+05:30

Requirement: Make a SharePoint Online list to read only.

How to make a list read only in SharePoint Online?

Well, there is no direct way to set a SharePoint Online list to read-only. However, we can follow this workaround to make a list to read-only mode.

Navigate to your SharePoint Online List >> Click on Settings gear and choose "List Settings"
Click on "Permissions for this list" link under "Permissions and Management" group of the list settings page.
Click on "Stop Inheriting Permissions" button in the ribbon and confirm the prompt, if the list is not with broken permissions already.
Select users and groups that has permissions other than read. E.g. Full Control, Edit, contribute, Etc. and click on "Edit User Permissions" button in the ribbon.
In the Edit Permissions page, Select "Read" permissions and Click on "OK" to reset everyone's permission to "Read".

PowerShell to make a list read only in SharePoint Online

Lets automate above steps to make a SharePoint Online list or library to read only.

#Parameter
$SiteURL = "https://Crescent.sharepoint.com/sites/PMO"
$ListName= "Projects"
 
#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -UseWebLogin
 
#Get the Web and List
$Web = Get-PnPWeb
$List = Get-PnPList -Identity $ListName -Includes HasUniqueRoleAssignments, RoleAssignments

#Break Permissions of the List
If ($List.HasUniqueRoleAssignments -eq $False)
{
    Set-PnPList -Identity $ListName -BreakRoleInheritance -CopyRoleAssignments
}
 
#Get Read Permission Level
$ReadPermission = Get-PnPRoleDefinition -Identity "Read"

#Grant "Read" permissions, if its not granted already
$List.RoleAssignments | ForEach-Object {
    #Get the user or group of the assignment - Handle error for orphans
    $Member = Get-PnPProperty -ClientObject $_ -Property Member -ErrorAction SilentlyContinue

    If($Member.IsHiddenInUI -eq $False)
    {
        Get-PnPProperty -ClientObject $_ -Property RoleDefinitionBindings | Out-Null
 
        #Check if the current assignment has any permission other than Read or related
        $PermissionsToReplace = $_.RoleDefinitionBindings | Where {$_.Hidden -eq $False -And $_.Name -Notin ("Read", "Restricted Read", "Restricted Interfaces for Translation")}
        
        #Grant "Read" permissions, if its not granted already
        If($PermissionsToReplace -ne $Null)
        {
            $_.RoleDefinitionBindings.Add($ReadPermission)
            $_.Update()
            Invoke-PnPQuery
            Write-host "Added 'Read' Permissions to '$($Member.Title)'" -ForegroundColor Cyan
        }
    }
}
#Reload List permissions
$List = Get-PnPList -Identity $ListName -Includes RoleAssignments

#Remove All permissions other than Read or Similar
$List.RoleAssignments | ForEach-Object {
    #Get the user or group of the assignment - Handle error for orphans
    $Member = Get-PnPProperty -ClientObject $_ -Property Member #-ErrorAction SilentlyContinue | Out-Null    
    If($Member.IsHiddenInUI -eq $False)
    {
        Get-PnPProperty -ClientObject $_ -Property RoleDefinitionBindings | Out-Null
 
        $PermissionsToRemove = $_.RoleDefinitionBindings | Where {$_.Hidden -eq $False -And $_.Name -Notin ("Read", "Restricted Read", "Restricted Interfaces for Translation")}
        If($PermissionsToRemove -ne $null)
        {
            ForEach($RoleDefBinding in $PermissionsToRemove)
            {
                $_.RoleDefinitionBindings.Remove($RoleDefBinding)
                Write-host "Removed '$($RoleDefBinding.Name)' Permissions from '$($Member.Title)'" -ForegroundColor Yellow    
            }
            $_.Update()
            Invoke-PnPQuery
        }
    }
}
Write-host "List is set to Read-Only Successfully!" -f Green

This PowerShell script resets all permissions with "Read". Please note, These methods doesn't control Site Collection Administrators!

How to Convert SharePoint Online Site to Microsoft Teams?

2020-02-08T17:57:00.008+05:30

Requirement: Convert SharePoint Online Site to Microsoft Teams.

Microsoft Teams is a chat-based collaboration platform that brings together people, conversations, content, and tools to create a more open, digital environment. Every time you create a new team in Teams, you're also creating a new Office 365 Group, Calendar, OneNote, Planner, SharePoint team site, etc. and ties into other Office 365 cloud applications. And for every channel you create in Teams, a folder within a SharePoint document library is automatically created.

How to migrate SharePoint Online Site to Teams?
You may have an existing SharePoint Online team site for a dedicated project or business function and using it to storing documents and communication and wants to take advantage from Microsoft Teams and here is how you can we can convert your existing SharePoint Online team sites into Microsoft Team so that the files from the existing document library can be accessed in Microsoft Teams.

Prerequisites: Your existing SharePoint Online site must be group connected to be able to integrate with Microsoft Teams! If it is not already, Its a matter of few clicks through settings menu link. Step by step instructions are here: How to Connect SharePoint Online site to Office 365 Group?

Option 1: Create a Microsoft Team from an existing SharePoint site
When you create a new modern SPO site with Office 365 Group, You'll see the Microsoft Teams prompt on the site home page.

Navigate to the home page of the existing SharePoint Online team site >> Click on Create a team from the Microsoft Teams prompt in the bottom left corner of the screen.
It spins the wheel with "Creating Team" message for a while and you'll get "Success! Click here to go to your Team" message Once your team is created. Clicking on it will take you to the Microsoft Teams app, and you can start collaborating in your new associated team. Also, it adds the "Teams" menu to SharePoint left navigation.

Option 2: Create Microsoft Teams from an existing SharePoint Site using Teams App
What if you have closed the "Create a Team" popup already in your SharePoint Site? Well, you can convert the SharePoint Online site to Teams with Microsoft Teams application. Here is how:

Open Microsoft Teams Client application from your desktop >> Click on "Join or create a team" link from the bottom of the Teams client application.
Click on the "Create Team" button under Join or create a team page >> Choose "Create from..." An existing Office 365 group or team
Click on "Office 365 group"
Select the Office 365 group of your existing SharePoint site and then click on the "Create" button.

and your Teams should be ready shortly!

Option 3: Create Teams from SharePoint Online using PowerShell:
We can migrate SharePoint Online site using PowerShell as well. Make sure you have Teams PowerShell module installed prior to running this script: How to Install Microsoft Teams PowerShell Module?

Import-Module Microsoft.Online.SharePoint.PowerShell -DisableNameChecking

$AdminCenterURL = "https://crescent-admin.sharepoint.com"
$SiteURL = "https://crescent.sharepoint.com/sites/legal"

#Get Credentials to connect
$Cred = Get-Credential

#Connect to SharePoint Online
Connect-SPOService -Url $AdminCenterURL -Credential $Cred

#Connect to Microsoft Teams
Connect-MicrosoftTeams -Credential $Cred

#Get the Site collection
$Site = Get-SPOSite -Identity $SiteURL

#Create Teams from the SharePoint Site
New-Team -GroupId $Site.GroupId.Guid

Creating Teams from SharePoint Online Site doesn't replace it with teams! Instead, it connects them together for better collaboration! These steps only "Team enable" an existing SharePoint site. You can continue using it as usual.

SharePoint Online: Get Subsite Permission Report using PowerShell

2020-02-05T23:25:00.014+05:30

Requirement: PowerShell script to get subsite permissions in SharePoint Online.

How to Get Subsite Permissions in SharePoint Online?

To get permissions of a SharePoint Online subsite,

Click on Setting gear >> Click on "Site Permissions" link
Click on "Advanced Permission Settings" in the site permissions page

This gives you a all users and groups that has permissions to the subsite.

SharePoint Online: PowerShell to Get Subsite Permissions

This PowerShell script extracts and exports all direct permissions of the given subsite (not any underlying objects such as list, folder, file) .

#Parameters
$SiteURL = "https://crescent.sharepoint.com/sites/Marketing/2020"
$ReportOutput = "C:\Temp\SitePermissionRpt.csv"

#Connect to Site
Connect-PnPonline -Url $SiteURL -UseWebLogin

#Get the web
$Web = Get-PnPWeb -Includes RoleAssignments

#Loop through each permission assigned and extract details
$PermissionData = @()
ForEach ($RoleAssignment in $Web.RoleAssignments)
{
    #Get the Permission Levels assigned and Member
    Get-PnPProperty -ClientObject $RoleAssignment -Property RoleDefinitionBindings, Member
    
    #Get the Permission Levels assigned
    $PermissionLevels = ($RoleAssignment.RoleDefinitionBindings | Select -ExpandProperty Name | Where {$_ -ne "Limited Access"}) -join ","
    $PermissionType = $RoleAssignment.Member.PrincipalType

    #Leave Principals with no Permissions
    If($PermissionLevels.Length -eq 0) {Continue}
    
    #Collect Permission Data
    $Permissions = New-Object PSObject
    $Permissions | Add-Member NoteProperty Name($RoleAssignment.Member.Title)
    $Permissions | Add-Member NoteProperty Type($PermissionType)
    $Permissions | Add-Member NoteProperty PermissionLevels($PermissionLevels)
    $PermissionData += $Permissions
}

$PermissionData
$PermissionData | Export-csv -path $ReportOutput -NoTypeInformation

This script gets all permissions from the subsite and generates a CSV as:

While this script extracts permissions applied on the subsite, what if you want to get members of each group along with the direct permissions to the site?

#Parameters
$SiteURL = "https://crescent.sharepoint.com/sites/Marketing/2020"
$ReportOutput = "C:\Temp\SitePermissionRpt.csv"

#Connect to Site
Connect-PnPonline -Url $SiteURL -UseWebLogin

#Get the web
$Web = Get-PnPWeb -Includes RoleAssignments

#Loop through each permission assigned and extract details
$PermissionData = @()
ForEach ($RoleAssignment in $Web.RoleAssignments)
{
    #Get the Permission Levels assigned and Member
    Get-PnPProperty -ClientObject $RoleAssignment -Property RoleDefinitionBindings, Member
    
    #Get the Permission Levels assigned
    $PermissionLevels = ($RoleAssignment.RoleDefinitionBindings | Select -ExpandProperty Name | Where { $_ -ne "Limited Access"} ) -join ","
    
    #Leave Principals with no Permissions
    If($PermissionLevels.Length -eq 0) {Continue}

    $PermissionType = $RoleAssignment.Member.PrincipalType
    #Get SharePoint group members
    If($PermissionType -eq "SharePointGroup")
    {
        #Get Group Members
        $GroupMembers = Get-PnPGroupMembers -Identity $RoleAssignment.Member.LoginName
                  
        #Leave Empty Groups
        If($GroupMembers.count -eq 0){ Continue }
        $GroupUsers = ($GroupMembers | Select -ExpandProperty LoginName | Where { $_ -ne "SHAREPOINT\system"}) -join "; "
  
        #Add the Data to Object
        $Permissions = New-Object PSObject
        $Permissions | Add-Member NoteProperty Name($RoleAssignment.Member.Title)
        $Permissions | Add-Member NoteProperty Accounts($GroupUsers)
        $Permissions | Add-Member NoteProperty Type($PermissionType)
        $Permissions | Add-Member NoteProperty PermissionLevels($PermissionLevels)
        $PermissionData += $Permissions
    }
    Else
    {
        #Add the Data to Object
        $Permissions = New-Object PSObject
        $Permissions | Add-Member NoteProperty Name($RoleAssignment.Member.Title)
        $Permissions | Add-Member NoteProperty Accounts($RoleAssignment.Member.LoginName)
        $Permissions | Add-Member NoteProperty Type($PermissionType)
        $Permissions | Add-Member NoteProperty PermissionLevels($PermissionLevels)
        $PermissionData += $Permissions
    }
}

#Export Permissions data to CSV file
$PermissionData | Export-csv -path $ReportOutput -NoTypeInformation

This script gets all the users, SharePoint groups and members of the SharePoint Online site or subsite along with the permissions assigned to them.

Navigate to Site File System in SharePoint Online using PowerShell

2020-02-04T21:26:00.004+05:30

Do you know you can navigate to site file system using SharePoint Online from PowerShell? Well, Here is how it works:

Create a Mapped Drive for SharePoint Online site or Library

To start with, we've to map SharePoint Online library as a drive from PowerShell using New-PSDrive cmdlet.

#Variable 
$LibraryPath = "\\crescentintranet.sharepoint.com@SSL\DavwwwRoot\Sites\marketing"
 
#Virtually map SharePoint Online Folder as PSDrive
New-PSDrive -name "M" -Root $LibraryPath -PSProvider filesystem

This doesn't appear in File explorer, But you can access it through PowerShell. You can also create a persistent drive with -Persist switch.

Access Mapped Drive through PowerShell

Once the library or site is mapped as network drive, you can start navigating to the site file system and manipulate it. E.g. Let's copy a folder with its content in a SharePoint Online document library.

#Switch to Mapped Drive
M:

#Change Current Directory to "Branding" Folder
CD Branding

#Get the contents of the Folder
Dir

#Copy a Folder with its contents
Copy 2019 -Destination 2020 -Recurse

Here is the entire flow:

SharePoint Online: Update List Items from a CSV File using PowerShell

2020-02-01T14:59:00.000+05:30

Requirement: Update SharePoint Online List from a CSV using PowerShell.

PowerShell to Update SharePoint Online List from CSV File

We have a list called "Projects" with a bunch of columns and wanted to update items from a CSV file. Here is the PowerShell CSOM script to update SharePoint Online list from CSV:

PowerShell Script to Update List Items from CSV:
Set parameters in the script, make sure CSV columns are mapped with the respective field internal name with your SharePoint Online list and then run the script:

#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

#Parameters
$SiteURL = "https://crescent.sharepoint.com/sites/PMO"
$ListName= "Projects"
$CSVFile = "C:\Temp\ListData.csv"

#Setup Credentials to connect
$Cred = Get-Credential
 
Try {
    #Get the data from CSV file
    $CSVData = Import-CSV $CSVFile

    #Setup the context
    $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
    $Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.UserName,$Cred.Password)
 
    #Get the List
    $List = $Ctx.Web.Lists.GetByTitle($ListName)
    $Ctx.Load($List)
    $Ctx.ExecuteQuery()

    #Loop through each Row in the CSV file and update the matching list item based on "ID"
    ForEach($Row in $CSVData)
    {
        Try {
            #Get list item to update
            $ListItem = $List.GetItemById($Row.ID)
            $Ctx.Load($ListItem)
            $Ctx.ExecuteQuery()

            #Update List Item
            $ListItem["ProjectName"] = $Row.'Project Name'
            $ListItem["IsActive"] = $Row.'Is Active'
            $ListItem["Start_x0020_Date"] = $Row.'Start Date'
            $ListItem["Status"] = $Row.'Status'
            $ListItem.Update()
            Write-host "Updated List Item:"$Row.ID
        }
        Catch {
            write-host "Error Updating Item $($Row.ID): $($_.Exception.InnerException.Message)" -foregroundcolor Yellow
        }
    }
}
Catch {
    write-host "Error: $($_.Exception.Message)" -foregroundcolor Red
}

This script works for simple field types such as Single line of text, Multiple lines of text, Date and Time, Yes/No, etc. What if you have a very large list with complex field types such as Person or Group, Hyperlink or Picture, Managed Metadata, Lookup and you have to update the list items from a CSV using PowerShell. Here is my list to update:

List structure:

My CSV File with data to update:

You can download this CSV file here: CSV to Update SharePoint Online List

PowerShell script to Update List Items from CSV:

Just set the parameters, make sure the data in the right format as in the CSV file. List items are updated with the matching ID field value from the given CSV file.

#Config Variables
$SiteURL = "https://crescent.sharepoint.com/sites/Projects"
$ListName = "Projects"
$CSVFilePath = "C:\Temp\ProjectData.csv"

#Function to get Lookup ID from Lookup Value
Function Get-LookupID($ListName, $LookupFieldName, $LookupValue)
{
    #Get Parent Lookup List and Field from Child Lookup Field's Schema XML
    $LookupField =  Get-PnPField -List $ListName -Identity $LookupFieldName
    [Xml]$Schema = $LookupField.SchemaXml
    $ParentListID = $Schema.Field.Attributes["List"].'#text'
    $ParentField  = $Schema.field.Attributes["ShowField"].'#text'
    $ParentLookupItem  = Get-PnPListItem -List $ParentListID -Fields $ParentField | Where {$_[$ParentField] -eq $LookupValue} | Select -First 1
 
    If($ParentLookupItem -ne $Null)
    {
        #Get the Parent Item ID
        Return $ParentLookupItem["ID"]
    }
    Else
    {
        Return $Null
    }
}

Try {
    #Connect to the Site
    Connect-PnPOnline -URL $SiteURL -UseWebLogin

    #Get the data from CSV file
    $CSVData = Import-CSV $CSVFilePath

    #Get the List
    $List = Get-PnPList -Identity $ListName
    
    #Get fields to Update from the List - Skip Read only, hidden fields, content type and attachments
    $ListFields = Get-PnPField -List $ListName | Where { (-Not ($_.ReadOnlyField)) -and (-Not ($_.Hidden)) -and ($_.InternalName -ne  "ContentType") -and ($_.InternalName -ne  "Attachments") }
     
    #Loop through each Row in the CSV file and update the matching list item ID
    ForEach($Row in $CSVData)
    {
        #Get the List Item to update
        $ListItem = Get-PnPListItem -List $List -Id $Row.ID -ErrorAction SilentlyContinue
        If($ListItem -ne $Null)
        {
            $ItemValue = @{}            
            $CSVFields = $Row | Get-Member -MemberType NoteProperty | Select -ExpandProperty Name
            #Map each field from CSV to target list
            Foreach($CSVField in $CSVFields)
            {
                $MappedField = $ListFields | Where {$_.InternalName -eq $CSVField}
                If($MappedField -ne $Null)
                {
                    $FieldName = $MappedField.InternalName
                    #Check if the Field value is not Null
                    If($Row.$CSVField -ne $Null)
                    {
                        #Handle Special Fields
                        $FieldType  = $MappedField.TypeAsString 
                        If($FieldType -eq "User" -or $FieldType -eq "UserMulti") #People Picker Field
                        {
                            $PeoplePickerValues = $Row.$FieldName.Split(",")
                            $ItemValue.add($FieldName,$PeoplePickerValues)
                        }
                        ElseIf($FieldType -eq "Lookup" -or $FieldType -eq "LookupMulti") #Lookup Field
                        {
                            $LookupIDs = $Row.$FieldName.Split(",") | ForEach-Object { Get-LookupID -ListName $ListName -LookupFieldName $FieldName -LookupValue $_ }                
                            $ItemValue.Add($FieldName,$LookupIDs)
                        }
                        Else
                        {
                            #Get Source Field Value and add to Hashtable
                            $ItemValue.Add($FieldName,$Row.$FieldName)
                        }
                    }
                }
            }
            Write-host "Updating List item ID $($Row.ID) with value:" 
            $ItemValue | Format-Table
            #Update List Item value
            Set-PnPListItem -List $ListName -Identity $Row.ID -Values $ItemValue | Out-Null
        }
        Else
        {
            Write-host "Could not find item ID $($Row.ID) in the list, Skipped!" -f Yellow
        }
    }
}
Catch {
    write-host "Error: $($_.Exception.Message)" -foregroundcolor Red
}

Note: Make sure your CSV column names are exactly matching with the list column internal names.

Please note, this script updates existing list items from a given CSV file. If you want to import (add new items) from a CSV file, use: SharePoint Online: Import CSV File into SharePoint List using PowerShell

SharePoint Online: How to Change Required Field Settings in a List or Library?

2020-01-30T00:26:00.020+05:30

Requirement: Set required fields in SharePoint Online list or document library.

How to Set Required Fields in SharePoint Online?

Required fields setting in SharePoint Online lists and libraries help to ensure that users enter certain information when saving items or uploading files. You can configure any field as a required field in SharePoint - or change a field to not be required. By default, Certain fields are marked as "Required" in SharePoint. E.g. For custom lists - Title field is required.

You can mark a field as required either while creating a new column or editing existing column in SharePoint Online list or document library by settings the configuration "Require that this column contains information" to "Yes".

You can also change required field configuration from list or library settings:

Navigate to the list or library where you would like to mark a field as required.
Click on the settings gear icon in the top right of the page and select list settings.
In the List Settings page, Scroll down to the Columns section and Click on a Column that you would like to make required.
Scroll down to the Additional Column Settings and to select "Yes" to make the field required or "No" to make it optional.
Select OK to save your changes.

When users tries to saved a item or file without filling in the required field, they'll see an error message "You can't leave this blank".

If the default value is set for any field, such as Choice fields - Required field validation won't trigger as the default value will automatically apply. When you mark a field as required in SharePoint Online document libraries, and bulk upload files either by Drag-and-drop/Explorer view/Menu, The documents will be checked out automatically and not available for other users, until you populate the required fields and check-in documents.

However, In modern libraries, this behavior is bit different: Documents will not be checked-out (as long as the "Require Check Out" in the Versioning settings is set to "No") when a mandatory field value is missing, but you'll see a message "Required Info" on those items.

PowerShell to Change Required Fields Settings in SharePoint Online:

We can turn required field settings for all fields in a SharePoint List or library to either ON or OFF using PowerShell as:

#Set Parameters
$SiteURL = "https://crescent.sharepoint.com/sites/PMO"
$ListName = "Projects"

#Connect to SharePoint Online
Connect-PnPOnline -Url $SiteURL -UseWebLogin

#Get the List
$List = Get-PnPList -Identity $ListName

#Get all Required Fields - Exclude system fields
$RequiredFields = Get-PnPField -List $List | Where { $_.Required -eq $true -and $_.Title -ne "Name" -and $_.ReadOnlyField -eq $false -and $_.Hidden -eq $false -and $_.InternalName -ne  "ContentType" -and $_.InternalName -ne  "Attachments"}

ForEach($Field in $RequiredFields)
{
    #Set Required Field Flag to False
    $Field.Required = $False
    $Field.Update()
    Invoke-PnPQuery
    Write-Host "Required Field Settings updated for $($Field.Title)"
}

This script removes require field flag from all columns. You can set the flag as $True and change the where condition to do the reverse as well. Here is my another post on setting a field as required or not-required using PowerShell: How to Make a Field Required in SharePoint Online using PowerShell?

SharePoint Online: Bulk Update Metadata Properties from a CSV File using PowerShell

2020-01-27T00:33:00.000+05:30

Requirement: Bulk Update Metadata of Multiple Documents from a CSV in SharePoint Online.

SharePoint Online: PowerShell to Bulk Edit Metadata Properties from a CSV File

We've a document library with a 100s of documents in several sub-folders and wanted to bulk update metadata of all those documents. While quick edit is one option, its really cumbersome to navigate through each folder and update properties of each document.

The solution we arrived is to prepare a CSV file with list of files to update and populate the CSV with required metadata and use PowerShell script to bulk edit metadata of all documents from the CSV file.

PowerShell to Get All Files from a Document Library to CSV

Let's generate document inventory from all folders and sub-folders of a given SharePoint Online document library:

#Parameters
$SiteURL = "https://crescent.sharepoint.com/sites/marketing"
$ListName= "Branding"
$CSVOutput = "C:\Temp\DocInventory.csv"

#Connect to SharePoint Online site
Connect-PnPOnline $SiteURL -UseWebLogin
 
#Get all Files from the document library
$ListItems = Get-PnPListItem -List $ListName -PageSize 2000 -Fields "FileLeafRef" | Where {$_.FileSystemObjectType -eq "File"}

#Iterate through each item
$DocumentsList = @()
Foreach ($Item in $ListItems) 
{
    #Extract File Name and URL
    $DocumentsList += New-Object PSObject -Property ([ordered]@{
        FileName          = $Item.FieldValues.FileLeafRef
        ServerRelativeURL = $Item.FieldValues.FileRef
    })
}
#Export the results
$DocumentsList | Export-Csv -Path $CSVOutput -NoTypeInformation

This script generates a CSV file with "FileName" and "ServerRelativeURL" columns of all files from a given document library.

PowerShell to Bulk Update Metadata in SharePoint Online

Once we have the files list ready, populated the file with relevant metadata and used this script to update. This script uses "ServerRelativePath" as the key to get files and update. Here is my CSV file:

You can download the CSV from CSV to Bulk Update metadata in SharePoint Online

#Parameters
$SiteURL = "https://crescent.sharepoint.com/sites/marketing"
$ListName= "Branding"
$CSVFile = "C:\Temp\DocMetadata.csv"

#Function to get Lookup ID from Lookup Value
Function Get-LookupID($ListName, $LookupFieldName, $LookupValue)
{
    #Get Parent Lookup List and Field from Child Lookup Field's Schema XML
    $LookupField =  Get-PnPField -List $ListName -Identity $LookupFieldName
    [Xml]$Schema = $LookupField.SchemaXml
    $ParentListID = $Schema.Field.Attributes["List"].'#text'
    $ParentField  = $Schema.field.Attributes["ShowField"].'#text'
    $ParentLookupItem  = Get-PnPListItem -List $ParentListID -Fields $ParentField | Where {$_[$ParentField] -eq $LookupValue} | Select -First 1 
    If($ParentLookupItem -ne $Null)  { Return $ParentLookupItem["ID"] }  Else  { Return $Null }
}

Try {
    #Connect to SharePoint Online site
    Connect-PnPOnline $SiteURL -UseWebLogin

    #Get the data from CSV file
    $CSVData = Import-CSV $CSVFile

    #Get fields to Update from the List - Skip Read-only, hidden fields and content type
    $ListFields = Get-PnPField -List $ListName | Where { (-Not ($_.ReadOnlyField)) -and (-Not ($_.Hidden)) -and ($_.InternalName -ne  "ContentType") }

    #Loop through each Row in the CSV file and update metadata
    ForEach($Row in $CSVData)
    {
        #Get All columns from CSV - Exclude "FileName" and "ServerRelativeURL" Columns
        $CSVFields = $Row | Get-Member -MemberType NoteProperty | Select -ExpandProperty Name | Where {$_.Name -notin ("FileName","ServerRelativeURL")}

        #Get the File to update
        $File = Get-PnPFile -Url $Row.ServerRelativeURL -AsListItem -ErrorAction SilentlyContinue
        If($File -ne $Null)
        {
            #Frame the Metadata to update
            $ItemValue = @{}

            #Map each field from CSV to target list
            Foreach($CSVField in $CSVFields)
            {
                $MappedField = $ListFields | Where {$_.InternalName -eq $CSVField}
                If($MappedField -ne $Null)
                {
                    $FieldName = $MappedField.InternalName
                    #Check if the Field value is not Null
                    If($Row.$CSVField -ne $Null)
                    {
                        #Handle Special Fields
                        $FieldType  = $MappedField.TypeAsString 
                        If($FieldType -eq "User" -or $FieldType -eq "UserMulti") #People Picker Field
                        {
                            $PeoplePickerValues = $Row.$FieldName.Split(",")
                            $ItemValue.add($FieldName,$PeoplePickerValues)
                        }
                        ElseIf($FieldType -eq "Lookup" -or $FieldType -eq "LookupMulti") #Lookup Field
                        {
                            $LookupIDs = $Row.$FieldName.Split(",") | ForEach-Object { Get-LookupID -ListName $ListName -LookupFieldName $FieldName -LookupValue $_ }                
                            $ItemValue.Add($FieldName,$LookupIDs)
                        }
                        Else
                        {
                            #Get Source Field Value and add to Hashtable
                            $ItemValue.Add($FieldName,$Row.$FieldName)
                        }
                    }
                }
            }
            #Update document properties
            Write-host "Updating Metadata of the File '$($Row.FileName)' at '$($Row.ServerRelativeURL)' with values:"
            $ItemValue | Format-Table
            Set-PnPListItem -List $ListName -Identity $File.Id -Values $ItemValue | Out-Null
        }
        Else
        {
            Write-Host "Could not find File at $($Row.ServerRelativeURL)' , Skipped!" -f Yellow
        }
    }
}
Catch {
    write-host "Error: $($_.Exception.Message)" -foregroundcolor Red
}

Make sure your CSV column headers are matching the internal names (not the display names!) of the metadata columns in your document library. Also, verify the format of the metadata entered in your CSV file once. (E.g. If you supply invalid metadata, then the update would not take place - Obviously!) E.g. For MMS fields, the format should be: TermGroup|TermSet|Term.

SharePoint Online: PowerShell to Get All User Profiles

2020-01-26T18:29:00.001+05:30

Requirement: Get All User Profiles in SharePoint Online using PowerShell.

How to Get User Profiles in SharePoint Online?
User Profiles in SharePoint is a central location that stores information about users. It enables My Sites, social features and share profiles across multiple sites in the tenant. To get all user profiles in SharePoint Online:

Login to SharePoint Online Admin >> Click on "More Features" in left navigation >> Click on "Open" button next to "User Profiles".
In User Profiles page, Click on "Manage User Profiles" under People tab. Use Search to get the user profile of the user.

This page helps us to get individual user profiles and properties one by one. Now, Let's see how to get all user profiles in SharePoint Online using PowerShell.

SharePoint Online: PowerShell to Get All User Profiles
Let's get all user profiles in SharePoint Online using PowerShell

#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.UserProfiles.dll"
 
#Variables
$AdminSiteUrl = "https://crescent-admin.sharepoint.com"
$CSVPath = "C:\Temp\UserProfiles.csv"

#Get Credentials
$Cred = Get-Credential

#Connect to AzureAD
Connect-AzureAD -Credential $Cred

#Get All Users from AzureAD
$AllUsers = Get-AzureADUser -All:$True
Write-host "Total Number of User Profiles Found:"$AllUsers.Count

#Setup the context
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($AdminSiteURL)
$Ctx.Credentials = $Credentials

#Collect User data
$PeopleManager = New-Object Microsoft.SharePoint.Client.UserProfiles.PeopleManager($Ctx)
$UserDataCollection = @()

$Counter = 1
ForEach($User in $AllUsers)
{
    #Get User Profile
    $UserProfile = $PeopleManager.GetPropertiesFor(('i:0#.f|membership|'+$User.UserPrincipalName))
    $Ctx.Load($UserProfile)
    $Ctx.ExecuteQuery()

    Write-Progress -Activity "Extracting User Profile Data..." -Status "Getting User Profile $Counter of $($AllUsers.Count)" -PercentComplete (($Counter / $AllUsers.Count)  * 100)
    #Get User Profile Data
    $UserData = New-Object PSObject
    ForEach($Key in $UserProfile.UserProfileProperties.Keys)
    {   
        $UserData | Add-Member NoteProperty $Key($UserProfile.UserProfileProperties[$Key])
    }
    $UserDataCollection += $UserData
    $Counter++
}

#Export Data to CSV File
$UserDataCollection | Export-Csv -Path $CSVPath -NoTypeInformation

This PowerShell gets all user profiles and properties in SharePoint Online.

Here is my other post to get all user profile properties: SharePoint Online: PowerShell to Get User Profile Properties

SharePoint Online: Monitor External Sharing Invitations with Alerts

2020-01-26T01:21:00.010+05:30

Requirement: Monitor SharePoint Online and OneDrive external sharing invitations.

SharePoint Online: Configure External Sharing Alert

When external sharing is enabled, users share files stored in SharePoint or OneDrive for Business site securely with team members, colleagues, external vendors or partners. You can audit the external user invitations in SharePoint Online and OneDrive for Business by using the Set-SPOTenant cmdlet with "BccExternalSharingInvitations" parameter to "$True" and providing a list of email addresses to "BccExternalSharingInvitationsList". By default, this configuration is set to False - meaning BCC for external sharing is disabled. You can turn it ON and configure E-mail address to receive notification when external sharing invitations are sent.

#Parameters
$TenantAdminURL = "https://crescent-admin.sharepoint.com"

#Connect to Admin Center
Connect-SPOService -Url $TenantAdminURL -Credential (Get-Credential)

#enable alert for external user invitation
Set-SPOTenant -BccExternalSharingInvitations $True -BccExternalSharingInvitationsList "Admin@Crescent.com"

This will blind-copy (BCC) the e-mail messages to the E-mail IDs listed in the BccExternalSharingsInvitationList parameter. You can specify either a single email address or multiple addresses by creating a comma separated list with no spaces.

Set-SPOTenant -BccExternalSharingInvitations $True -BccExternalSharingInvitationsList "Admin@Crescent.com,helpdesk@Crescent.com"

As a result, a copy of every external user invitation will be sent to the specified E-mails and remain available for later auditing or review. This can be a PowerShell way of Monitoring External Sharing Invitations if needed.

Disable BCC External Sharing Invitations
To disable the BCC external sharing invitations, just set the flag to False.

Set-SPOTenant -BccExternalSharingInvitations $False

Check Existing Settings using Get-SPOTenant cmdlet

You can check the existing setting for tenant using:

Get-SPOTenant | Select BccExternalSharingInvitations, BccExternalSharingInvitationsList

Monitor External User Sharing using Compliance Center Alerts

External sharing report is a common requirement in organizations. We can either search Audit logs and get a list of all of externally shared resources or create a alert policy to get E-mail notification as and when it happens. To monitor external content sharing using Microsoft 365 compliance center,

Navigate to compliance center alert policies at https://protection.office.com/alertpolicies
Click on New alert policy >> Provide name and other parameters.
Choose the activity as "Shared File Externally" and create the policy.

More information here: https://docs.microsoft.com/en-us/microsoft-365/compliance/alert-policies?view=o365-worldwide

Fix "The sign-in name or password does not match one in the Microsoft account system" Error in SharePoint Online

2020-01-22T15:14:00.001+05:30

Problem: Getting "The sign-in name or password does not match one in the Microsoft account system" Error in SharePoint Online.

Solution:

I've experienced this error in SharePoint Online PowerShell scripts couple of times and this could happen in any of the PowerShell methods like CSOM, SharePoint Online Management Shell, or PnP PowerShell. Here are the possible causes and solutions:

Your user name or password could be wrong (Obviously!). Check if you are able to login using web browser with the user name and password given in the PowerShell script.
Your password may be expired or account may be locked out or disabled.
Your saved password in Windows credentials store could be incorrect or expired (especially when you use PnP PowerShell module Connect-PnPOnline to get credentials from Windows Credentials store)
Your account may be MFA (Multi-factor authentication) enabled! If yes, either MFA must be turned-OFF or you have to follow the methods described in : How to Connect to SharePoint Online with MFA enabled Accounts from PowerShell?
When you are working with multiple tenants, make sure the supplied credentials are matching the given URL. E.g. If you are trying to connect to https://crescent.sharepoint.com, make sure you are providing username@crescent.com (or whatever is appropriate!) but not "username@someotherdomain.com" in SharePoint Online.
Instead of using User name and password you can use App ID & Password: How to Connect to SharePoint Online using AppID and AppSecret in PnP PowerShell?

SharePoint Online: Activate a Feature on All Sites using PowerShell

2020-01-16T13:27:00.006+05:30

Requirement: Enable a SharePoint Online feature for all sites using PowerShell.

PowerShell to Activate a Site Collection Feature for All Sites

We have a requirement to enable a SharePoint Online "Open Documents in Client Applications by Default" feature for all site collections in the tenant. Here is the PowerShell to enable a feature for all site collections.

#Parameters
$TenantAdminURL = "https://Crescent-Admin.SharePoint.com"
#Site Collection feature "Open Documents in Client Applications by Default"
$FeatureId = "8a4b8de2-6fd8-41e9-923c-c7c3c00f8295" 

#Connect to Admin Center
$Cred = Get-Credential
Connect-PnPOnline -Url $TenantAdminURL -Credentials $Cred

#Get All Site collections - Exclude: Seach Center, Mysite Host, App Catalog, Content Type Hub, eDiscovery and Bot Sites
$SitesCollections = Get-PnPTenantSite | Where -Property Template -NotIn ("SRCHCEN#0", "SPSMSITEHOST#0", "APPCATALOG#0", "POINTPUBLISHINGHUB#0", "EDISC#0", "STS#-1")

#Loop through each site collection
ForEach($Site in $SitesCollections)
{
    #Connect to site collection
    Write-host -f Yellow "Trying to Activate the feature on site:"$Site.Url    
    $SiteConn = Connect-PnPOnline -Url $Site.Url -Credentials $Cred

    #Get the Feature
    $Feature = Get-PnPFeature -Scope Site -Identity $FeatureId -Connection $SiteConn

    #Check if feature is activated
    If($Feature.DefinitionId -eq $null)
    {
        #Enable site collection feature
        Enable-PnPFeature -Scope Site -Identity $FeatureId -Force -Connection $SiteConn

        Write-host -f Green "`tFeature Activated Successfully!"
    }
    Else
    {
        Write-host -f Cyan "`tFeature is already active!"
    }
    Disconnect-PnPOnline -Connection $SiteConn
}

Enable a Site Feature in All Sites of a SharePoint Online Site Collection using PowerShell

What if you want to activate a web scoped feature on all sites, including root site and subsites of a given site collection? Say, we want to activate "Publishing " feature on all sites (webs) in a SharePoint Online site collection.

#Parameters
$SiteURL = "https://crescent.sharepoint.com/sites/marketing"
$FeatureId = "94c94ca6-b32f-4da9-a9e3-1f3d343d7ecb" #Web Scoped Publishing Feature

#Function to Activate a Feature in SharePoint Online Web
Function Activate-PnPWebFeature
{ 
    [cmdletbinding()]
    Param(
        [parameter(Mandatory = $true, ValueFromPipeline = $True)] $Web,
        [parameter(Mandatory = $true, ValueFromPipeline = $False)] $FeatureId
    )

    Try {
        Write-host -f Yellow "Trying to Activate Feature on:"$web.Url
        #Get the Feature to activate
        $Feature = Get-PnPFeature -Scope Web -Identity $FeatureId -Web $Web -ErrorAction Stop
 
        #Check if the Feature is Activate
        If($Feature.DefinitionId -eq $null)
        {    
            #Activate feature            
            Enable-PnPFeature -Scope Web -Identity $FeatureId -Force -Web $Web -Verbose -ErrorAction Stop
 
            Write-host -f Green "`tFeature Activated Successfully!"
        }
        Else
        {
            Write-host -f Cyan "`tFeature is already active!"
        }
    }
    Catch {
        write-host "`tError Activating Feature: $($_.Exception.Message)" -foregroundcolor Red
    }
}

#Connect to the site collection
Connect-PnPOnline -Url $SiteURL -UseWebLogin

#Call the Function for Web & all Subwebs
Get-PnPWeb | Activate-PnPWebFeature -FeatureId $FeatureId 
Get-PnPSubWebs -Recurse | ForEach-Object { Activate-PnPWebFeature $_ -FeatureId $FeatureId}

If your web scoped feature has any dependency on site collection features, You must activate the site collection feature first! In our case, We must activate publishing feature at the site collection prior activating that feature in all webs.

PowerShell to Activate Feature On All Sites of the Tenant

How about activating a site feature for all sites in the tenant? The below PowerShell script enables given feature on all sites, including root site and its sub-sites of all site collections in the SharePoint Online tenant.

#Parameters
$TenantAdminURL = "https://Crescent-Admin.SharePoint.com"
$FeatureId = "a7a2793e-67cd-4dc1-9fd0-43f61581207a" #"Following Content" Web Scoped Feature

#Function to Activate a Web Feature in SharePoint Online
Function Activate-PnPWebFeature
{ 
    [cmdletbinding()]
    Param(
        [parameter(Mandatory = $true, ValueFromPipeline = $True)] $Web,
        [parameter(Mandatory = $true, ValueFromPipeline = $False)] $FeatureId
    )

    Try {
        Write-host -f Yellow "Trying to Activate Feature on:"$web.Url
        #Get the Feature to activate
        $Feature = Get-PnPFeature -Scope Web -Identity $FeatureId -Web $Web -Connection $SiteConn -ErrorAction Stop

        #Check if the Feature is Activate
        If($Feature.DefinitionId -eq $null)
        {
            #Activate the feature
            Enable-PnPFeature -Scope Web -Identity $FeatureId -Force -Web $Web -Connection $SiteConn -ErrorAction Stop 
            Write-host -f Green "`tFeature Activated Successfully!"
        }
        Else
        {
            Write-host -f Cyan "`tFeature is already active!"
        }
    }
    Catch {
        write-host "`tError Activating Feature: $($_.Exception.Message)" -foregroundcolor Red
    }
}

#Connect to Admin Center
$Cred = Get-Credential
Connect-PnPOnline -Url $TenantAdminURL -Credentials $Cred

#Get All Site collections - Exclude: Seach Center, Mysite Host, App Catalog, Content Type Hub, eDiscovery and Bot Sites
$SitesCollections = Get-PnPTenantSite | Where -Property Template -NotIn ("SRCHCEN#0", "SPSMSITEHOST#0", "APPCATALOG#0", "POINTPUBLISHINGHUB#0", "EDISC#0", "STS#-1")

#Loop through each site collection
ForEach($Site in $SitesCollections)
{
    #Connect to site collection
    $SiteConn = Connect-PnPOnline -Url $Site.Url -Credentials $Cred

    #Call the Function for Web & all Subwebs
    Get-PnPWeb -Connection $SiteConn | Activate-PnPWebFeature -FeatureId $FeatureId 
    Get-PnPSubWebs -Connection $SiteConn -Recurse | ForEach-Object { Activate-PnPWebFeature $_ -FeatureId $FeatureId}

    Disconnect-PnPOnline -Connection $SiteConn   
}

To enable a feature in SharePoint Online site, use: SharePoint Online: Activate a Feature using PowerShell

You can also enable a SharePoint Online feature in all site collections using PowerShell with SharePoint Online Management Shell and CSOM as in my another article: PowerShell to enable a site collection feature for all sites in SharePoint Online

SharePoint Online: Disable a Feature for All Sites using PowerShell

2020-01-10T23:03:00.009+05:30

Requirement: Disable a feature for all SharePoint Online sites using PowerShell.

PowerShell to Deactivate a Site Feature on All Collections in the Tenant

We have a requirement to disable the SharePoint Online "Open Documents in Client Applications by Default" feature for all sites in the tenant. Here is the PowerShell to disable a feature for all site collections.

#Parameters
$TenantAdminURL = "https://crescent-Admin.sharepoint.com"
#Site Collection feature "Open Documents in Client Applications by Default"
$FeatureId = "8a4b8de2-6fd8-41e9-923c-c7c3c00f8295" 

#Connect to Admin Center
$Cred = Get-Credential
Connect-PnPOnline -Url $TenantAdminURL -Credentials $Cred

#Get All Site collections - Exclude: Seach Center, Mysite Host, App Catalog, Content Type Hub, eDiscovery and Bot Sites
$SitesCollections = Get-PnPTenantSite | Where -Property Template -NotIn ("SRCHCEN#0", "SPSMSITEHOST#0", "APPCATALOG#0", "POINTPUBLISHINGHUB#0", "EDISC#0", "STS#-1")

#Loop through each site collection
ForEach($Site in $SitesCollections)
{
    #Connect to site collection
    Write-host -f Yellow "Trying to Deactivate the feature on site:"$Site.Url    
    $SiteConn = Connect-PnPOnline -Url $Site.Url -Credentials $Cred

    #Get the Feature
    $Feature = Get-PnPFeature -Scope Site -Identity $FeatureId -Connection $SiteConn

    #Check if feature is activated
    If($Feature.DefinitionId -ne $null)
    {
        #Disable site collection feature
        Disable-PnPFeature -Scope Site -Identity $FeatureId -Force -Connection $SiteConn
        Write-host -f Green "`tFeature Deactivated Successfully!"
    }
    Else
    {
        Write-host -f Cyan "`tFeature is not active!"
    }
    Disconnect-PnPOnline -Connection $SiteConn
}

How to Disable a Feature for All Sites in a Site Collection using PowerShell?

Say, we want to disable "Following Content" site feature from all webs of a given site collection:

#Parameters
$SiteURL = "https://crescent.sharepoint.com/sites/marketing"
$FeatureId = "a7a2793e-67cd-4dc1-9fd0-43f61581207a" #"Following Content" Web Scoped Feature

#Function to Deactivate a Feature in SharePoint Online Web
Function Deactivate-PnPWebFeature
{ 
    [cmdletbinding()]
    Param(
        [parameter(Mandatory = $true, ValueFromPipeline = $True)] $Web,
        [parameter(Mandatory = $true, ValueFromPipeline = $False)] $FeatureId
    )

    Try {
        Write-host -f Yellow "Trying to Deactivate Feature on:"$web.Url
        #Get the Feature to Deactivate
        $Feature = Get-PnPFeature -Scope Web -Identity $FeatureId -Web $Web -ErrorAction Stop
 
        #Check if the Feature is Activate
        If($Feature.DefinitionId -ne $null)
        {    
            #Deactivate feature            
            Disable-PnPFeature -Scope Web -Identity $FeatureId -Force -Web $Web -Verbose -ErrorAction Stop
 
            Write-host -f Green "`tFeature Deactivated Successfully!"
        }
        Else
        {
            Write-host -f Cyan "`tFeature is not active!"
        }
    }
    Catch {
        write-host "`tError Activating Feature: $($_.Exception.Message)" -foregroundcolor Red
    }
}

#Connect to the site collection
Connect-PnPOnline -Url $SiteURL -UseWebLogin

#Call the Function for Web & all Subwebs
Get-PnPWeb | Deactivate-PnPWebFeature -FeatureId $FeatureId 
Get-PnPSubWebs -Recurse | ForEach-Object { Deactivate-PnPWebFeature $_ -FeatureId $FeatureId}

SharePoint Online: Deactivate a feature on All Sites using PowerShell

We can deactivate a web scoped feature from all sites and subsites of the tenant with the below PowerShell.

#Parameters
$TenantAdminURL = "https://Crescent-Admin.sharepoint.com"
$FeatureId = "a7a2793e-67cd-4dc1-9fd0-43f61581207a" #"Following Content" Web Scoped Feature

#Function to Deactivate a Web Feature in SharePoint Online
Function Deactivate-PnPWebFeature
{ 
    [cmdletbinding()]
    Param(
        [parameter(Mandatory = $true, ValueFromPipeline = $True)] $Web,
        [parameter(Mandatory = $true, ValueFromPipeline = $False)] $FeatureId
    )

    Try {
        Write-host -f Yellow "Trying to Deactivate Feature on:"$web.Url
        #Get the Feature to Deactivate
        $Feature = Get-PnPFeature -Scope Web -Identity $FeatureId -Web $Web -Connection $SiteConn -ErrorAction Stop

        #Check if the Feature is activated
        If($Feature.DefinitionId -ne $null)
        {
            #Deactivate the feature
            Disable-PnPFeature -Scope Web -Identity $FeatureId -Force -Web $Web -Connection $SiteConn -ErrorAction Stop
            Write-host -f Green "`tFeature Deactivated Successfully!"
        }
        Else
        {
            Write-host -f Cyan "`tFeature is not active!"
        }
    }
    Catch {
        write-host "`tError Deactivating Feature: $($_.Exception.Message)" -foregroundcolor Red
    }
}

#Connect to Admin Center
$Cred = Get-Credential
Connect-PnPOnline -Url $TenantAdminURL -Credentials $Cred

#Get All Site collections - Exclude: Seach Center, Mysite Host, App Catalog, Content Type Hub, eDiscovery and Bot Sites
$SitesCollections = Get-PnPTenantSite | Where -Property Template -NotIn ("SRCHCEN#0", "SPSMSITEHOST#0", "APPCATALOG#0", "POINTPUBLISHINGHUB#0", "EDISC#0", "STS#-1")

#Loop through each site collection
ForEach($Site in $SitesCollections)
{
    #Connect to site collection
    $SiteConn = Connect-PnPOnline -Url $Site.Url -Credentials $Cred

    #Call the Function for Web and all Subwebs
    Get-PnPWeb -Connection $SiteConn | Deactivate-PnPWebFeature -FeatureId $FeatureId 
    Get-PnPSubWebs -Connection $SiteConn -Recurse | ForEach-Object { Deactivate-PnPWebFeature $_ -FeatureId $FeatureId}

    Disconnect-PnPOnline -Connection $SiteConn   
}

Office 365: How to Disable Delve in SharePoint Online?

2020-01-07T14:16:00.001+05:30

Requirement: Disable Delve in SharePoint Online.

How to Disable Delve for All Users in Office 365?
Delve is an Office 365 App which is used to view and organize all related documents in one location. Microsoft Delve uses artificial intelligence and machine learning to display relevant information to you based on what you work on and who you work with from different applications within Microsoft 365, such as SharePoint, OneDrive, and Teams. Say for E.g. To find out what documents someone is working on, you can simply click on the person's name and see which documents they are working on. Although documents shown in Delve are security trimmed (Users will only see a document if they have access to it), some companies fear about their privacy and wants to disable delve for all users.

To disable delve for all users in Office 365, follow these steps:

Login to your SharePoint Admin Center
Click on "Settings" from left navigation >> Click on "classic settings page" in the bottom>> Under Office graph, Select "Don't allow access to the Office Graph"
Click on "OK" button in the bottom of the page to commit your changes.

This disables delve in SharePoint Online for all users for the entire tenant. (Please note, this does not disable Delve feature altogether, but it does not show any documents in Delve!)

How to Disable the Delve User Profile Redirect?
When you click on any user account in SharePoint Online, it takes you to the Delve page of the user (or My Site in SharePoint On-premises). You can stop that redirect by appending "Force=True" parameter to the URL. https://yourdomain.sharepoint.com/_layouts/15/userdisp.aspx?ID=USERID&Force=True

E.g. https://yourdomain.sharepoint.com/_layouts/userdisp.aspx?ID=12&Force=True

This takes you to the simple user information page:

Disable Delve per User in SharePoint Online

We can also disable delve for one user! Users can individually opt out of sharing their documents with Delve and the Office Graph by login to Delve from the Waffle inside Office 365, or by going directly to the URL https://delve.office.com and click on Settings gear >> Click on "Feature settings" and choose "Off" under documents.

To disable delve for some users, just set below highlighted properties in their user profile to null.

You can also use PowerShell to set these user profile properties and disable delve for all users: Just set "DelveFlags" and "OfficeGraphEnabled" values to $null. How to Set User Profile Property in SharePoint Online using PowerShell?

Setting "msExchHideFromAddressLists" attribute in AD and then it synched to SharePoint user profile property "SPS-HideFromAddressLists" - But it would hide the user from both Delve and Search results.

Disable Delve for Documents in SharePoint Online:

Delve absolutely adheres to all permissions set in SharePoint and OneDrive for Business. No one will ever see anything they don’t already have access to. However, If you want to disable Delve for a particular document(s) in a site, you can create a site column called "HideFromDelve" of Boolean type ("Yes/No") and add this column to each library and then set this flag to "Yes" for each document that you don't want to show in Delve. Here is the step by step instruction:

Step 1: Create "HideFromDelve" Site Column

Navigate to the SharePoint Online site that has documents you want to hide them from Delve.
Click on settings gear in the top-right corner and go to Site Settings
In site settings page, Under Web Designer Galleries, click on "Site columns" >> Click on "Create"
Enter the column name as "HideFromDelve" (EXACT the same name), set column type as "Yes/No" Checkbox field.
Set the default value to "No" (Otherwise every document you create within the library will not show up in Delve!)

The next step is to add the site column libraries wherever required.

Step 2: Add the "HideFromDelve" Site Column to document library

Navigate to the library that has documents you want to hide
Go to Library Settings >> Under the list of columns, click the link Add from existing site columns
Scrolls down in the Available site columns list and select the newly created HideFromDelve column and click the add button to move it across. Then click Ok.

After the column is added to your library, the next step is to set that metadata for documents you want to hide from delve

Step 3: Mark the Documents you want to hide from Delve

Navigate back to your library and you can begin marking documents to be hidden from Delve.
Edit document properties and set the "HideFromDelve" column value to "Yes". The quickest way to do this is through the Quick Edit view.

After the documents have been re-indexed by the SharePoint search, they should no longer be visible in Delve (but still searchable through SharePoint search).

Disable delve in SharePoint Document Library or Site

You can also remove either a document library or site both from Delve and SharePoint Online search results. Navigate to the document library >> Advanced Settings, set "Search" to "No". Similarly, In site settings >> Search >> Search and Offline Availability >> Set "Allow this site to appear in search results"to "No".

SharePoint Online: Bulk Delete Folders from a CSV using PowerShell

2020-01-04T02:02:00.033+05:30

Requirement: Bulk Delete Folders in SharePoint Online.

How to Bulk Delete Multiple Folders in SharePoint Online?

To bulk delete folders with its sub-folders and files, we can use a CSV file with PowerShell script.

#Set Parameters
$SiteURL = "https://crescent.sharepoint.com/sites/Projects"
$CSVFilePath = "C:\Users\Thomas\Desktop\BulkDeleteFolders.csv"
  
#Connect to the site
Connect-PnPOnline -Url $SiteURL -UseWebLogin

#Get the data from CSV file
$CSVFile = Import-CSV $CSVFilePath | Sort-Object ParentFolderSiteRelativeURL -Descending
  
#Read CSV file and delete each folder
ForEach($Row in $CSVFile)
{
    Try {
        #Check if folder exists
        $Folder  = Get-PnPFolderItem -FolderSiteRelativeUrl $Row.ParentFolderSiteRelativeURL -ItemName $Row.FolderName #-ErrorAction SilentlyContinue
        If($Folder -ne $Null)
        {
            #Delete the folder        
            Remove-PnPFolder -Name $Row.FolderName -Folder $Row.ParentFolderSiteRelativeURL -Recycle -Force
            Write-host -f Green "Folder '$($Row.FolderName)' Deleted Successfully from $($Row.ParentFolderSiteRelativeURL)"
        }
        Else
        {
            Write-Host "Folder '$($Row.FolderName)' doesn't exists at $($Row.ParentFolderSiteRelativeURL)" -ForegroundColor Yellow
        }
    }
    Catch {
        write-host -f Red "`tError:" $_.Exception.Message
    }
}

Here is my CSV file with list of folder names and the site relative URL of its parent folder.

You can download the CSV to delete folders: CSV to Bulk Delete Folders in SharePoint Online

SharePoint Online: Can't Delete Folder with Files?

The above script absolutely works fine when your site is not placed under any retention hold policies. However you got to delete all files and sub-folders of the folder to avoid "You have to delete all the items in this folder before you can delete this folder." error message.

SharePoint Online: PowerShell script to Bulk Delete Folders from a CSV File

This PowerShell script recursively deletes each folder and its contents (files, sub-folders) from the CSV file in a given site.

#Set Parameters
$SiteURL = "https://crescent.sharepoint.com/sites/Marketing"
$CSVFilePath = "C:\Users\Thomas\Desktop\BulkDeleteFolders.csv"

#Function to delete all Files and sub-folders from a Folder
Function Empty-PnPFolder($Folder)
{
    #Get the site relative path of the Folder
    If($Folder.Context.Web.ServerRelativeUrl -eq "/")
    {
        $FolderSiteRelativeURL = $Folder.ServerRelativeUrl
    }
    Else
    {        
        $FolderSiteRelativeURL = $Folder.ServerRelativeUrl.Replace($Folder.Context.Web.ServerRelativeURL,"")
    }

    #Delete all files in the Folder
    $Files = Get-PnPFolderItem -FolderSiteRelativeUrl $FolderSiteRelativeURL -ItemType File
    ForEach ($File in $Files)
    {
        #Delete File
        Remove-PnPFile -ServerRelativeUrl $File.ServerRelativeURL -Force -Recycle
        Write-Host -f Green ("`tDeleted File: '{0}' at '{1}'" -f $File.Name, $File.ServerRelativeURL)        
    }
 
    #Process all Sub-Folders
    $SubFolders = Get-PnPFolderItem -FolderSiteRelativeUrl $FolderSiteRelativeURL -ItemType Folder
    Foreach($SubFolder in $SubFolders)
    {
        #Exclude "Forms" and Hidden folders
        If(($SubFolder.Name -ne "Forms") -and (-Not($SubFolder.Name.StartsWith("_"))))
        {
            #Call the function recursively
            Empty-PnPFolder -Folder $SubFolder
 
            #Delete the folder
            Remove-PnPFolder -Name $SubFolder.Name -Folder $FolderSiteRelativeURL -Force -Recycle
            Write-Host -f Green ("`tDeleted Folder: '{0}' at '{1}'" -f $SubFolder.Name, $SubFolder.ServerRelativeURL)
        }
    }
}
  
#Connect to the site
Connect-PnPOnline -Url $SiteURL -UseWebLogin

#Get the data from CSV file
$CSVFile = Import-CSV $CSVFilePath | Sort-Object ParentFolderSiteRelativeURL -Descending
  
#Read CSV file and delete each folder
ForEach($Row in $CSVFile)
{
    Try {
        #Check if folder exists
        $Folder  = Get-PnPFolderItem -FolderSiteRelativeUrl $Row.ParentFolderSiteRelativeURL -ItemName $Row.FolderName #-ErrorAction SilentlyContinue
        If($Folder -ne $Null)
        {
            Write-Host -f Yellow "Deleting Folder '$($Row.FolderName)' from $($Row.ParentFolderSiteRelativeURL)..."
            #Empty the Folder
            Empty-PnPFolder $Folder

            #Delete the folder
            Remove-PnPFolder -Name $Row.FolderName -Folder $Row.ParentFolderSiteRelativeURL -Recycle -Force
            Write-host -f Green "`tFolder '$($Row.FolderName)' Deleted Successfully!"
        }
        Else
        {
            Write-Host "Folder '$($Row.FolderName)' doesn't exists at $($Row.ParentFolderSiteRelativeURL)" -ForegroundColor Yellow
        }
    }
    Catch {
        write-host -f Red "`tError:" $_.Exception.Message
    }
}

This script deletes all files, sub-folders first and then deletes folder in SharePoint Online.

To bulk delete files from a CSV file, use: SharePoint Online: Bulk Delete Files from a CSV using PowerShell

SharePoint Online: Create Multiple Lists from a CSV File using PowerShell

2020-01-03T14:07:00.006+05:30

Requirement: Bulk create lists in SharePoint Online from a CSV File.

How to Create Multiple lists from CSV File?

We can use the combination of CSV and PowerShell to create multiple lists in bulk in SharePoint Online. Here is my CSV File:

You can download this CSV here: SharePoint Online CSV Template to Create Multiple Lists

PowerShell to Create Multiple lists from CSV File

Use this PnP PowerShell to create SharePoint lists from csv.

#Set Parameters
$SiteURL = "https://crescent.sharepoint.com/sites/Marketing"
$CSVFilePath = "C:\Documents\ListCreationTemplate.csv"
  
#Connect to the site
Connect-PnPOnline -Url $SiteURL -UseWebLogin

#Get the data from CSV file
$CSVFile = Import-CSV $CSVFilePath
  
#Read CSV file and create List
ForEach($Row in $CSVFile)
{
    Try {
        #Create List
        Write-host -f Yellow "Creating List:"$Row.ListName
        If($Row.OnQuickLaunch -eq "True")
        {
            New-PnPList -Title $Row.ListName -Template $Row.Template -OnQuickLaunch -ErrorAction Stop | Out-Null
        }
        else
        {
            New-PnPList -Title $Row.ListName -Template $Row.Template -ErrorAction Stop | Out-Null
        }
        Write-host -f Green "`tCreated List '$($Row.ListName)'"
    }
    Catch {
        write-host -f Red "`tError:" $_.Exception.Message
    }
}

This CSV file has ListName, Description, Template, OnQuickLaunch columns. You can add any necessary columns as the parameter for creating list.

Tail: How to Get All Values from an Enumerator?

If you want get all values from an enum, use the below PowerShell script. In my case, I had to retrieve all list template types.

#Get All Values from the Enum
Function Get-EnumValues([string]$Enumerator)
{
    $EnumValues = @{}
    [Enum]::GetValues([Type]$Enumerator) | ForEach-Object {
        $EnumValues.add($_, $_.value__)
    }
    $EnumValues
}
Get-EnumValues -Enumerator "Microsoft.SharePoint.Client.ListTemplateType"

You can also use: GetEnumValues() method of the enumerator. E.g.

[Microsoft.SharePoint.Client.ListTemplateType].GetEnumValues()

SharePoint Online: Bulk Delete Files from a CSV using PowerShell

2020-01-02T22:20:00.004+05:30

Requirement: Delete multiple files from SharePoint Online document libraries from a CSV using PowerShell.

How to Delete Multiple Files in SharePoint Online Site from a CSV file?
We have a CSV file with a list of URLs of various files in multiple document libraries in SharePoint Online. I wanted to bulk delete them. Here is my CSV file format: It has "FileName" and "ServerRelativeURL" as columns.

You can download the CSV template at: CSV to Bulk Delete Files in SharePoint Online

PowerShell to Remove Files using a CSV file in SharePoint Online:
This PowerShell script deletes each file from the CSV file in a given site.

#Parameters
$SiteURL = "https://crescent.sharepoint.com/sites/marketing"
$CSVFilePath = "C:\Temp\BulkDeleteFiles.csv"

#Get Credentials to connect
$Cred = Get-Credential

Try {
    #Connect to PnP Online
    Connect-PnPOnline -Url $SiteURL -Credentials $Cred

    #Read from CSV file and delete
    Import-CSV $CSVFilePath | ForEach-Object {
        #Check if File exists
        $File = Get-PnPFile -ServerRelativeUrl $_.ServerRelativeUrl -ErrorAction SilentlyContinue 
        If($File)
        {
            #Delete the File - Send to Recycle bin without prompting
            Remove-PnPFile -ServerRelativeUrl $_.ServerRelativeURL -Recycle -Force
            Write-Host "Deleted File '$($_.FileName)' at $($_.ServerRelativeUrl)" -ForegroundColor Green
        }
        Else
        {
            Write-Host "File '$($_.FileName)' doesn't exists at $($_.ServerRelativeUrl)" -ForegroundColor Yellow
        }
    }
}
catch {
    write-host "Error: $($_.Exception.Message)" -foregroundcolor Red
}

Fix "Import-Module: The specified module was not loaded because no valid module file was found in any module directory" Error

2020-01-02T15:11:00.005+05:30

Problem: When trying to import the PnP PowerShell module, got an error message "Import-Module: The specified module 'SharePointPnPPowerShellOnline' was not loaded because no valid module file was found in any module directory"

Solution:

This is because the PowerShell console or ISE is unable to get the module from given module path's in the environment variable "PSModulePath". To fix the issue, You have to make sure your custom module's path is appended to the PSModulePath.

Step 1: Get the Path of your custom PowerShell Module

Make sure you have the custom module installed already, To get the modules installed, Open PowerShell console and type:

Get-module -ListAvailable

This gets all installed modules in your system along with their installed Path. Get the path of your custom module as highlighted. In my case, the PnP PowerShell module was installed at "C:\Program Files\WindowsPowerShell\Modules".

Step 2:

As far next step, We've to verify if our custom module's path is added in the "PSModulePath" variable.

Go to System Properties (Windows Key + Pause Break) >> Click on Advanced System Settings >> Click on Environment Variables
Pick "PSModulePath" and click on "Edit" button.
Add the the path to this variable. In my case, its "C:\Program Files\WindowsPowerShell\Modules"
Restart PowerShell ISE or PowerShell console.

This should solve the problem.

How to Use a Encrypted Password File in PowerShell Scripts?

2020-01-02T15:08:00.019+05:30

Requirement: Use a encrypted password file in PowerShell scripts.

How to use a Encrypted Password File to Read/Write Credentials in PowerShell?

PowerShell modules like PnP PowerShell offers a mechanism to use Windows credentials store to Save and retrieve user name and password to use it in scripts. However, for other PowerShell modules like SharePoint Online Management Shell, AzureAD, CSOM. etc. we don't have any direct ways to suppress the password prompt, other than storing the password in plain text within the script. In situations like we need to schedule the script in Windows task scheduler, for unattended execution of the script without any user intervention, we can use this method:

Here is how we can store and read encrypted passwords from file in PowerShell scripts.

MFA must be turn-off for the saved credentials to work!

Step 1: Create a encrypted password file to store credentials
Step 2: Read the encrypted password from the file and use it in scripts.

Create a Encrypted Password File

Basically, we need to get the credentials from the user (once!) and store the encrypted password to a file. Here is the PowerShell script to save encrypted password to a file.

#function to Save Credentials to a file
Function Save-Credential([string]$UserName, [string]$KeyPath)
{
    #Create directory for Key file
    If (!(Test-Path $KeyPath)) {        
        Try {
            New-Item -ItemType Directory -Path $KeyPath -ErrorAction STOP | Out-Null
        }
        Catch {
            Throw $_.Exception.Message
        }
    }
    #store password encrypted in file
    $Credential = Get-Credential -Message "Enter the Credentials:" -UserName $UserName
    $Credential.Password | ConvertFrom-SecureString | Out-File "$($KeyPath)\$($Credential.Username).cred" -Force
}

#Get credentials and create a encrypted password file
Save-Credential -UserName "salaudeen@crescent.com" -KeyPath "C:\Scripts"

This creates a file with encrypted credentials on given path.

Get the encrypted password from the File

Once we create the encrypted password file, we can read the file and use the saved credentials in our scripts like:

#function to get credentials from a Saved file
Function Get-SavedCredential([string]$UserName,[string]$KeyPath)
{
    If(Test-Path "$($KeyPath)\$($Username).cred") {
        $SecureString = Get-Content "$($KeyPath)\$($Username).cred" | ConvertTo-SecureString
        $Credential = New-Object System.Management.Automation.PSCredential -ArgumentList $Username, $SecureString
    }
    Else {
        Throw "Unable to locate a credential for $($Username)"
    }
    Return $Credential
}

#Get encrypted password from file
$Cred = Get-SavedCredential -UserName "salaudeen@crescent.com" -KeyPath "C:\Scripts"

#Connect to Azure AD from saved credentials
Connect-AzureAD -Credential $Cred

Alright, here is how we can use this method to connect to SharePoint Online Management Shell:

#Get encrypted password from file
$Cred = Get-SavedCredential -UserName "salaudeen@crescent.com" -KeyPath "C:\Scripts"

#Connect to SharePoint Online PowerShell
Connect-SPOService -URL "https://crescentintranet-admin.sharepoint.com" -Credential $Cred

#Get all Site Collections
Get-SPOSite

Similarly, to connect to SharePoint Online using CSOM PowerShell, use:

Import-Module Microsoft.Online.SharePoint.PowerShell

#Get encrypted password from file
$Cred = Get-SavedCredential -UserName "salaudeen@crescent.com" -KeyPath "C:\Scripts"

#Parameter
$SiteUrl = "https://crescentintranet.sharepoint.com/sites/marketing"
    
#Set up the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)
$Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
 
#Get the Web Object
$Web = $Ctx.web
$Ctx.Load($Web)
$Ctx.ExecuteQuery()
 
#Get the Title of the Web
Write-host $Web.Title

I used it in automated PowerShell scripts that's scheduled in Windows task scheduler. E.g. PowerShell to import custom user profile properties from Azure AD to SharePoint Online user profile store.

Getting Started with Home Site in SharePoint Online

2020-01-01T13:57:00.000+05:30

What is Home Site in SharePoint Online?
Homesite in SharePoint Online is the top landing page for your organization at https://tenant.sharepoint.com/_layouts/15/sharepoint.aspx. The new Set-SPOHomeSite cmdlet sets a modern communication site collection as the SharePoint Online landing page for your organization! So when you click "SharePoint" from the tiles in Office 365 app launcher, you'll be taken into the SharePoint Home branded from your communication site. Please note, It takes only the branding, Logo, navigation and footer from the communication site, but the default SharePoint Home content like News, Sites, Events, etc. stays as is.

Technically you can set any communication site a home site, but making the "root" site of your tenant as the home site makes sense probably.

What's Special About Home Site?

The SharePoint Homesite can be used as a global search center to search among all SharePoint sites organization-wide.
It acts as an enterprise-wide news portal that collates news from all sites. Also, it enables SharePoint mobile app experience for that site.
Enabling home site adds home icon to your SharePoint mobile app. The mobile app gets updated with a home button on the Find tab Search box and that will take you to the organization's new SharePoint Homesite.

How to Set Home Site in SharePoint Online?
Make sure you have the latest version of SharePoint Online PowerShell module installed, Run this PowerShell script to set Homesite in SharePoint Online. Log in as a tenant admin and designate any communication site to be the landing page for your organization, one home site per tenant.

$AdminSiteURL = "https://crescent-admin.sharepoint.com/"
$HomeSiteURL = "https://crescent.sharepoint.com"

#Connect to the Site
Connect-SPOService -URL $AdminSiteURL -Credential (Get-Credential)

#Set Home Site
Set-SPOHomeSite -HomeSiteUrl $HomeSiteURL

Once you run the above script, The Out-of-Box SharePoint Online starter landing page powered by graph intelligence to get news, sites, documents and items saved for later and all personalized itesm available for all users gets replaced with the communication site design. It also adds "My SharePoint" link to your existing communication sites which links to the personal SharePoint start page.

Create a Communication Site in SharePoint Online using PowerShell

2019-12-25T20:56:00.002+05:30

Requirement: Create a communication site in SharePoint Online using PowerShell

How to Create a Communication Site in SharePoint Online?
One of the recent addition to SharePoint Online space is Communication sites! The modern SharePoint Online communication site templates are designed to broadcast information to a broader audience such as to an entire organization or teams to keep them informed and engaged. You can share news, reports, images, documents, organizational events and other information in a visually compelling format that communicates key information into one central place. A communication site is more visual-centric, dynamic sites that look great on the web, on a PC or Mac, on mobile browsers, and in the SharePoint app. Communication sites allow people to create and share timely updates that go beyond email, Anyone can easily create communicate sites and customize within 30 minutes!

Communication sites provide configurable templates for the sites and pages to make your life easy while communicating messages to a large audience. When you create a communication site in SharePoint, you can start with a blank site or choose one of the available site designs, each of which comes with a default set of web parts with preset designs to help users get started, faster.

Create a New Communication Site in SharePoint Online
To create a communication site in SharePoint Online, follow these steps:

Login to New SharePoint Admin Center as a tenant admin or SharePoint Online Administrator.
Click on "Sites" >> "Active Sites" from left navigation >> Click on "Create" >> From the options available, choose "Communication Site " to create a new communication site collection.
Give the site a name, which becomes the URL of the site. You can edit URL as well below it if needed. These sites are created under the /sites managed path by default.
Choose a template for your site - it is just a starting layout, you can change it later anyway. I've selected "Topic" here!
Optionally, under the "Advanced settings", select the language and set the description by adding some text that lets people know the purpose of your site and then click Finish.

SharePoint creates the communication site quickly and will appear on the sites list. Now, we can populate it with our own information. When ready, You can share it with other people by adding the permissions and sharing it with others. Communication sites don't come with an Office 365 group.

You can also use the "SharePoint Home" (https://tenant.sharepoint.com/_layouts/15/sharepoint.aspx) to create communication sites by simply going to: Office 365 App Launcher, click on SharePoint.

Here is where communication site templates fits well:

A typical Intranet-Extranet-department sites.
News Portals, Product or Service oriented sites,
Campaigns, insights, Events, usiness highlights and reviews

Alright, let's create a communication site in SharePoint Online with PowerShell!

Create SharePoint Online communication site with PowerShell

#Define Parameters
$AdminCenterURL = "https://crescent-admin.sharepoint.com/"
$SiteURL = "https://crescent.sharepoint.com/sites/communications"
$SiteTitle = "Communication Site"
$SiteOwner = "Salaudeen@crescent.com"

#Connect to SharePoint Online
Connect-SPOService -Url $AdminCenterURL -Credential (Get-Credential)

#PowerShell to Create new communication site
New-SPOSite -Url $SiteURL -Owner $SiteOwner -Template "SITEPAGEPUBLISHING#0" -StorageQuota 2048 -Title $SiteTitle

Let's add some error handling to the above script and re-write it in PnP PowerShell.

Create a Communication site in SharePoint Online using PnP PowerShell
Here is how to create a communication site using PnP PowerShell

New-PnPSite -Type CommunicationSite -Title Intranet -Url https://crescent.sharepoint.com/sites/Intranet -SiteDesign Showcase

and bit expanded one goes here:

#Define Config Variables
$AdminCenterURL = "https://crescent-Admin.sharepoint.com"
$SiteURL = "https://crescent.sharepoint.com/sites/intranet"
$SiteTitle = "Crescent Intranet Portal"
$SiteOwner = "Salaudeen@crescent.com"
$Template = "SITEPAGEPUBLISHING#0" #Communication Site template
$Timezone = 24 #(GMT+04:00) Abu Dhabi, Muscat
 
#Get Credentials to connect
$Cred = Get-Credential
 
Try
{
    #Connect to Tenant Admin
    Connect-PnPOnline -URL $AdminCenterURL -Credential $Cred
     
    #Check if site exists already
    $Site = Get-PnPTenantSite | Where {$_.Url -eq $SiteURL}
 
    If ($Site -eq $null)
    {
        #sharepoint online pnp powershell to create communication site
        New-PnPTenantSite -Url $SiteURL -Owner $SiteOwner -Title $SiteTitle -Template $Template -TimeZone $TimeZone -RemoveDeletedSite
        write-host "Site Collection $($SiteURL) Created Successfully!" -foregroundcolor Green
    }
    else
    {
        write-host "Site $($SiteURL) exists already!" -foregroundcolor Yellow
    }
}
catch {
    write-host "Error: $($_.Exception.Message)" -foregroundcolor Red
}