# In order to run script for SharePoint 2019 OnPremise set $SharePointVersion = "2019". $SharePointVersion = "Online" #Possible options: Online, 2019 Set-ExecutionPolicy -Scope CurrentUser -ExecutionPolicy RemoteSigned function Install-PnP-Module{ if($SharePointVersion -eq "Online"){ Write-Host "Checking for PnP.Powershell module..." if (-not (Get-Module -ListAvailable -Name PnP.Powershell)) { Write-Host "Installing PnP.Powershell module..." Install-Module -Name PnP.Powershell -Scope CurrentUser -Repository PSGallery -AllowClobber Write-Host "PnP.Powershell module has been installed" } else{ Write-Host "PnP.Powershell module is installed" } Import-Module -Name PnP.Powershell } else{ Write-Host "Checking for SharePointPnPPowerShell2019 module..." if (-not (Get-Module -ListAvailable -Name SharePointPnPPowerShell2019)) { Write-Host "Installing SharePointPnPPowerShell2019 module..." Install-Module -Name SharePointPnPPowerShell2019 -Scope CurrentUser -Repository PSGallery -AllowClobber Write-Host "SharePointPnPPowerShell2019 module has been installed" } else{ Write-Host "SharePointPnPPowerShell2019 module is installed" } Import-Module -Name SharePointPnPPowerShell2019 } } function Connect-SharePoint-Site{ $SiteURL = Read-Host 'Enter Site URL' # Site URL which contains main list and sub-library Write-Host ("connecting to '"+$SiteURL+"' site") if($SharePointVersion -eq "Online"){ $connection = Connect-PnPOnline -Url $SiteURL -ReturnConnection -Interactive -ForceAuthentication } else{ $cred = Get-Credential -Message "Enter credentials to SharePoint site" $connection = Connect-PnPOnline -Url $SiteURL -ReturnConnection -Credentials $cred } if( !$connection ){ Write-Error -Message "Failed to connect to SharePoint site." -ErrorAction Continue Read-Host -Prompt "Press Enter to quit..." exit 0 } } #------------------------------------------------------------------------------------------------------------------------------------ Install-PnP-Module Write-Host "-------------------------------------------------------------------" -ForegroundColor White Connect-SharePoint-Site $LibraryName = Read-Host "Enter sub-library ID or Name" $lib = Get-PnPList -Identity $LibraryName if( !$lib ){ Write-Error -Message "Cannot load the library." -ErrorAction Continue Read-Host -Prompt "Press Enter to quit..." exit 0 } $LookupFieldName = Read-Host ("Enter Internal name of lookup column from '"+$lib.Title+"' library") # Lookup column in sub-library $lookupfield = Get-PnPField -List $lib.Id -Identity $LookupFieldName if( !$lookupfield ){ Write-Error -Message ("Cannot load find '"+$LookupFieldName+"' lookup column in '"+$lib.Title+"' the library.") -ErrorAction Continue Read-Host -Prompt "Press Enter to quit..." exit 0 } Write-Host ("Loading main folders from '" + $lib.Title + "' library...") $queryMainFolders= " 1 " [Microsoft.SharePoint.Client.ListItem[]]$mainfolders = Get-PnPListItem -List $LibraryName -PageSize 500 -Query $queryMainFolders | Where-Object {$_[$LookupFieldName] -ne $null} $choices = '&Yes', '&No' $decision = $Host.UI.PromptForChoice('Confirmation', "'"+$lib.Title+" library contains "+$mainfolders.Count+" main folders. Are you sure you want to set lookup value for their children recursively?", $choices, 1) if ($decision -eq 1) { Write-Error -Message "The process has been cancelled." -ErrorAction Continue Read-Host -Prompt "Press Enter to quit..." exit 0 } Write-Host ("Loading items from '"+$lib.Title+"' library") [Microsoft.SharePoint.Client.ListItem[]]$allitems = Get-PnPListItem -List $lib.Id -PageSize 500 Write-Host ("Updating items...") for( $i = 0; $i -lt $mainfolders.Count; $i++) { [Microsoft.SharePoint.Client.ListItem]$folder = $mainfolders[$i]; [String]$folderUrl = $folder['FileRef'] Write-Host (""+($i + 1)+"/" +$mainfolders.Count + " - " + $folder['FileLeafRef'] + " (" +$folder[$LookupFieldName].LookupId + ";#"+ $folder[$LookupFieldName].LookupValue + ")" ) [Microsoft.SharePoint.Client.ListItem[]]$files = $allitems | Where-Object { ($_["FileRef"] -like ($folderUrl + "/*") -and $_["FSObjType"] -eq 0 ) } $filescount = $files.Count if($SharePointVersion -eq "Online"){ $batchsize = 100 for( $idx = 0; $idx -lt $filescount / $batchsize; $idx++) { $batch = New-PnPBatch $idxStart = $idx * $batchsize $idxEnd = [math]::Min( ($idx + 1 )*$batchsize - 1, $filescount - 1 ) $files[$idxStart..$idxEnd] | ForEach-Object { Set-PnPListItem -List $lib.Id -Identity $_.Id -Values @{ $LookupFieldName = $folder[$LookupFieldName].LookupId } -UpdateType SystemUpdate -Batch $batch } Invoke-PnPBatch -Batch $batch } } else{ $files | ForEach-Object { Set-PnPListItem -List $lib.Id -Identity $_.Id -Values @{ $LookupFieldName = $folder[$LookupFieldName].LookupId } } } Write-Host ("updated " + $filescount + " items" ) } Write-Host "The process has been finished." Read-Host -Prompt "Press Enter to quit..." exit 0