Autocomplete textbox with JavaScript CSOM

I have searched on the internet about how to create an auto-complete functionality in SharePoint. Of course, jQuery UI was the solution with an Ajax request to REST service. For some reason, I cannot understand it, the examples I have seen are based on synchronous Ajax request. So I simply said no way. I needed something asynchronous to avoid page freeze.

Normally to create an auto-complete is a simple thing.


    jQuery('#txtBox').autocomplete({
        minLength: 3,
        source: function(request, response) {
         // At the and of the async operation call response with obtained results
         }
     });

Asynchronous operation will be placed inside source function, but instead using classic Ajax examples, I will use JavaScript CSOM. It is not better, but I like it more. So is more a personal choice.

For getting data from SharePoint, you can use classical example from MSDN website, but I prefer to reorganize the code a little bit. After all I still have Microsoft Ajax library available so I can put classes in namespaces or I can validate parameters type.

/// <Reference Name="MicrosoftAjax.js" />


Type.registerNamespace('Shp');


Shp.Lists = function () {
    throw 'Cannot instantiate Shp.Lists static class';
}


Shp.Lists.GetItems = function (listName, query, web, success, fail) {
    /// <summary>Get list items based on provided CAML query</summary>
    /// <param name="listName" type="String" optional="false" mayBeNull="false">List name</param>
    /// <param name="query" type="String" optional="false" mayBeNull="false">Query</param>
    /// <param name="web" type="SP.Web" optional="false" mayBeNull="true">Web</param>
    /// <param name="success" type="Function" optional="false" mayBeNull="false">Success callback</param>
    /// <param name="fail" type="Function" optional="true" mayBeNull="false">Fail callback</param>

    var e = Function.validateParameters(arguments, [{ name: 'listName', type: String, mayBeNull: false, optional: false },
                                                   { name: 'query', type: String, mayBeNull: false, optional: false },
                                                   { name: 'web', type: SP.Web, mayBeNull: true, optional: false },
                                                   { name: 'success', type: Function, mayBeNull: false, optional: false },
                                                   { name: 'fail', type: Function, mayBeNull: false, optional: true }], true);
    if (e) throw e;

    var fail = fail || function (error) { alert(error); };
    var ctx = (web === null) ? SP.ClientContext.get_current() : web.get_context();
    var web = (web === null) ? ctx.get_web() : web;


    Shp.Lists._GetItems(listName, query, ctx, web, success, fail);
}

Shp.Lists._GetItems = function (listName, query, ctx, web, success, fail) {

    var oList = web.get_lists().getByTitle(listName);
    var camlQuery = new SP.CamlQuery();
    camlQuery.set_viewXml(query);
    var oListItems = oList.getItems(camlQuery);
    ctx.load(oListItems);
    ctx.executeQueryAsync(function () {
        success(oListItems);
    }, function (sender, args) {
        fail(args.get_message());
    });
}

Shp.Lists.registerClass('Shp.Lists');

“Shp.Lists.GetItems” should be called with the following parameters:

  • List name, as string, not optional and cannot be null.
  • CAML query as string, not optional and cannot be null.
  • Web as SP.Web, not optional but can be null. In this case web associated with the current context is used.
  • Success as function, not optional and cannot be null. It is executed if operation is a success.
  • Fail function, optional and cannot be null. If not specified and operation fails, code will alert the error message.

Now as I created a reusable function for reading list items, everything should be much easier. I just need to call “Shp.Lists.GetItems” with correct parameters inside auto-complete source and, if operation is successfully, to add suggestions based on list items.

jQuery('#txtBox').autocomplete({
    minLength: 3,
    source: function (request, response) {
       
        var term = request.term;
        var query = '<View><Query><Where><Contains><FieldRef Name="Title" /><Value Type="Text">' + term + '</Value></Contains></Where></Query></View>';
        Shp.Lists.GetItems("list name", query, null, function (items) {
            var suggestions = [];
            var listItemEnumerator = items.getEnumerator();
            while (listItemEnumerator.moveNext()) {
                suggestions.push(listItemEnumerator.get_current().get_item('Title'));
            }
            // Add suggestions
            response(suggestions);

        });

    }
});

This was my approach of creating the auto-complete functionality. As I said, is a personal option to use JavaScript CSOM because looks for me more organized and structured. Of course code can be extended and you can even create an Ajax client side control to incorporate this functionality.

Thank you for reading my post!

Advertisements

Send list items to recycle bin with JavaScript CSOM

Delete operations examples provided by Microsoft include how to delete list items using JavaScript CSOM. And everything is fine, with a small exception. “deleteObject” operation is completely deleting items and you cannot restore them from recycle bin. But instead you can use “recycle” method of SP.ListItem object. My article will show how to sent multiple items to recycle bin using JavaScript CSOM.

As usual, I am using Microsoft Ajax to organize my code in namespaces and classes:

/// <Reference Name="MicrosoftAjax.js" />
/// <Reference Path="jquery.js" />
Type.registerNamespace('Shp');

Shp.Lists = function () {
    throw 'Cannot instantiate Shp.Lists static class';
}

Shp.Lists.registerClass('Shp.Lists');

I will have a static class called “Lists” inside “Shp” namespace and with some help from jQuery (I will need it for deferred object) I will add my methods to complete the operations. In my case method will be called “RecycleItems”.

/// <Reference Name="MicrosoftAjax.js" />
/// <Reference Path="jquery.js" />

Type.registerNamespace('Shp');


Shp.Lists = function () {
    throw 'Cannot instantiate Shp.Lists static class';
}

Shp.Lists.RecycleItems = function (listName, listItems, web) {
    ///	 <summary>Recycle items from the list</summary>
    ///  <param name="listName" type="String" optional="false" mayBeNull="false">List name</param>
    ///  <param name="listItems" type="Array" elementType="Number" elementInteger="true" elementMayBeNull="false" optional="false" mayBeNull="false">Array with string representation of list items id</param>
    ///  <param name="web" type="SP.Web" optional="true" mayBeNull="false">Web</param>
    ///  <returns type="jQuery.deffered" />
    var e = Function.validateParameters(arguments, [{ name: 'listName', type: String, optional: false, mayBeNull: false },
                                                   { name: 'listItems', type: Array, elementType: Number, elementInteger: true, elementMayBeNull: false, optional: false, mayBeNull: false },
                                                   { name: 'web', type: SP.Web, optional: true, mayBeNull: false }], true);
    if (e) throw e;

    // Depending if I provided web parameter, I build internal variable to call internal method
    var ctx = (typeof web === 'undefined' || web === null) ? SP.ClientContext.get_current() : web.get_context();
    var web = (typeof web === 'undefined' || web === null) ? ctx.get_web() : web;

    // Call and return internal method result, which is a jQuery.deffered
    return Shp.Lists._DefferedRecycleItems(listName, listItems, ctx, web);

}

Shp.Lists._DefferedRecycleItems = function (listName, listItems, ctx, web) {
    // Implementation will go here
}

Shp.Lists.registerClass('Shp.Lists');

My method is validating parameters types and called an internal method if parameters are in expected format. Last parameter, which is SP.Web, is optional. If you do not provide it, code will use website that is associated with the client context. And entire implementation will go into my internal method.

/// <Reference Name="MicrosoftAjax.js" />
/// <Reference Path="jquery.js" />

Type.registerNamespace('Shp');


Shp.Lists = function () {
    throw 'Cannot instantiate Shp.Lists static class';
}

Shp.Lists.RecycleItems = function (listName, listItems, web) {
    ///	 <summary>Recycle items from the list</summary>
    ///  <param name="listName" type="String" optional="false" mayBeNull="false">List name</param>
    ///  <param name="listItems" type="Array" elementType="Number" elementInteger="true" elementMayBeNull="false" optional="false" mayBeNull="false">Array with string representation of list items id</param>
    ///  <param name="web" type="SP.Web" optional="true" mayBeNull="false">Web</param>
    ///  <returns type="jQuery.deffered" />
    var e = Function.validateParameters(arguments, [{ name: 'listName', type: String, optional: false, mayBeNull: false },
                                                   { name: 'listItems', type: Array, elementType: Number, elementInteger: true, elementMayBeNull: false, optional: false, mayBeNull: false },
                                                   { name: 'web', type: SP.Web, optional: true, mayBeNull: false }], true);
    if (e) throw e;

    // Depending if I provided web parameter, I build internal variable to call internal method
    var ctx = (typeof web === 'undefined' || web === null) ? SP.ClientContext.get_current() : web.get_context();
    var web = (typeof web === 'undefined' || web === null) ? ctx.get_web() : web;

    // Call and return internal method result, which is a jQuery.deffered
    return Shp.Lists._DefferedRecycleItems(listName, listItems, ctx, web);

}

Shp.Lists._DefferedRecycleItems = function (listName, listItems, ctx, web) {

    var deferred = jQuery.Deferred();
    var oList = web.get_lists().getByTitle(listName);
    var recycledItems = [];

    // For each provided list item id we perform recycle method
    for (var i = 0; i < listItems.length; i++) {
        var oListItem = oList.getItemById(listItems[i]);
        var recycleItem = oListItem.recycle();
        recycledItems.push(recycleItem);
    }

    ctx.executeQueryAsync(function () {

        // In case of success, we refine the results into an array of GUID and use it with deferred.resolve
        Array.forEach(recycledItems, function (element, index, array) {
            array[index] = element['m_value']['_m_guidString$p$0'];
        }, null);

        deferred.resolve(recycledItems);

    }, function (sender, args) {

        // In case of fail, we use error message with deferred.reject
        deferred.reject(args.get_message());

    });

    return deferred.promise();

}

Shp.Lists.registerClass('Shp.Lists');

In my development tasks, code above provided an easy way to remove items from a list, being also capable to restore them. Without entering into details, I can show a basic example.

// Array containing ID of the list items to be sent to recycle bin
var deleteIds = ["1", "2", "3", "6"];

// Define delete operation
var deleteOps = Shp.Lists.RecycleItems('List Name', deleteIds);

// In case of fail we show error message
deleteOps.fail(function (error) {
    alert(error)
});

// In case of success, we show  identifier (GUID type) of the new recycle bin item
deleteOps.done(function (listItemsGuid) {
    for(var i = 0; i < listItemsGuid.length; i++)    {
        alert(listItemsGuid[i]);
    }
});

Hoping my code will help you, I will let you know continue your work. And thank you for ready my post. 🙂

Getting list items with C# CSOM in chunks

I am not expert in C#, but because C# CSOM is more more used in client applications, I had to use it. Of course, the first operation a developer is performing with CSOM is to retrieve data. There is nothing complicated in this, but CSOM cannot retrieve thousands and thousands of items at once. Imagine you want to get a list with 50.000 items and you want to populate an Excel sheet with data you obtained. It is simply not possible. But you can write the code in a way to retrieve data in chunks and execute an operation for each successfully data retrieval request.

In my case I created a class called “SharePointOperation”.

    class SharePointOperation : IDisposable
    {
        private ClientContext oContext;
        private Web oWeb;

        public SharePointOperation(string webUrl)
        {
            oContext = new ClientContext(webUrl);
            oWeb = oContext.Web;
        }

        public void Dispose()
        {
            oContext.Dispose();
        }
    }

I pass the web URL to the constructor and upon class initialization context and web are created. I have implemented IDisposable interface to be able to use my instance in a “using” statement.

The method to get list items is a simple one.

        /// <summary>
        /// Get list items
        /// </summary>
        /// <param name="listName"></param>
        /// <param name="camlQuery"></param>
        /// <param name="callback"></param>
        public void GetListItems(string listName, string camlQuery, Action<ListItemCollection> callback)
        {
            ListItemCollectionPosition position = new ListItemCollectionPosition {PagingInfo = ""};
            ListItemCollection oItems;
            List oList = oWeb.Lists.GetByTitle(listName);
 
            do
            {
                CamlQuery oQuery = new CamlQuery { ViewXml = camlQuery };
                oQuery.ListItemCollectionPosition = position;
  
                oItems = oList.GetItems(oQuery);
                oContext.Load(oItems);
                oContext.ExecuteQuery();
                callback(oItems);
                position = oItems.ListItemCollectionPosition;
            } while (position != null);       
      
           
        }

My method accepts 3 parameters: list name, CAML query string and and action which accepts “ListItemCollection” object type as parameter. Every time I get a new chunk, my action is executed.


string caml = "<View><RowLimit>10</RowLimit><Query><Where><Eq><FieldRef Name='Title' /><Value Type='Text'>Title value</Value></Eq></Where>/Query></View>";

 using (SharePointOperation op = new SharePointOperation("https://url"))
{
                op.GetListItems("List name",caml, (ListItemCollection items) =>
                {
                   // Code not extended for  brevity
                    MessageBox.Show("Done");
                });
}

I don’t pretend this code is perfect, but it might give an idea about how to organize your code and make your life easier. I do not think you want to create complicated code each time you have to deal with large lists from C# CSOM. And to be sure my message is clear, see below entire code for my class.

using System;
using Microsoft.SharePoint.Client;

    class SharePointOperation : IDisposable
    {
        private ClientContext oContext;
        private Web oWeb;

        public SharePointOperation(string webUrl)
        {
            oContext = new ClientContext(webUrl);
            oWeb = oContext.Web;
        }

        /// <summary>
        /// Get list items
        /// </summary>
        /// <param name="listName"></param>
        /// <param name="camlQuery"></param>
        /// <param name="callback"></param>
        public void GetListItems(string listName, string camlQuery, Action<ListItemCollection> callback)
        {
            ListItemCollectionPosition position = new ListItemCollectionPosition {PagingInfo = ""};
            ListItemCollection oItems;
            List oList = oWeb.Lists.GetByTitle(listName);
 
            do
            {
                CamlQuery oQuery = new CamlQuery { ViewXml = camlQuery };
                oQuery.ListItemCollectionPosition = position;
  
                oItems = oList.GetItems(oQuery);
                oContext.Load(oItems);
                oContext.ExecuteQuery();
                callback(oItems);
                position = oItems.ListItemCollectionPosition;
            } while (position != null);
         
      
           
        }

        public void Dispose()
        {
            oContext.Dispose();
        }
    }

Adding a custom task in Excel add-in VSTO application

This article is describing how to add a custom task pane in an Excel add-in application. Custom task panes are very powerful and represent the right way to enhance Excel with custom functionality. So in case you want to create input form to collect data from the user, you just need to add a task pane which includes a user control (typically these are used in Windows forms application).

Because I did not want to repeat the code each time I am requested to add a custom task pane, I have add it to a static method.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
using Microsoft.Office.Core;

namespace ExcelApplication
{
    public static class ExcelUtils
    {
        public static Microsoft.Office.Tools.CustomTaskPane AddCustomTaskPane(System.Windows.Forms.UserControl userControl, string taskPaneTitle)
        {
            Microsoft.Office.Tools.CustomTaskPane pane = Globals.ThisAddIn.CustomTaskPanes.Cast<Microsoft.Office.Tools.CustomTaskPane>().FirstOrDefault(taskPane => taskPane.Title == "Forecast Report") ??
                                                         Globals.ThisAddIn.CustomTaskPanes.Add(userControl, taskPaneTitle) as Microsoft.Office.Tools.CustomTaskPane;
            return pane;
        }
    }
}

Each time I call this method, a custom task pane is added to the application with specified title. In case a custom task pane having specified title already exists, method is returning existing one. Of course in this case it doesn’t matter what user control you have specified and you should pay attention not to create a conflict.
Let’s see how we can use this in a concrete case.

           UserControl forecastReport = new UserControl();
           Microsoft.Office.Tools.CustomTaskPane pane = ExcelUtils.AddCustomTaskPane(forecastReport, "Forecast Report");

           pane.VisibleChanged += ((object s, EventArgs ee) =>
           {
               if (pane.Visible == false)
               {
                   Globals.ThisAddIn.CustomTaskPanes.Remove(pane);
               }
           });
           pane.Visible = true;

So, not only I just showed how to use my static method. I also showed also how to release resources when user hides the pane. But to be honest, if you want to even forward, you can adapt the static method a little bit.

        public static Microsoft.Office.Tools.CustomTaskPane AddCustomTaskPane(System.Windows.Forms.UserControl userControl, string taskPaneTitle, bool autoDisposeOnHide = false)
        {
            Microsoft.Office.Tools.CustomTaskPane pane = Globals.ThisAddIn.CustomTaskPanes.Cast<Microsoft.Office.Tools.CustomTaskPane>().FirstOrDefault(taskPane => taskPane.Title == taskPaneTitle) ??
                                                         Globals.ThisAddIn.CustomTaskPanes.Add(userControl, taskPaneTitle) as Microsoft.Office.Tools.CustomTaskPane;

            // Retun pane if auto dipose on close is false
            if (autoDisposeOnHide != true) return pane;

            // If auto dispose is set to true, we continue the code and define handler for visible change event
            EventHandler disposeOnClose = (object sender, EventArgs e) =>
            {
                Microsoft.Office.Tools.CustomTaskPane pn = sender as Microsoft.Office.Tools.CustomTaskPane;
                if (pn.Visible != false) return;
                Globals.ThisAddIn.CustomTaskPanes.Remove(pn);
                pn.Dispose();
            };

            // Bind the event handler to task pane visible change event
            pane.VisibleChanged -= disposeOnClose;
            pane.VisibleChanged += disposeOnClose;
            return pane;
        }

I have introduced a parameter called autoDisposeOnHide. If is set to true, when user hides the task pane, it is disposed and remove from task panes collection.

Happy Coding !!!