Basic CSOM caml query

<View>
    <Query>
        <Where>
            <Eq>
                <FieldRef Name='Matricule'/>
                <Value Type='Text'>90136794</Value>
            </Eq>
        </Where>

<OrderBy><FieldRef Name='ID' Ascending='True'/></OrderBy>
    </Query>
    <ViewFields>
        <FieldRef Name='Matricule' />
        <FieldRef Name='Email' />
    </ViewFields>
    <RowLimit>2</RowLimit>
</View>

Query Null Text fields or not

<Where>
    <IsNotNull>
        <FieldRef Name="Famille"/>
    </IsNotNull>
</Where>

<Where>
    <IsNull>
        <FieldRef Name="Famille"/>
    </IsNull>
</Where>

by ID

<Where>
    <Eq>
        <FieldRef Name='ID'/>
        <Value Type='Counter'>645</Value>
    </Eq>
</Where>

Get tasks by itemId (relatedItems in tasks list)

<Where>
    <Eq>
        <FieldRef Name='RelatedItems'/>
        <Value Type='RelatedItems'>""ItemId"":435,</Value>
    </Eq>
</Where>

boolean / Yes/No

<Eq><FieldRef Name='YourFieldName'/><Value Type='Boolean'>1</Value></Eq>

Comparators

<Eq> => equals

<Neq> => not equals

<Lt> => Less than

<Leq> => less or equal

<Gt> greater than

<Geq> greater or equal

<Contains>

<BeginsWith>

Errors

• Sharepoint error	Error in my cases
  One or more field types are not installed properly. Go to the list settings page to delete these fields.	Fields in your query are not in list
  Cannot complete this action. Please try again.	Syntax error in your query

   

Override native css

:global(#spLeftNav) {  
  display: none;
} 
:global(.CanvasZone) {  
  max-width: 100%;
} 

Is member of

import 'core-js/modules/es6.string.includes.js';
import 'core-js/modules/es6.number.is-nan.js';
import 'core-js/es6/array';
import 'es6-map/implement';
import { sp,SPRest } from '@pnp/sp';
import { IWebPartContext } from '@microsoft/sp-webpart-base';
import * as React from 'react';

class PeopleService {
    private _context: IWebPartContext;
    private _localPnPSetup: SPRest;

    public constructor(webPartContext: IWebPartContext) {
        this._context = webPartContext;

        // To limit the payload size, we set odata=nometadata
        // We just need to get list items here
        // We use a local configuration to avoid conflicts with other Web Parts
        this._localPnPSetup = sp.configure({
            headers: {
                Accept: 'application/json; odata=nometadata',
            },
        }, this._context.pageContext.web.absoluteUrl);
    }

    public async isMemberOf(group: string): Promise{
        let groups = await this._localPnPSetup.web.currentUser.groups.get();
        let ret: boolean = false;
        
        groups.map(grp =>{
            if(grp.LoginName == group)
            ret =  true;
        });
        return ret;
    }

    public async isMemberOfanyGroup(groupNames: string[]): Promise{
        let groups = await this._localPnPSetup.web.currentUser.groups.get();
        let ret: boolean = false;
        groups.map(grp =>{
            groupNames.map(name =>{
                console.log("name : '" + name + "' grp : '" + grp.LoginName  + "'");
            if(grp.LoginName == name)
                ret = true;
            });
        });
        return ret;
    }

}
export default PeopleService;

Taxonomy, get tags, add tags

import { IWebPartContext } from '@microsoft/sp-webpart-base';
import { Session, ITermStore, ITermSet, ITermData, ITerm } from '@pnp/sp-taxonomy';

class TaxonomyService {
    private _context: IWebPartContext;
    
    public constructor(webPartContext: IWebPartContext) {
        this._context = webPartContext;
    }

    public async getTags() : Promise{
        
        console.log("Session");
        const taxonomy = new Session(this._context.pageContext.site.absoluteUrl);
        const store: any = taxonomy.termStores.getByName("Taxonomy_Wf8XzHaRobdAERjvvke+Tg==");
        let datas = await store.getTermSetById("c18ff3e6-e4a8-4dcb-85f5-51171f4bbc11").terms.select('Name', 'Id', 'Parent').get();
        
        let ret: string[] = [];
        for(let i = 0 ; i < datas.length ; i++)
            ret.push(datas[i]);

        return datas;
    }
    
    public async addNewTag(tag){
        
        const taxonomy = new Session(this._context.pageContext.site.absoluteUrl);
        const store: any = taxonomy.termStores.getByName("Taxonomy_Wf8XzHaRobdAERjvvke+Tg==");
        let datas = await store.getTermSetById("c18ff3e6-e4a8-4dcb-85f5-51171f4bbc11");
        const term: ITerm & ITermData = await datas.addTerm(tag, 1036, true);
    }
}
export default TaxonomyService;

CSOM delete items by id range

Get items

public ListItemCollection GetItems(string listRelativUrl, string where, string orderBy, string viewFields, int rowlimit = 100)
{
    string xmlView = "";
    try
    {
        List lst = CurrentWeb.GetList(CurrentWeb.ServerRelativeUrl + listRelativUrl);

        StringBuilder vf = new StringBuilder();
        if (!string.IsNullOrEmpty(viewFields))
        {
            vf.Append("<ViewFields>");
            foreach (string fieldName in viewFields.Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries))
            {
                vf.Append($"<FieldRef Name='{fieldName.Trim()}' />");
            }

            vf.Append("</ViewFields>");
        }

        CamlQuery camlQuery = new CamlQuery();
        camlQuery = new CamlQuery();
        Logger.LogInfo($"listRelativUrl {listRelativUrl} : <View><Query>{where}{orderBy}</Query>{vf.ToString()}<RowLimit>{rowlimit}</RowLimit></View>");
        xmlView = $"<View><Query>{where}{orderBy}</Query>{vf.ToString()}<RowLimit>{rowlimit}</RowLimit></View>";
        camlQuery.ViewXml = xmlView;

        ListItemCollection coll = lst.GetItems(camlQuery);
        SPContext.Load(coll);
        SPContext.ExecuteQuery();

        Logger.Log($"coll.Count : '{coll.Count}' : camlQuery.ViewXml {camlQuery.ViewXml}");

        return coll;
    }
    catch (ServerException ex1)
    {
        Logger.LogError($"Error ServerException Common.Sharepoint.SPTools.GetItems url '{_url}' login : '{_login}' " +
            $" listRelativUrl '{listRelativUrl}' where '{where}' orderBy '{orderBy}' rowlimit '{rowlimit}' xmlView : '{xmlView}' Exception '{ex1}'");
        throw;
    }
    catch (Exception ex)
    {
        Logger.LogError($"Error .Common.Sharepoint.SPTools.GetItems url '{_url}' login : '{_login}' " +
            $" listRelativUrl '{listRelativUrl}' where '{where}' orderBy '{orderBy}' rowlimit '{rowlimit}' xmlView : '{xmlView}' Exception '{ex}'");
        throw;
    }
}

delete items

public int DeleteItemByIdRange(string listRelativeUrl, int rowLimit, int from, int to = -1)
{
    int deleted = 0;
    try
    {
        string req = "";
        if (to == -1)
            req = $"<Where><Geq><FieldRef Name='ID'/><Value Type='Counter'>{from}</Value></Geq></Where>";
        else
        {
            if (from > to)
                throw new Exception($"DeleteItemByIdRange from should be < than to listRelativeUrl '{listRelativeUrl}' from '{from}' to '{to}'");
            req = $"<Where><And><Geq><FieldRef Name='ID' /><Value Type='Counter'>{from}</Value></Geq><Leq><FieldRef Name='ID' /><Value Type='Counter'>{to}</Value></Leq></And></Where>";
        }
        ListItemCollection coll = GetItems(listRelativeUrl, req, "", "ID", rowLimit);

        while (coll.Count > 0)
        {
            string id = coll[0].Id.ToString();
            coll[0].DeleteObject();
            this.ExecuteQuery($"DeleteItemByIdRange listRelativeUrl '{listRelativeUrl}' from '{from}' to '{to}' Id = '{id}'");
            deleted++;
        }
        return deleted;
    }
    catch (Exception ex)
    {
        Logger.LogError($"Error Sharepoint.SPTools.DeleteItemByIdRange from id '{from}' to id '{to}' url '{_url}' login : '{_login}'  Exception '{ex}'");
        throw;
    }
}

catch ServerException to get more details on CSOM exceptions

Microsoft.SharePoint.Client.InvalidClientQueryException A type named 'SP.Data' could not be resolved by the model. When a model is available, each type name must resolve to a valid type

get the correct ListItemEntityTypeFullName 

var xhr = new XMLHttpRequest();
var url = _spPageContextInfo.webAbsoluteUrl;
if (url === "undefined") {
    console.log("_spPageContextInfo.webAbsoluteUrl undefined");
    url = "http://siteUrl";
}
//xhr.open('GET', url +"/_api/web/Lists?$select=Id,Title,Hidden,ItemCount");
xhr.open('GET', url + "/_api/lists/getbytitle('Campaigns')?$select=ListItemEntityTypeFullName");
xhr.setRequestHeader("Accept", "application/json; odata=verbose");
xhr.onload = function () {
    if (xhr.status === 200) {
        var kk = JSON.parse(xhr.responseText);
        console.dir(kk);
    }
    else {
        console.dir(xhr);
        alert('Request failed.  Returned status of ' + xhr.status);
    }
};
xhr.send();

Sharepoint update an item with REST no jquery

console.clear();
function UpdateListItem(listName){
    var listItemId=176;
    var itemType = "SP.Data.SurveysListItem";//GetItemTypeForListName(listName);
	//debugger;
    var item = {
        "__metadata": { "type": itemType },
		"LaunchReportingFlag": "0"
    };
	/*
		"Reporting": ""		
	
	*/
    var url = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('" + listName + "')/items("+listItemId+")";
	var requestdigest = document.getElementById("__REQUESTDIGEST");
	var xhr = new XMLHttpRequest();
	xhr.open('POST', url, true);
	xhr.setRequestHeader("Accept", "application/json; odata=verbose");
	xhr.setRequestHeader("X-RequestDigest", requestdigest.value);
	xhr.setRequestHeader("content-type", "application/json; odata=verbose");
	xhr.setRequestHeader("X-HTTP-Method", "MERGE");
	xhr.setRequestHeader("If-Match", "*");
	
	xhr.onload = function () {
		if (xhr.status === 204) {
			console.log("success");
		}
		else {
			console.log("error");
			console.log(xhr.status);
			console.dir(xhr);
			console.dir(xhr.responseText);
			
		}
	};
	xhr.send(JSON.stringify(item));
}
 
UpdateListItem("Campaigns");

getItemTypeForListName

function getItemTypeForListName(listTitle){
	var xhr = new XMLHttpRequest();
	var url = _spPageContextInfo.webAbsoluteUrl;
	if (url === "undefined") {
		console.log("_spPageContextInfo.webAbsoluteUrl undefined");
		url = "http://siteUrl";
	}
	//xhr.open('GET', url +"/_api/web/Lists?$select=Id,Title,Hidden,ItemCount");
	xhr.open('GET', url + "/_api/web/lists/getbytitle('" + listTitle + "')/?$select=ListItemEntityTypeFullName");
	xhr.setRequestHeader("Accept", "application/json; odata=verbose");
	xhr.onload = function () {
		if (xhr.status === 200) {
			var kk = JSON.parse(xhr.responseText);
			console.dir(kk.d.ListItemEntityTypeFullName);
		}
		else {
			console.dir(xhr);
			alert('Request failed.  Returned status of ' + xhr.status);
		}
	};
	xhr.send();
}

use rest api to search items in sharepoint

var fdi = {};
fdi.search = function(url, query, target, selectProperties){
    var req = url + "/_api/search/query?querytext='" + query + "'&selectproperties='"+ selectProperties +"'&sortlist='LastModifiedTime:descending'&rowlimit=500";

    //&rowsperpage=0&rowlimit=5
    var xhr = new XMLHttpRequest();

    fdi.fdilog(req);
    xhr.open('GET', req);
    xhr.setRequestHeader("Accept", "application/json; odata=verbose");
    xhr.onload = function () {
        if (xhr.status === 200) {
            var kk = JSON.parse(xhr.responseText);
            fdi.lastQuery = req;
            fdi.lastResult = kk;

            fdi.fdilog(kk.d);
            //debugger;
            
            fdi.fdilog(kk.d.query.PrimaryQueryResult.RelevantResults.Table.Rows);//,TypeAsString,Title,Hidden,Required,Group
            fdi.fdilog("RowCount : " + kk.d.query.PrimaryQueryResult.RelevantResults.RowCount);
            fdi.fdilog("TotalRows : " + kk.d.query.PrimaryQueryResult.RelevantResults.TotalRows);
           var rows = kk.d.query.PrimaryQueryResult.RelevantResults.Table.Rows.results;
            var datas = [];
            var columns = selectProperties.split(",");
            for(var i = 0 ; i < rows.length ; i++){
                var tar = {};
                tar.rowNum = i;
                for(var j = 0 ; j < columns.length ; j++){
                        var found = false;
                    for(var k = 0 ; k < rows[i].Cells.results.length ; k++){
                        if(rows[i].Cells.results[k].Key == columns[j]){

                            found = true;
                            tar[columns[j]] = rows[i].Cells.results[k].Value;
                        }
                        if(!found)
                            tar[columns[j]] = "";
                    }
                }
                datas.push(tar);
//rows[i].Cells.results[1].Key
            }
            fdi.fdilog(datas);
            fdi.createTable("rowNum," + selectProperties, datas, target, "#EEEEEE");
        }
        else {
            fdi.fdilog(req);
            fdi.fdilog(xhr);
            alert('Request failed.  Returned status of ' + xhr.status);
        }
    };
    xhr.send();//send request
}

use as below

var reqvvv = "ContentTypeId:0x010056313CD55A8D274EB8FAF2CA0B228BCA00AAEACC42FC8BD24797DEF621447F7D00*";
var seletvvv = "Title,Path,RefinableString20,RefinableString23,RefinableString24,RefinableString25,RefinableDecimal02,RefinableDecimal03,RefinableDecimal04,LastModifiedTime,ListItemID";

fdi.search("https://aSite/sites/Dev", reqvvv, "fdiOutPut", seletvvv); 

Display in a table

fdi.createTable = function( columns, datas, target, backGroundColor){
    if(fdi.isNullOrEmpty(target))
        return;
    
    // var targetDiv = document.getElementsByClassName("SPCanvas");
    // targetDiv[0];
    var targetDiv = document.getElementById(target);
    targetDiv.innerHTML = "";
	var tbl = document.createElement('table');
    var att = document.createAttribute("class");
    
    //var thead = document.createElement('thead');
    var tr1 = document.createElement('tr');
    
    var columnArray = columns.split(",");
    var att = null;
    for(var i = 0 ; i < columnArray.length ; i++){
        var th = document.createElement('th');
        fdi.fdidebuglog(columnArray[i]);
		th.innerText = columnArray[i];
        att = document.createAttribute("style");
        att.value = "border:1px solid #000;width:230px";
        th.setAttributeNode(att);
		tr1.appendChild(th);
    }
    tbl.appendChild(tr1);

    for(var j = 0 ; j < datas.length ; j++){
        var tr2 = document.createElement('tr');
        //background-color: #EEEEEE
        if(j % 2 == 0 && typeof(backGroundColor) !== "undefined"){
            att = document.createAttribute("style");
            att.value = "background-color:" + backGroundColor + ";";
            tr2.setAttributeNode(att);
        }
        for(var i = 0 ; i < columnArray.length ; i++){
            var td = document.createElement('td');
            td.innerText = datas[j][columnArray[i]];
            att = document.createAttribute("style");
            att.value = "border:1px solid #000;width:230px";
            td.setAttributeNode(att);
            tr2.appendChild(td);
        }        
        tbl.appendChild(tr2);
    }
    
    targetDiv.appendChild(tbl);
}

full script

SPFX webpart query Taxonomy / Term store with @pnp/sp-taxonomy on ie / internet explorer

Your package.json must contains at least :

"@pnp/polyfill-ie11": "1.0.0", 
"@pnp/sp": "1.2.7", 
"@pnp/sp-clientsvc": "^1.3.9", 
"@pnp/sp-taxonomy": "^1.3.9", 
"@types/es6-promise": "0.0.33"

In your service where you want to query Term store, you should import :

import 'core-js/modules/es6.string.includes.js';
import 'core-js/modules/es6.number.is-nan.js'; 
import 'core-js/es6/array'; 
import 'es6-map/implement'; 
import { Session } from '@pnp/sp-taxonomy'; 

Power plateform

Power automate flow

Functions

Check date is null

if(equals(triggerBody()?['myDate'], null),'[myDate is null]', formatDateTime(triggerBody()?['myDate'], 'dd/MM/yyyy'))

Power apps

Display view on query string value

If ( !IsBlank(Param("taskDisplay")) && Param("taskDisplay")="true"; Navigate(DevTasksView;ScreenTransition.Cover) )

set variable value (separator in english is , in french ;)

Set(CurrentToShow; "loppement") ;; Set(CurrentButton; "DEV")

Addind header like this

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

Replace New line and Tab

replace(replace(variables('VarGlobalScope'), decodeUriComponent('%09'), '    '), decodeUriComponent('%0A'), '<br>')

decodeUriComponent('%09') = TAB '\t'

decodeUriComponent('%0A') = New line '\r\n'