Import excel data to list using PnP SharePoint Online

 

$credentials = Get-Credential -Message “Please Enter SharePoint Online credentials”

$Site="https://test.sharepoint.com/teams/OC-Product/"

Connect-PnPOnline -Url $Site -Credentials $credentials

$CustomerData = Import-CSV "D:\\LegalEntityData.csv"

 

foreach ($Record in $CustomerData){

Write-Host "adding" $Record.'DivisionNumber '

 Add-PnPListItem -List "Legal Entity" -Values @{

"Title"= $Record.'DivisionNumber ';

"DivisionAddress"= $Record.'Address';

"City"= $Record.'City';

"State"= $Record. 'State';

"Zip"= $Record.'Zip';

"BUNumber"= $Record.'Business Unit';

"Area"= $Record.'Area';

"DivisionName"= $Record.'DivisionName'

}

}

PnP script for Site Scope Feature Activation for SPO (online)

 #Config Variable

$SiteURL = "https://test.sharepoint.com/sites/HR-EngagementChamps/"

$FeatureId = "f6924d36-2fa8-4f0b-b16d-06b72890180fa" #Site Scoped Publishing Feature

 

#Connect to PNP Online

Connect-PnPOnline -Url $SiteURL -Credentials (Get-Credential)

 

#get the Feature

$Feature = Get-PnPFeature -Scope Site -Identity $FeatureId

 

#Get the Feature status

If($Feature.DefinitionId -eq $null)

{   

    #Activate the Feature

    Write-host -f Yellow "Activating Feature..."

    Enable-PnPFeature -Scope Site -Identity $FeatureId -Force

 

    Write-host -f Green "Feature Activated Successfully!"

}

Else

{

    Write-host -f Yellow "Feature is already active!"

}

Create Site collection in SharePoint Online using PnP

 

#Get-Command -Module *PnP*

Connect-PnPOnline –Url https://dev-admin.sharepoint.com/ –Credentials (Get-Credential)

New-PnPTenantSite -Title "Legal"-Url https://dev.sharepoint.com/sites/Legal -Owner "admin@test.com" -Lcid 1033 -Template “STS#0” -TimeZone 2 -StorageQuota 1000 

Set version limit for list/lbrari items in sharepoint

 cls

Add-PSSnapin Microsoft.SHarePoint.Powershell -erroraction SilentlyContinue

$SystemLists =@("Pages", "Converted Forms", "Master Page Gallery", "Customized Reports", "Form Templates", "List Template Gallery", "Theme Gallery", "Reporting Templates", "Site Collection Images", "Site Pages", "Solution Gallery", "Style Library", "Web Part Gallery", "wfpub") 

$URLListFile = "C:\setversion.txt"  

$URLList = Get-Content $URLListFile -ErrorAction SilentlyContinue

   Foreach($SiteURL in $URLList) 

        { 

            $SPweb = Get-SPWeb $SiteURL

            

            Write-Host "Site Name: '$($SPweb.Title)' at $($SPweb.URL)" -ForegroundColor Green

        # loop through all lists in web

            foreach ($SPlist in $SPweb.Lists)

              { 

                if($SPlist.EnableVersioning -eq $true)

                    {

                        write-host "Setting versioning Limit for :" $SPlist.title

                        if(($SPlist.BaseType -eq "DocumentLibrary") -and ($SPlist.Hidden -eq $false) -and ($SystemLists -notcontains $List.Title)){

   

                             $SPlist.MajorVersionLimit = 5;                  

                             $SPlist.EnableMinorVersions = $true;

                             $SPlist.MajorWithMinorVersionsLimit = 5; 

                             $SPlist.Update();

                             }

                       }

                             

                             $list = $SPweb.Lists[$SPlist.Title]

                             foreach($spitem in $list.Items)

                             {

                                #Perform a empty update - without creating new version, so that versioning changes will take effect 

               

                                $itemId= $spitem.ID

                                $item = $list.GetItemById($itemId)                

                                $item.SystemUpdate()

                             }

           } 

     }

     $SPweb.Dispose() 

    

Delete Major and Minor versions

 cls

#$w = Get-SPWebApplication -Identity http://qaintranet2013.com/

#$w.GrantAccessToProcessIdentity("domain\admin1")

$webUrl = "http://qaintranet2013.com/sites/test/";

$web = Get-SPWeb $webUrl;

$list = $web.Lists["Pages"]

foreach($item in $list.Items)

{

    $minor = 0

    $major = 0

    $listsToDelete = @()

    write-host $item.Title

    

    foreach($version in $item.Versions)

    {

        if($version.Level -eq "Draft")

        {

          

          

            $minor += 1

            if($version.VersionLabel.Contains(".0"))

            { 

                #do nothing 

            }

            else

            {

                if($minor -gt 5)

                {

                    #$version.Delete();

                    $lstminor +=$version

                }

            }

        }

        else #Assume Level must be Published

        {

            $major += 1

           

           

            if($major -gt 5)

            { 

               $listsToDelete +=$version 

                #$version.Delete();

            }

        }

    }

        

    Write-Host $major "/" $minor

}

Get List/ document Items version size

 cls

Add-PSSnapin Microsoft.SHarePoint.Powershell -erroraction SilentlyContinue

#create a CSV file 

"List URL, List Name, Item Count, Last modified, List Type, List Base Template, Versioning Enabled, Major Versions Limit, Minor Versions Enabled, Minor Versions limit, Force Checkout Enabled " > D:\SPO\ListDetails.csv #Write the Headers in to a text file 

#Write the CSV Header - Tab Separated 

"Site Collection Name `t Site Name`t Library `t File Name `t File URL `t File Type `t Last Modified `t No. of Versions `t Latest Version Size(MB) `t Versions Size(MB) `t Total File Size(MB)" | out-file D:\SPO\VersionSizeReport.csv 

  

#Arry to Skip System Lists and Libraries 

$SystemLists =@("Pages", "Converted Forms", "Master Page Gallery", "Customized Reports", "Form Templates", "List Template Gallery", "Theme Gallery", "Reporting Templates", "Site Collection Images", "Site Pages", "Solution Gallery", "Style Library", "Web Part Gallery", "wfpub") 

   

#Get Last Year's Same day! 

$DateFilter=([DateTime]::Now.AddYears(-1)) 

############################################################################################################

# The following secion iterates through an entire farm. Two closing loops at the end finish out the        #

# For-Each login here                                                                                      #

############################################################################################################

$Farm = Get-SPWebApplication

Foreach ($WebApplicationname in $Farm)

    {

    $WebApplication = Get-SPWebApplication -identity $WebApplicationname

    $WebAppSites = $WebApplication.Sites

    Foreach ($SiteCollectionUrl in $webappsites) 

        {

############################################################################################################

# End farm interative initiation                                                                           #

############################################################################################################

                

        $site = Get-SPSite -identity $SiteCollectionUrl

        If ($site.ReadLocked -eq $false)

        {

        foreach($web in $site.allWebs) 

        { 

            write-host "Scaning Site" $web.title "@" $web.URL 

               foreach($list in $web.lists) 

               { 

                #Get only Document Libraries & Exclude Hidden System libraries 

                if ( ($List.BaseType -eq "DocumentLibrary") -and ($List.Hidden -eq $false) -and($SystemLists -notcontains $List.Title) ) 

                { 

                    foreach ($ListItem  in $List.Items) 

                    { 

          #Consider items with 5+ versions And apply Date Filter 

                        if ($ListItem.Versions.Count -gt 20) 

                        { 

          $versionSize=0 

  

                            #Get the versioning details 

                            foreach ($FileVersion in $ListItem.File.Versions) 

                            { 

                                $versionSize = $versionSize + $FileVersion.Size; 

                            } 

       #To Calculate Total Size(MB) 

       $ToalFileSize= [Math]::Round(((($ListItem.File.Length + $versionSize)/1024)/1024),2) 

         

                            #Convert Size to MB 

                            $VersionSize= [Math]::Round((($versionSize/1024)/1024),2) 

         

       #Get the Size of the current version 

       $CurrentVersionSize= [Math]::Round((($ListItem.File.Length/1024)/1024),2) 

  

                            #Log the data to a CSV file where versioning size > 0MB! 

                            if ($versionSize -gt 0) 

                            { 

                                "$($Site.RootWeb.Title) `t $($Web.Title) `t $($List.Title) `t $($ListItem.Name) `t $($Web.Url)/$($ListItem.Url) `t $($ListItem['File Type'].ToString()) `t $($ListItem['Modified'].ToString())`t $($ListItem.Versions.Count) `t $CurrentVersionSize `t $($versionSize) `t $($ToalFileSize)" | Out-File D:\SPO\VersionSizeReport.csv -Append

                            } 

                        } 

                    } 

                } 

}

}

            } 

  $Web.Dispose()           

        } 

 $Site.Dispose()           

    } 

   

    #Send message to console 

    write-host "Versioning Report Generated Successfully!"

List All WebParts

 #----------------------------------------------------------------------------- 

# Name:             list-all-webparts.ps1  

# Description:      This script will create a list of all web parts in all 

#                   pages in a site collection.

#                     

# Usage:            Run the script passing three paramters: Url, Folder and WP. 

# Url Accepts multiple values (comma separated).

#                   Folder is the destination of the report.

# WP is the name of the web part (optional).

# By:               Riccardo Celesti blog.riccardocelesti.it

#----------------------------------------------------------------------------- 

Param([Parameter(Mandatory=$true)] 

      [String] 

      $Url,

      [Parameter(Mandatory=$true)] 

      [ValidateScript({Test-Path $_ -PathType 'Container'})]

      [String] 

      $folder,

  [Parameter(Mandatory=$false)]

      [String] 

      $WP

) 

if ((gsnp Microsoft.SharePoint.Powershell -EA SilentlyContinue) -eq $null){

    asnp Microsoft.SharePoint.Powershell -EA Stop

}

$filename = "WebPartsReport_" + (Get-Date).ToFileTimeUtc().ToString() + ".csv"

$filenamewp = "WebPartsReport_" + $WP.Replace(" ","-") + "_" + (Get-Date).ToFileTimeUtc().ToString() + ".csv"

$logfile = Join-Path $folder $filename

$logfilewp = Join-Path $folder $filenamewp

$urlArray = $Url.Split(",")

$header = "File Url, Web Part Title, Web Part Type, Visible"

ac $logfile $header

ac $logfilewp $header

$logfilecontrol = $null

foreach ($SPsite in $urlArray){

    Get-SPSite $SPsite| % {

        foreach ($web in $_.AllWebs){

        

            if ([Microsoft.SharePoint.Publishing.PublishingWeb]::IsPublishingWeb($web)){

                $library = [Microsoft.SharePoint.Publishing.PublishingWeb]::GetPublishingWeb($web)

                $pages = $library.PagesList

            

                foreach ($file in $pages.Items){

                    $fileUrl = $web.Url + "/" + $file.File.Url

                    $manager = $file.file.GetLimitedWebPartManager([System.Web.UI.WebControls.Webparts.PersonalizationScope]::Shared);

                    $webparts = $manager.webparts

                    $webparts | %{

                        ac $logfile "$fileUrl, $($_.DisplayTitle), $($_.GetType().ToString()), $($_.IsVisible)"

if ($_.DisplayTitle -match $WP -and -not [string]::IsNullOrEmpty($WP)){

ac $logfilewp "$fileUrl, $($_.DisplayTitle), $($_.GetType().ToString()), $($_.IsVisible)"

                            $logfilecontrol = 1

}

                    }

                }

                $sitepages = [Microsoft.Sharepoint.Utilities.SpUtility]::GetLocalizedString('$Resources:WikiLibDefaultTitle',"core",$web.UICulture.LCID)

                $pages = $null

                $pages = $web.Lists[$sitepages]

                if ($pages -and $pages.ItemCount -gt 0){

                    foreach ($file in $pages.Items) {

                        $fileUrl = $web.Url + "/" + $file.File.Url

                        $manager = $file.file.GetLimitedWebPartManager([System.Web.UI.WebControls.Webparts.PersonalizationScope]::Shared);

                        $webparts = $manager.webparts

                        $webparts | %{

                            ac $logfile "$fileUrl, $($_.DisplayTitle), $($_.GetType().ToString()), $($_.IsVisible)"

if ($_.DisplayTitle -match $WP -and -not [string]::IsNullOrEmpty($WP)){

ac $logfilewp "$fileUrl, $($_.DisplayTitle), $($_.GetType().ToString()), $($_.IsVisible)"

                                $logfilecontrol = 1

}

                        }   

                    }

                }

            } else {

                $sitepages = [Microsoft.Sharepoint.Utilities.SpUtility]::GetLocalizedString('$Resources:WikiLibDefaultTitle',"core",$web.UICulture.LCID)

                $pages = $null

                $pages = $web.Lists[$sitepages]

                if ($pages){

                    foreach ($file in $pages.Items) {

                        $fileUrl = $web.Url + "/" + $file.File.Url

                        $manager = $file.file.GetLimitedWebPartManager([System.Web.UI.WebControls.Webparts.PersonalizationScope]::Shared);

                        $webparts = $manager.webparts

                        $webparts | %{

                            ac $logfile "$fileUrl, $($_.DisplayTitle), $($_.GetType().ToString()), $($_.IsVisible)"

                            

                            if ($_.DisplayTitle -match $WP -and -not [string]::IsNullOrEmpty($WP)){

ac $logfilewp "$fileUrl, $($_.DisplayTitle), $($_.GetType().ToString()), $($_.IsVisible)"

                                $logfilecontrol = 1

}

                        }

                    }

                }

            }

        }

    }

}

notepad.exe $logfile

if ($logfilecontrol -eq 1){

    notepad.exe $logfilewp

}

Get Site Master Pages

 Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

"URL,MasterPage" > "c:\SiteMasterPage.csv" #Write the Headers in to a text file 

#Get All site collections

$SiteCollections = Get-SPWebApplication | Get-SPSite -Limit All

#Loop through all site collections

   foreach($Site in $SiteCollections)

    {

        #Loop through all Sub Sites

       foreach($Web in $Site.AllWebs)

       {

                  write-host "Scanning Site" $web.title "@" $web.URL 

       $web.Url + "," + $Web.CustomMasterUrl  >> c:\SiteMasterPage.csv  #append the data 

                 }

    }

Write-host "Report Generated at c:\SiteMasterPage.csv" -foregroundcolor green 

Detect and Remove inactive AD Accounts

 #Powershell to Remove Orphaned Users from SharePoint 2013/2010

#Load SharePoint Management shell 

if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null) {

    Add-PSSnapin "Microsoft.SharePoint.PowerShell"

}

 

#Function to Check if an User exists in AD

function CheckUserExistsInAD()

   {

   Param( [Parameter(Mandatory=$true)] [string]$UserLoginID )

   

  #Search the User in AD

  $forest = [System.DirectoryServices.ActiveDirectory.Forest]::GetCurrentForest()

  foreach ($Domain in $forest.Domains)

  {

         $context = new-object System.DirectoryServices.ActiveDirectory.DirectoryContext("Domain", $Domain.Name)

         $domain = [System.DirectoryServices.ActiveDirectory.Domain]::GetDomain($context)

     

         $root = $domain.GetDirectoryEntry()

         $search = [System.DirectoryServices.DirectorySearcher]$root

         $search.Filter = "(&(objectCategory=User)(samAccountName=$UserLoginID))"

         $result = $search.FindOne()

  

         if ($result -ne $null)

         {

           return $true

         }

  }

  return $false 

 }

  

 #Change these variables as desired

 #$WebAppURL="http://SharePointWebSite"

 #$RemoveUsers = $false

  

  

  

    

 #Get all Site Collections of the web application

 #$WebApp = Get-SPWebApplication $WebAppURL

$textout = ""

 $textout > "D:\SPO\SharePointOrphanedUsers.csv"

$Farm = Get-SPWebApplication

Foreach ($WebApplicationname in $Farm)

    {

    $WebApp = Get-SPWebApplication -identity $WebApplicationname

    $WebAppSites = $WebApp.Sites

  

 #Iterate through all Site Collections

 foreach($site in $WebApp.Sites)  

 {

    if ($site -ne $null)

    {

        $web = $site.AllWebs

        foreach ($webp in $web)

        {

            $OrphanedUsers = @()

            if (($webp.permissions -ne $null) -and ($webp.hasuniqueroleassignments -eq "True"))

            {

                #Iterate through the users collection

                foreach($User in $webp.SiteUsers)

                {

                    #Exclude Built-in User Accounts , Security Groups & an external domain "corporate"

                    if(($User.LoginName.ToLower() -ne "nt authority\authenticated users") -and

                            ($User.LoginName.ToLower() -ne "nt authority\system") -and

                                ($User.LoginName.ToLower() -ne "sharepoint\system") -and

                                  ($User.LoginName.ToLower() -ne "nt authority\local service")  -and

                                      ($user.IsDomainGroup -eq $false ) -and

                                          ($User.LoginName.ToLower().StartsWith("corporate") -ne $true) )

                    {

 

                        $UserName = $User.LoginName.split("\")  #Domain\UserName

                        $AccountName = $UserName[1]    #UserName

                         

                        #If the user does not exist in Active Directory then it is an orphaned account in the SharePoint Site Collection

                        if ( ( CheckUserExistsInAD $AccountName) -eq $false )

                        {

                                $textout = """$($User.Name)"",""$AccountName"",""($($User.LoginName))"",""$($webp.URL)"""

                                #Write-Host $textout

                                $textout >> "D:\SPO\SharePointOrphanedUsers.csv"

                                #Make a note of the Orphaned user

                                $OrphanedUsers+=$User.LoginName

                                 

                                 

                        }

                         

                    }

                }

                # ****  Remove Users ****#

                # Remove the Orphaned Users from the site

              #  if ($RemoveUsers)

                #{

                   # foreach($OrpUser in $OrphanedUsers)

                    #{

                       # $webp.SiteUsers.Remove($OrpUser)

                       # Write-host "Removed the Orphaned user $($OrpUser) from $($webp.URL) "

                    #}

               # }

 

            }

 

        }        

                  

    }

}

    }

Get Check-Out files by Web Application Level

 ##########################################################################################################################  

######## V 1.0  

######## PowerShell Script to get a list of checked out files in your SharePoint Environment  

################################################################################################################################  

 

#check to see if the PowerShell Snapin is added  

if((Get-PSSnapin | Where {$_.Name -eq "Microsoft.SharePoint.PowerShell"}) -eq $null) {  

    Add-PSSnapin Microsoft.SharePoint.PowerShell;  

}  

 

## SharePoint DLL   

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")   

$global:currentPhysicalPath = Split-Path ((Get-Variable MyInvocation -Scope 0).Value).MyCommand.Path  

   

Function Get-SPWebApplication()  

{    

  Param( [Parameter(Mandatory=$true)] [string]$WebAppURL )  

  return [Microsoft.SharePoint.Administration.SPWebApplication]::Lookup($WebAppURL)  

}  

   

Function global:Get-SPSite()  

{  

  Param( [Parameter(Mandatory=$true)] [string]$SiteCollURL )  

   

   if($SiteCollURL -ne '')  

    {  

        return new-Object Microsoft.SharePoint.SPSite($SiteCollURL)  

    }  

}  

    

Function global:Get-SPWeb()  

{  

    Param( [Parameter(Mandatory=$true)] [string]$SiteURL )  

    $site = Get-SPSite($SiteURL)  

    if($site -ne $null)  

    {  

        $web=$site.OpenWeb();  

    }  

   return $web  

}  

#EndRegion  

   

 Function GetCheckedOutFiles([string]$WebAppURL)  

 {   

    try  

    {  

        $results = @()  

         

        #Get the Web Application  

        $WebApp=Get-SPWebApplication($WebAppURL)  

  

        #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")  

   

        #Loop through each site collection  

        foreach($Site in $WebApp.Sites)  

        {  

            #Loop through each site in the site collection  

            foreach($Web in $Site.AllWebs)  

            {  

                #Loop through each document library  

                foreach ($List in $Web.GetListsOfType([Microsoft.SharePoint.SPBaseType]::DocumentLibrary))  

                {  

                    #Get only Document Libraries & Exclude Hidden System libraries  

                    if (($List.Hidden -eq $false) -and ($SystemLists -notcontains $List.Title) )  

                    {  

                        #Loop through eadh Item  

                        foreach ($ListItem in $List.Items)  

                        {  

                            if( ($ListItem.File.CheckOutStatus -ne "None") -and ($ListItem.File.CheckedOutByUser -ne $null))  

                            {  

                                $sitecollectionUrl =  "<SiteCollection relativeURL=" + $Site.RootWeb.ServerRelativeURL + "></SiteCollection>"  

                                #Create an object to hold storage data  

                                $resultsData = New-Object PSObject  

                                $resultsData | Add-Member -type NoteProperty -name "SiteCollection Title" -value $Site.RootWeb.Title -Force    

                                $resultsData | Add-Member -type NoteProperty -name "SiteCollection URL" -value $sitecollectionUrl -Force              

                                $resultsData | Add-Member -type NoteProperty -name "Web Title" -value $Web.Title -Force  

                                $resultsData | Add-Member -type NoteProperty -name "Web URL" -value $Web.url -Force  

                                $resultsData | Add-Member -type NoteProperty -name "Library Name" -value $List.Title -Force  

                                $resultsData | Add-Member -type NoteProperty -name "File Name" -value $ListItem.Name -Force  

                                $resultsData | Add-Member -type NoteProperty -name "File URL" -value $Web.Site.MakeFullUrl(“$($Web.ServerRelativeUrl.TrimEnd(‘/’))/$($ListItem.Url)”)  -Force  

                                $resultsData | Add-Member -type NoteProperty -name "Last Modified" -value $ListItem['Modified'].ToString() -Force  

                                $resultsData | Add-Member -type NoteProperty -name "Checked-Out By" -value $ListItem.File.CheckedOutByUser -Force  

                                $resultsData | Add-Member -type NoteProperty -name "Checked-Out By User Email" -value $ListItem.File.CheckedOutBy.Email -Force  

                                $resultsData | Add-Member -type NoteProperty -name "Primary Administrator" -value $Site.Owner -Force  

                                $resultsData | Add-Member -type NoteProperty -name "Primary Administrator Email" -value $Site.Owner.Email -Force  

                                $resultsData | Add-Member -type NoteProperty -name "Secondary Administrator" -value $Site.SecondaryContact -Force  

                                $resultsData | Add-Member -type NoteProperty -name "Secondary Administrator Email" -value $Site.SecondaryContact.Email -Force  

                                $results += $resultsData   

                            }  

                        }  

                    }  

                }  

                $Web.Dispose()           

            }  

            $Site.Dispose()           

        }  

        $results | export-csv -Path D:\SPO\ListAllCheckedOutFiles.csv -notypeinformation -Force  

         

        #Send message to output console  

        write-host "Checked out Files Report Generated Successfully!"  

    }  

    catch [System.Exception]   

    {   

        write-host -f red $_.Exception.ToString()   

    }   

}  

 

# Function Call  

$WebApp = Read-Host "Enter the web application URL to work on:"  

GetCheckedOutFiles $WebApp