Sharepoint Query Search By UTC Date

By fredericdietrich On 09/12/2019

Sharepoint query search by date (UTC date type)

When your field is DateOnly => 12/12/2019, search service will store it like : 2019-12-11T23:00:00.0000000Z

In your regionnal settings, you can see -1

So in your query to retrieve you should substract one hour

                    
                        var aDate = new Date('2019-12-12);
                        var offset = new Date().getTimezoneOffset();
                        console.log("offset : " + offset);//in my case, offset is -60
                        console.dir(aDate);
                        aDate.setMinutes(aDate.getMinutes() + offset);
                        console.dir(aDate);
                        /_api/search/query='MnpDate:aDate.toISOString()';
                        // aDate : 2019-12-11T23:00:00.0000000Z

Sharepoint Search REST

In javascript

Sharepoint REST query Search

By fredericdietrich On 28/06/2020

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

Sharepoint Query Search With REST

By fredericdietrich On 25/11/2019

using REST to query sharepoint

the script to put in a script editor webpart