SharePoint List Data Query with JavaScript/REST API


In this post, I am going to share with you my experiences on playing around REST API queries with JavaScript.  SharePoint itself includes REST API in order to allow developers perform Create, Read, Update, and Delete (CRUD) tasks remotely interact with client object models on SharePoint sites, list and libraries. In my case, I used just read method in this demonstration but it easy to understand overall picture with that and also you may learn a few basic CRUD operation with following MS site.

https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/working-with-lists-and-list-items-with-rest#working-with-lists-by-using-rest

Following picture represents SharePoint REST service architecture

https://xxx.yy/_api/site

To make it quick I used random IDE to write code and implement it with script editor add-in that is already deployed within SharePoint, instead of using Visual Studio to create custom solution and deploy it.

To add script editor to your SharePoint page please follow this steps,

Edit Page –> Insert Tab –> Media and Content –> Script Editor –> Add –> Edit Web Part –> Edit Snippet –> Copy all code into there

I planned to create user-friendly SharePoint inventory page that shows SharePoint site collection information like Site Collection URL, Database, Administrators, and Content Database Size. Moreover, be able to fast easily search within list also export to Excel file.

The result will be seem like below,

Please bear in mind that this jQuery REST API demonstration allows you to query only within same domain site collection, in order to work cross-domain query you should use SP.RequestExecuter.js library. Due to the security concerns as all web faced middleware applications SP is also has CORS prevention methods from server side. Therefore, there will be a few additional important action to be able to perform cross-domain requests. For the details please see below URL.

https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/access-sharepoint-data-from-add-ins-using-the-cross-domain-library

Before initiate the code we also should create new list named “inventory” that includes all necessary information related with farm for inventory. I preferred powershell to prepare that environment that allows us make process programmatically. You may use below article to do it.

http://get-itlabs.com/create-items-in-sharepoint-list-programmatically-with-powershell/

As a result source code like below,

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.10.24/js/jquery.dataTables.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/buttons/1.7.0/js/dataTables.buttons.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/buttons/1.7.0/js/buttons.html5.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
 
 
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css">
<link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/datatables/1.10.12/css/dataTables.jqueryui.min.css">
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/1.7.0/css/buttons.dataTables.min.css">
 
<div>
<input type="button" id="btnSubmit1" value="Dev Farm Inventory" />
<input type="button" id="btnSubmit2" value="Cent Farm Inventory" />
<table id="FarmTable" class="display" cellspacing="0" width="100%">
<thead>
<tr>
<th>Number</th>
<th>URL</th>
<th>Database Name</th>
<th>Site Administrators</th>
<th>Size(GB)</th>
</tr>
</thead>
</table>
</div>
 
 
<script>
var table = $('#FarmTable').DataTable();
var hostweburl
var appweburl
//-------- Button 1 ----------///
$("#btnSubmit1").on("click", function () {
var siteUrl = "https://xxyy/zz";
var oDataUrl = siteUrl + "/_api/web/lists/getbytitle('inventory')/items?$select=number,siteurl,contentdb,siteadmins,sitesize";
table.destroy();
$.ajax({
url: oDataUrl,
type: "GET",
dataType: "json",
headers: {
"accept": "application/json;odata=verbose",
"content-type": "application/json;odata=verbose",
"X-RequestDigest": $("#__REQUESTDIGEST").val()
},
success: OnSuccessButton,
error: OnFailure
  });
});
  
//-------- Button 2 ----------///
$("#btnSubmit2").on("click", function () {
var siteUrl = "https://xxyy/xx";
var oDataUrl = siteUrl + "/_api/web/lists/getbytitle('inventory')/items?$select=number,siteurl,contentdb,siteadmins,sitesize";
table.destroy();
$.ajax({
url: oDataUrl,
type: "GET",
cache: false,
dataType: "json",
headers: {
"accept": "application/json;odata=verbose",
"content-type": "application/json;odata=verbose",
"X-RequestDigest": $("#__REQUESTDIGEST").val()
},
success: OnSuccessButton,
error: OnFailure
  });     
});
function OnSuccessButton(data) {
try {
table = $('#FarmTable').DataTable({
"pageLength": 10000,
retrieve: true,
dom: 'Bfrtip',
        buttons: [{
            extend: 'excelHtml5',
            text: 'Export to Excel',
        }],
"aaData": data.d.results,
"aoColumns": [
{
"mData": "number"
},
{
"mData": "siteurl"
},
{
"mData": "contentdb"
},
{
"mData": "siteadmins"
},
{
"mData": "sitesize"
}
]
});
} catch (e) {
alert(e.message);
}
}
function OnFailure(data, errMessage) {
var errormessage =  JSON.stringify( data );
alert("Error Aldin: " + errMessage + errormessage);
 
}
function getQueryStringParameter(paramToRetrieve) {
            var params = document.URL.split("?")[1].split("&amp;");
            var strParams = "";
            for (var i = 0; i < params.length; i = i + 1) {
              var singleParam = params[i].split("=");
              if (singleParam[0] == paramToRetrieve)
                return singleParam[1];
            }
          }
</script>

Hope this works for you. Thanks for reading.
Erdem Ayyildiz

About the Author

Bir cevap yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir