Retrieve Unique column values in SharePoint List using JavaScript

  • Article

How To retrieve unique column values in SharePoint List using JavaScript   We will be exploring on how to retrieve unique column values from a SharePoint list using REST API calls and JavaScript.

Steps for retrieving unique values

For getting unique values of a column from SharePoint List, We will use special page exists in /_layouts/  i.e 'filter.aspx'. 
Filter.aspx takes 3 query string parameters and returns filtered values in response.

  • List ID: ID of the list in the current site collection
  • View ID: View ID, we will use Default View to get the ID.
  • Field Internal Name: Field internal name for which we need to pull unique values

First step is to get the list ID using REST call

$.ajax({
     url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/GetByTitle('"  + listName + "')?$select=Id",
     method: "GET",
     headers: { "Accept": "application/json; odata=verbose" }
})

Second step is to get Default View ID using REST call

$.ajax({
 url: _spPageContextInfo.siteAbsoluteUrl + "/_api/web/lists/GetByTitle('"  + listName + "')/DefaultView?$select=id",
 method: "GET",
  headers: { "Accept": "application/json; odata=verbose" }
})

Final step is to get filtered items by calling filter.aspx

$.ajax({
       url: _spPageContextInfo.webAbsoluteUrl + "/_layouts/15/filter.aspx?ListId={"  + ListId + "}&FieldInternalName="  + columnName + "&ViewId={"  + viewId + "}&FilterOnly=1&Filter=2",
      method: "GET",
     headers: { "Accept":  "application/json; odata=verbose"  }
})

Overall function call will look as follows:

function GetUniqueValues(listName, fieldInternalName) {
    $.ajax({
        url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/GetByTitle('"  + listName + "')?$select=Id",
        method: "GET",
        headers: { "Accept":  "application/json; odata=verbose"  }
    })
        .then(function (response) {
            var ListId = $(response)[0].d.Id;
            $.ajax({
                url: _spPageContextInfo.siteAbsoluteUrl + "/_api/web/lists/GetByTitle('"  + listName + "')/DefaultView?$select=id",
                method: "GET",
                headers: { "Accept":  "application/json; odata=verbose"  }
            })
                .then(function (response) {
                    var viewId = $(response)[0].d.Id;
                    console.log("ListId :" + ListId);
                    console.log("ViewId (Default View) :" + viewId);
                    $.ajax({
                        url: _spPageContextInfo.webAbsoluteUrl + "/_layouts/15/filter.aspx?ListId={"  + ListId + "}&FieldInternalName="  + fieldInternalName + "&ViewId={" + viewId + "}&FilterOnly=1&Filter=2",
                        method: "GET",
                        headers: { "Accept":  "application/json; odata=verbose"  }
                    })
                        .then(function (response) {
                            var uniqueVals = [];
                            $(response).find('OPTION').each(function (a, b) {
                                if ($(b)[0].value) {
                                    uniqueVals.push($(b)[0].value);
                                }
                            })
                            console.log(uniqueVals);
                        })
                })
        })
}

We will need to pass "List Name" and "Field Internal Name" to the function and it will provide unique values in "uniqueVals" array.