Blog

$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

roleassignments

_api/lists/getByTitle('myLList')/items(2)/roleassignments/addroleassignment(principalid=15)},roledefid=1073741826)

break role inheritance

_api/lists/getByTitle('@{triggerBody()?['ListTitle']}')/items(@{int(triggerBody()['ItemId'])})/breakroleinheritance(copyRoleAssignments=false,clearSubscopes=true)

get role assigments ids

/_api/Web/RoleDefinitions?$select=Name,Description,Id

Name Description Id

Full ControlHas full control.1073741829

DesignCan view, add, update, delete, approve, and customize.1073741828

EditCan add, edit and delete lists; can view, add, update and delete list items and documents.1073741830

ContributeCan view, add, update, and delete list items and documents.1073741827

ReadCan view pages and list items and download documents.1073741826

Restricted ViewCan view pages, list items, and documents. Documents can be viewed in the browser but not downloaded.1073741832

Limited AccessCan view specific lists, document libraries, list items, folders, or documents when given permissions.1073741825

System.LimitedViewnull1073741924

System.LimitedEditnull1073741925

Power Automate Update ListItem ContentType

With "Send an HTTP request to SharePoint"

Headers : 

{
  "Accept": "application/json; odata=verbose",
  "content-type": "application/json; odata=verbose",
  "X-HTTP-Method": "MERGE",
  "If-Match": "*"
}

SharePoint Copy ListItem / Duplicate ListItem

[string]$url = "https://yourSite.sharepoint.com/sites/test";
[string]$listTitle = "AddHistoryInExcel";
[int]$itemId = 17;
[string]$fieldsToCopy = "Title,WFInstance,ItemId,TargetListId,TargetDocLibPath";

Connect-PnPOnline -Url $url -Interactive

$itemToCopy = Get-PnPListItem -List $listTitle -Id $itemId
$list = Get-PnPList -Identity $listTitle
$ListItemInfo = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation  
$ListItem = $list.AddItem($ListItemInfo)

foreach($field in $fieldsToCopy.Split(","))
{
    $ListItem[$field] = $itemToCopy[$field]
}
$ListItem.Update()
Invoke-PnPQuery
Write-Host "ItemId $($itemId) has been duplicated target id $($ListItem.Id) "

Here i will show you how to get an Excel File from SharePoint dynamicaly

/_api/v2.1/drives?$select=id,webUrl&$filter=webUrl eq 'https://myTest/sites/Site1/doclib1'

body('Send_an_HTTP_request_to_SharePoint')['value'][0]['id']