Blog

Get Sharepoint list Items count

var xhr = new XMLHttpRequest();
console.clear();
var url = "https://mySiteUrl";
xhr.open('GET', url + "/_api/web/Lists/getbytitle('TheListName')?$select=ItemCount,Title");

xhr.setRequestHeader("Accept", "application/json; odata=verbose");
 
xhr.onload = function () {
	if (xhr.status === 200) {
		var kk = JSON.parse(xhr.responseText);
		console.dir(kk.d.Title + " Item Count : " + kk.d.ItemCount);
	}
	else {
		console.dir(xhr);
		alert('Request failed. Returned status of ' + xhr.status);
	}
};
xhr.send();

In your browser you must be your your site url, push key F12 paste above code in console, en push enter

fr

Power Automate Sharepoint Actions List

Powerautomate Extract Data From Json Array

{
	"fieldsMapping":[
		{
			"sourceFieldName": "Title2",
			"sourceFieldTypeAsString": "Text",
			"targetFieldName": "Title",
			"targetFieldTypeAsString": "Text"
		},
		 {
		  "sourceFieldName": "Title",
		  "sourceFieldTypeAsString": "Text",
		  "targetFieldName": "OtherTexteField",
		  "targetFieldTypeAsString": "Text"
		}
	]
}

items('Appliquer_à_chacun')?['sourceFieldName']

this command above will put sourceFieldName value in CurrentDataName variable

this command above will put sourceFieldName value in CurrentDataName variable

Power Automate goodies

Add a cont JSON to store urls and lists names for example

{
	"sourceUrl": "https://mySite/sites/DevSpFX/LRemy",
	"sourceListeName": "listeSource",
	"sourceListItemEntityTypeReq":"/_api/web/lists/getbytitle('listeSource')/?$select=ListItemEntityTypeFullName",
	"sourceListItemEntityTypeFullName":"null",
	"sourceListQuery":"/_api/Web/Lists/getbytitle('listeSource')/items?select=Title,myUser1,myChoice1,Title2",
	"targetUrl": "https://mySite/sites/DevSpFX/LRemy",
	"targetListItemEntityTypeReq":"/_api/web/lists/getbytitle('listTarget')/?$select=ListItemEntityTypeFullName",
	"targetListItemEntityTypeFullName":"null",
	"targetListeName": "listTarget",
	"targetListeRequest": "/_api/Web/Lists/getbytitle('listTarget')/items",
	"logListRequest": "/_api/Web/Lists/getbytitle('logs')/items",
	"fieldsMapping":[
		{
			"sourceFieldName": "Title2",
			"sourceFieldTypeAsString": "Text",
			"targetFieldName": "Title",
			"targetFieldTypeAsString": "Text"
		},
		 {
      "sourceFieldName": "Title",
      "sourceFieldTypeAsString": "Text",
      "targetFieldName": "OtherTexteField",
      "targetFieldTypeAsString": "Text"
    }
	]
}

Add a JSON analyser action (above json is juste an exemple)

Add your JSON in generate from sample, this will create an object that you can use later

Then add your JSON datas (if you modify JSON structure, you must regenerate the sample)

Use you Const to get items

Powershell CSOM clear list items / delete all items

clear
# Connect-PNPOnline -url $ProdWaveplaceURL -UseWebLogin -ErrorAction Stop
# Disconnect-PnPOnline

$listTitle = "testTaxo"
$list = Get-PnPList -Identity $listTitle -ThrowExceptionIfListNotFound


$ctx = Get-PnPContext
$page = $null
$pageNumber = 0;
$rowLimit = 100
$startDate = Get-Date
$deletedItemsCount = 0;

Do{
    $stringBuilder = New-Object System.Text.StringBuilder
 
    $stringBuilder.Append("<View scope='RecursiveAll'>") | Out-Null
    $stringBuilder.Append("<Query><Where></Where>")| Out-Null
    $stringBuilder.Append("<Orderby><Fieldref ascending='TRUE' name='ID'></Fieldref></Orderby>")| Out-Null
    $stringBuilder.Append("</Query>")| Out-Null
    $stringBuilder.Append("<Viewfields>")| Out-Null
    $stringBuilder.Append("<Fieldref name='ID' />")| Out-Null
    $stringBuilder.Append("</Viewfields>")| Out-Null
    $stringBuilder.Append("<Rowlimit paged='TRUE'>$($rowLimit)</Rowlimit>")| Out-Null
    $stringBuilder.Append("</View>")| Out-Null
    
    $spqQuery = New-Object Microsoft.SharePoint.Client.CamlQuery
    $spqQuery.ViewXml = $stringBuilder.ToString();
    $spqQuery.ListItemCollectionPosition = $page


    $pageNumber ++;

    $spqQuery.ViewXml = $stringBuilder.ToString();
    $itemki=$list.GetItems($spqQuery); 
    $spqQuery.ListItemCollectionPosition = $itemki.ListItemCollectionPosition

    $ctx.Load($itemki)
    $ctx.ExecuteQuery();


    Write-Host "################## PAGE " $($page.PagingInfo) " #########################"
    Write-Host "processing query results. Recs: $($itemki.Count)"

    $Counter = $itemki.Count;
    if($itemki.Count -eq 0){
        exit 0
    }
    do{
        $itemki[$Counter - 1].DeleteObject()

        $Counter--
        $deletedItemsCount++
    }while($Counter -gt 0)
    Invoke-PnPQuery
    Write-Host "deletedItemsCount $($deletedItemsCount)"
    $page = $itemki.ListItemCollectionPosition
    $comp = $endDate - $startDate
    $endDate = Get-Date
    Write-Host "time to delete $($rowLimit) elements : Days '$($comp.Days)' Hours '$($comp.Hours)' Minutes '$($comp.Minutes)' Seconds '$($comp.Seconds)'"
 }   
 Until($page -eq $null) 

$comp = $endDate - $startDate
Write-Host "time to delete : Days '$($comp.Days)' Hours '$($comp.Hours)' Minutes '$($comp.Minutes)' Seconds '$($comp.Seconds)'"

Nintex 2016 Issue CodeTypeReferenceExpression

SharePoint Foundation Workflow Infrastructure Unexpected RunWorkflow:

Microsoft.SharePoint.SPException: CompilerError Line="-1" Column="-1" Text="Type System.CodeDom.CodeBinaryOperatorExpression is not marked as authorized in the application configuration file.

Error saving from workflow export file.: Nintex.Workflow.NWSavingWorkflowException: Erreur lors de la publication du flux de travail  Text="Le type System.CodeDom.CodeTypeReferenceExpression n'est pas indiqué comme étant autorisé dans le fichier de configuration de l'application."

You should add below red line in each web.config form your farm except central admin

<authorizedType Assembly="System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Namespace="System.CodeDom" TypeName="CodeTypeReferenceExpression" Authorized="True" />

<authorizedType Assembly="System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Namespace="System.CodeDom" TypeName="CodeTypeReferenceExpression" Authorized="True" />
     </targetFx>
    </authorizedTypes>
    <authorizedRuleTypes>
      <targetFx version="v4.0">

--------------------------------

Nintex.Workflow.NWException: Erreur lors de la lecture du paramètre 'SMTPServerPort' depuis la base de données de configuration. Vérifiez que vos bases de données Nintex Workflow sont à jour.

Nintex.Workflow.NWException: Error reading 'SMTPServerPort' parameter from configuration database. Make sure your Nintex Workflow databases are up to date.

To solve this issue, i executed this command line in an powershell admin instance

NWAdmin.exe -o UpgradeDatabases "Server=[serverName];Database=[dbName];Trusted_Connection=True;" -isConfig

For update workflow and forms Nintex configuration databases

Export List Fields To Csv With Pnp Powershell

param($url="https://myTestSite", $listTitle="/Lists/aList")
clear

#connect to your site using windows identity manager
Connect-PnPOnline -Url "$($url)"  -Credentials 'myWindowsOrWebIdentity'

$list = Get-PnPList -Identity $listTitle -Includes Fields


function ExctractFieldsNoContentType()
{
    param($targetList)
    $targetList.Title
   
    $toExport = @()
    for($i = 0 ; $i -lt $targetList.Fields.Count ; $i++)
    {
        $field = $targetList.Fields[$i]
        
        #put in an object some field properties
        $aFieldToExport = New-Object -TypeName PSObject -Property @{

        'Title' = $field.Title
        'InternalName' = $field.InternalName
        'TypeAsString' = $field.TypeAsString
        'Hidden' = $field.Hidden
        'Group' = $field.Group
        'Required' = $field.Required
        'Description' = $field.Description

        }
        $toExport += $aFieldToExport
    }
    #export to csv
    $toExport | Export-Csv -Path "$($targetList.Title)_fields.csv" -Encodin:UTF8 -NoTypeInformation -Delimiter ";"

}

ExctractFieldsNoContentType -targetList $list
Disconnect-PnPOnline

manage identities in 

Use Sharepoint REST api to do a caml query

fdi.post = function(request, data, urlWeb, retFunction, expecedcode=200, odata="verbose"){
    var url = _spPageContextInfo.webAbsoluteUrl + "/_api/contextinfo?&select=FormDigestValue";
	var xhr = new XMLHttpRequest();
	xhr.open('POST', url, true);
	xhr.setRequestHeader("Accept", "application/json; odata=nometadata");
	xhr.onload = function (data) {
		if (xhr.status === 200) {
			console.log(url + " : success");
			var d = JSON.parse(xhr.responseText);
            console.dir(d.FormDigestValue);

            var viewXml =  "<View>" +
                    "<Query>" +
                    "<Where></Where>" +
                    "</Query>" +
                    "</View>";
            
            var req = { "query" :{"__metadata": { "type": "SP.CamlQuery" }, "ViewXml": viewXml}};
            var xhrPOST = new XMLHttpRequest();
            var reqUrl = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/GetByTitle('Catalogue Produits')/GetItems";
            debugger;
            xhrPOST.open('POST', reqUrl, true);
            xhrPOST.setRequestHeader("Accept", "application/json; odata=" + odata);
            xhrPOST.setRequestHeader("content-type", "application/json; odata=" + odata);
            xhrPOST.setRequestHeader("X-RequestDigest", d.FormDigestValue);

            console.log("onload");
            xhrPOST.onload = function () {
                
            console.log("onload ok");
                console.dir(JSON.parse(xhrPOST.responseText));
            }
            xhrPOST.send(JSON.stringify(req));
            console.log("sent");
		}
		else {
			console.log("error");
			console.log(xhr.status);
			console.dir(xhr);
			console.dir(xhr.responseText);			
		}
	};
    xhr.send();
};