PowerShell Tips

$url = "https://test.sharepoint.com/sites/monSitte"
# Connect-PnPOnline -Url $url -Interactive
Clear-Host
$listTitle = "EarthStation_Authorisations"
$list = Get-PnPList -Identity "Antenna_Model_Ressources" -ThrowExceptionIfListNotFound

$ctx = Get-PnPContext
$page = $null
$pageNumber = 0;
$rowLimit = 3000
$datas = @();

#get first item
$reqFirst = "<View Scope='RecursiveAll'>
<Query>
    <ViewFields>
        <FieldRef Name='ID' />
    </ViewFields>
    <Where>
    </Where>
    <OrderBy>
        <FieldRef Name='ID' Ascending='TRUE' />
    </OrderBy>
</Query>
<RowLimit>1</RowLimit>
</View>"
$spqQuery = New-Object Microsoft.SharePoint.Client.CamlQuery # $req123 #
$spqQuery.ViewXml = $reqFirst #
$itemki = $list.GetItems($spqQuery); 
$ctx.Load($itemki)
$ctx.ExecuteQuery();

$itemId = $itemki[0].ID

# get last item
$reqFirst = "<View Scope='RecursiveAll'>
<Query>
    <ViewFields>
        <FieldRef Name='ID' />
    </ViewFields>
    <Where>
    </Where>
    <OrderBy>
        <FieldRef Name='ID' Ascending='FALSE' />
    </OrderBy>
</Query>
<RowLimit>1</RowLimit>
</View>"
$spqQuery = New-Object Microsoft.SharePoint.Client.CamlQuery # $req123 #
$spqQuery.ViewXml = $reqFirst #
$itemki = $list.GetItems($spqQuery); 
$ctx.Load($itemki)
$ctx.ExecuteQuery();

$max = $itemki[0].ID

$startDate = Get-Date
. ..\common.ps1
Do {
    $stringBuilder = New-Object System.Text.StringBuilder
  
    
  
    $stringBuilder.Append("<View Scope='RecursiveAll'>") | Out-Null
   
    $stringBuilder.Append("<Query><Where><And><And><Geq><FieldRef Name='Modified' /><Value Type='DateTime'>1900-12-01T19:49:00Z</Value></Geq><Gt><FieldRef Name='ID' /><Value Type='Counter'>$($itemId)</Value></Gt></And><Leq><FieldRef Name='ID' /><Value Type='Counter'>$($itemId + $rowLimit)</Value></Leq></And></Where>") | Out-Null
    # $stringBuilder.Append("<Query><Where><And><And><Geq><FieldRef Name='Modified' /><Value Type='DateTime'>1900-12-01T19:49:00Z</Value></Geq><Gt><FieldRef Name='ID' /><Value Type='Counter'>$($itemId)</Value></Gt></And><Leq><FieldRef Name='ID' /><Value Type='Counter'>$($itemId + $rowLimit)</Value></Leq></And></Where>") | Out-Null
    $stringBuilder.Append("<OrderBy><FieldRef Name='ID' Ascending='TRUE' /></OrderBy>") | Out-Null
    $stringBuilder.Append("</Query>") | Out-Null
   
    $stringBuilder.Append("<ViewFields>") | Out-Null
    $stringBuilder.Append("<FieldRef Name='Title' />") | Out-Null
    $stringBuilder.Append("<FieldRef Name='FileLeafRef' />") | Out-Null
    $stringBuilder.Append("<FieldRef Name='FileRef' />") | Out-Null
    $stringBuilder.Append("<FieldRef Name='Modified' />") | Out-Null
    $stringBuilder.Append("<FieldRef Name='Created' />") | Out-Null
    $stringBuilder.Append("<FieldRef Name='ID' />") | Out-Null
    $stringBuilder.Append("<FieldRef Name='Editor' />") | Out-Null
    $stringBuilder.Append("</ViewFields>") | Out-Null
   
    $stringBuilder.Append("<RowLimit Paged='TRUE'>$($rowLimit)</RowLimit>") | Out-Null
    $stringBuilder.Append("</View>") | Out-Null
     
    $req123 = Get-Content -Path ".\req\req1.xml" -Encoding:UTF8
    $spqQuery = New-Object Microsoft.SharePoint.Client.CamlQuery # $req123 #
    $spqQuery.ViewXml = $stringBuilder.ToString();
    $spqQuery.ListItemCollectionPosition = $page


    $pageNumber ++;

    $itemki = $list.GetItems($spqQuery); 
    $spqQuery.ListItemCollectionPosition = $itemki.ListItemCollectionPosition

    try {
        $ctx.Load($itemki)
        $ctx.ExecuteQuery();
    }
    catch {
        <#Do this if a terminating exception happens#>
        Write-Host "$($_)"
        Write-Host "$($_)"
        exit 0
    }


    Write-Host "################## PAGE " $($page.PagingInfo) " #########################"
    Write-Host "processing query results. Recs: $($itemki.Count) itemId $($itemId) max $($max)"
    
    $Counter = 0;
    foreach ($item in $itemki) {
        # Write-Host "$($item["ID"]) title pageNumber '$($pageNumber)' : $($item["Title"])"
        $datas += [PSCustomObject]@{
            "FileLeafRef" = $item["FileLeafRef"]
            "Title"       = $item["Title"]
            "FileRef"     = $item["FileRef"]
            "Modified"    = $item["Modified"]
            "Created"    = $item["Created"]
            "Editor"    = $item["Editor"].Email
        }
        $itemId = $item.ID
    }

    $page = $itemki.ListItemCollectionPosition
    # $itemId += $rowLimit
}   
Until($itemId -ge $max) 
Write-Host "$($listTitle) list.ItemCount $($list.ItemCount)" 
$datas | Export-Csv -Path ".\datas\$($listTitle)_7.csv" -Encoding utf8 -NoTypeInformation -Delimiter ";" -Append
Write-Host "$(AddNiceTimeSpan -start $startDate)"

{
    "siteUrl": "https://tenant.sharepoint.com/sites/fdiSandBox/",
    "targetFolder": "/Shared%20Documents/test",
    "sourceFolder": "C:\\temp\\myFolderr",
    "checkIfFileExists": true,
    "forceOverWrite": false,
    "includeChildFolders": true,
    "copyDates": false,
    "copyAuthor": false,
    "lofFileName": "copyfiles",
    "forbiddenChars": "~,#,%,&,*,{,},\\,:,<,>,?,/,|,",
    "replacementChar": "_"
}

Clear-Host
. .\common.ps1
#get copy configuration
$config = GetConnectionConfiguration -filePath ".\config\configuration.json"
$logFileName = "$($config.lofFileName))_$(Get-Date -Format "yyyyMMddhhmmss").log"
$loadedFolders = @();
$startDate = Get-Date
function CheckAlreadyConnected {
    Param([Parameter (Mandatory = $true)][string]$url)
    $ctx = Get-PnPContext
    if ($null -eq $ctx) {
        Connect-PnPOnline -Url $url -Interactive -ErrorAction Stop
        WriteInfo -Message "connected to $($url)"
    }
    else {
        $web = Get-PnPWeb
        if ($web.Url.Trim().ToLower().trim("/") -ne $url.Trim().ToLower().trim("/")) {
            Disconnect-PnPOnline
            WriteInfo -Message "Disconnect-PnPOnline  $($web.Url)"
            Connect-PnPOnline -Url $url -Interactive -ErrorAction Stop
            WriteInfo -Message "connected to $($url)"
        }
    }
}
CheckAlreadyConnected -url $config.siteUrl
$web = Get-PnPWeb

function copyFileToSp {
    Param([Parameter (Mandatory = $true)][string]$sourceFilePath,
        [Parameter (Mandatory = $true)][string]$destinationPath
        , $created, $modified
    )
    #check target folder exists
    if (-not $loadedFolders.Contains($destinationPath)) {
        $folder = Get-PnPFolder -Url $destinationPath -ErrorAction SilentlyContinue
        if ($null -eq $folder) {
            $datas = $destinationPath.Split("/", [System.StringSplitOptions]::RemoveEmptyEntries)
            $folderName = $datas[$datas.Length - 1];
            $target = $destinationPath.Substring(0, $destinationPath.Length - $folderName.Length - 1)

            Add-PnPFolder -Name $folderName -Folder $target
        }
        $loadedFolders += $destinationPath
    }
    $destination
    $destinationPath
    $Asset = @{}
    $valuesOk = $false
    # modified
    if ($null -ne $modified) {
        $date = Get-Date -Date $modified 
        $Asset.add("Modified", $date.ToString("yyyy-MM-dd HH:mm"))
        $valuesOk = $true;
    }
    # created
    if ($null -ne $created) {
        $date = Get-Date -Date $created 
        $Asset.add("Created", $date.ToString("yyyy-MM-dd HH:mm"))
        $valuesOk = $true;
    }

    if ($valuesOk -eq $true) {    
        $spFile = Add-PnPFile -Path $sourceFilePath -Folder $destinationPath -Values $Asset
    }
    else {        
        $spFile = Add-PnPFile -Path $sourceFilePath -Folder $destinationPath
    }
    $pp = 0;
    $pp ++;
}

if ($config.includeChildFolders) {
    $filesToCopy = Get-ChildItem -LiteralPath $config.sourceFolder -Recurse
}
else {
    $filesToCopy = Get-ChildItem -LiteralPath $config.sourceFolder 
}
$filesToCopy.Length


foreach ($file in $filesToCopy) {
    $file.FullName;
    $file.LastAccessTime
    if ($file.GetType().Name -eq "FileInfo") {
        $filePath = $file.DirectoryName
        $destinationFileName = $filePath.Substring($config.sourceFolder.Length).trim("\").Replace("\", "/")
        $destinationFileName = "$($web.ServerRelativeUrl)/$($config.targetFolder.Trim().Trim("/"))/$($destinationFileName)".Replace("%20", " ").TrimEnd("/")
        Write-Host "dest : '$($destinationFileName)'"
        copyFileToSp -sourceFilePath $file.FullName -destinationPath $destinationFileName -modified $file.LastWriteTime -created $file.CreationTime
    }
}
WriteInfo -Message "nb files / folders added : $($filesToCopy.Length)"
AddNiceTimeSpan -start $startDate