Integrate Azure Chat Bot with SharePoint

BotWithSP-Featured

In my previous article, I wrote about how can we create an simple Azure chat bot based on one of the default template, Forms Flow, that Azure Bot comes with. That is good to get us started but to make anything useful, we need to extend the bot.

In this article I am going to talk about how can we extend the Azure Chat bot with Forms Flow,  we created last time, and Integrate it with SharePoint. This logic can be used to integrate both with SharePoint on-premise (2013 and 2016) and Online.

Problem Statement

We are going to extend our Azure Chat bot with Forms Flow, we created earlier, to capture issue details from the user, save that information in a SharePoint list and let the user know a tracking ID for future reference.

Initial Setup

Since, we will need additional references to integrate our bot with SharePoint, we need to use Visual Studio. We can either use Visual Studio Code or other versions of Visual Studio. In this case, I used Visual Studio 2017 Community Edition.

Get the Code

First, we need to get the code generated by Azure bot framework, which we’ll use to extend. We can always start from scratch, but I prefer this approach as the template takes care of a lot of background work.

  • Open the Azure Bot with forms flow we created last time in browser
  • Click on settings, scroll down and click on Configure
  • Click on the bot zip file to download the file

BotWithSP-1

As per my experience, you may need to click on the link multiple times before the download starts ūüôĀ

  • Save this to your local machine

BotWithSP-2

  • Extract the zip file and you will see 2 folders and 2 files

BotWithSP-3

  • Let’s leave these there for now. I will get back what to do with these files later in the article.

Setup Visual Studio Project

Now that we have the code extracted from our bot, lets set it up in Visual Studio.

  • Open Visual Studio 2017 and go to File — New — Project
  • Select Node.js from left node and “Blank Azure Node.js Web Application” from the template

BotWithSP-4

When I tried to use the template “From Existing Node.js code”, it failed to deploy.

  • Give it a name and create the project
  • Once the project gets created, it will look something like this, with server.js file open

BotWithSP-5

  • Copy all the files and folders from the extracted code in previous step to the root of this new project

BotWithSP-6

  • Go to Visual Studio project and in the Solution Explorer, click on Show All Files. You will see the newly added folders and files will become visible

BotWithSP-7

  • Right click each of those files and folders (4 in this case) and click on Include in the project

BotWithSP-8

  • Delete server.js file, we don’t need that in our solution

At this point our setup of Visual Studio project is done.

Add References for SharePoint

To start integrating with SharePoint, we need to add some references in visual studio.

  • First, we’ll add a reference of restify. Right click, npm in the solution explorer and select “Install New npm Packages”

BotWithSP-9

  • In the search box, type restify and click on the “Install Package”.¬†The Bot Builder SDK uses restify, a popular framework for building web services, to create the bot’s web server.

BotWithSP-10

  • Once installed, repeat the steps and search “csom-node” and install that as well.¬†The library provides a SharePoint Client Object Model (CSOM) API for Node.js applications.The current version supports SharePoint Online CSOM library (v 16) The remote authentication is performed via Claims-Based Authentication.

BotWithSP-11

Prepare SharePoint

So far so good. We have our visual studio project with the required references added. Lets now see how to connect to SharePoint.

  • To start with, just create a custom list in your SharePoint site. I am using SharePoint online site here, but this should work with on-premise as well.
  • Add 2 additional columns in your list – Description (Multi-line text) and TicketID (single line text).
  • We’ll add Title and description as provided by the user in the chat window and generate the ticket ID and send that to the user as reference

BotWithSP-13

Connect with SharePoint

Now we are ready for some action in code to integrate our bot with SharePoint.

  • Open index.js file and replace the bot initiation with the following code
//Start the bot conversation
var bot = new builder.UniversalBot(connector, [
    function (session) {
        //Start a different dialog to capture the ticket details
        session.beginDialog('ensureTicketDetails', session.userData.TicketDetails);
    },
    function (session, results) {
        //return to this once the ensureTicketDetails dialog completes
        session.userData.TicketDetails = results.response; // Save user data
        session.endDialog('Hello %(name)s!, Your support ticket reference number is %(spticketRef)s!', session.userData.TicketDetails);
    }
]);
  • Add the Dialog for¬†ensureTicketDetails
//Gather problem related information from the user
bot.dialog('ensureTicketDetails', [
    function (session, args) {
        session.userData.TicketDetails = args || {};
        builder.Prompts.text(session, "Hello... What's your name?");
    },
    function (session, results) {
        session.userData.TicketDetails.name = results.response;
        builder.Prompts.text(session, "Hi " + results.response + ", Let me know the title of the problem?");
    },
    function (session, results) {
        session.userData.TicketDetails.title = results.response;
        builder.Prompts.text(session, "Type the problem description and press enter");
    },
    function (session, results) {
        session.userData.TicketDetails.description = results.response;
        //Store the information in SharePoint
        RaiseTicketInSP(session.userData.TicketDetails, function (ticketRef) {
            session.userData.TicketDetails.spticketRef = ticketRef;
            session.endDialogWithResult({ response: session.userData.TicketDetails });
        });
    },
]);

  • Core logic to add the use response to the SharePoint List
//Connet to SharePoint and add the details provided by the user in chat to a list
function RaiseTicketInSP(TicketDetails, fn) {
    var csomapi = require('csom-node');
    //I am using hardcoded credentials here, you should keep these credentials in the Application settings
    var settings = {
        url: "https://o395.sharepoint.com/",
        username: "[email protected]",
        password: "xxxxxxxxxxxxxxx"
    }
    csomapi.setLoaderOptions({ url: settings.url });  //set CSOM library settings

    var authCtx = new AuthenticationContext(settings.url);
    authCtx.acquireTokenForUser(settings.username, settings.password, function (err, data) {

        var ctx = new SP.ClientContext("/");  //set root web
        authCtx.setAuthenticationCookie(ctx);  //authenticate
        //retrieve SP.Web client object
        var web = ctx.get_web();
        ctx.load(web);
        ctx.executeQueryAsync(function () {

            var ctx = web.get_context();
            var list = web.get_lists().getByTitle("Tickets");
            var creationInfo = new SP.ListItemCreationInformation();
            var listItem = list.addItem(creationInfo);
            listItem.set_item('Title', TicketDetails.title);
            listItem.set_item('Description', TicketDetails.description);
            var uuid = guid();
            listItem.set_item('TicketID', uuid);

            listItem.update();
            ctx.load(listItem);
            ctx.executeQueryAsync(function () {
                fn(uuid);
            },
                function (sender, args) {
                    console.log('An error occured: ' + args.get_message());
                });

        },
            function (sender, args) {
                console.log('An error occured: ' + args.get_message());
            });
    });
}

  • Add a helper function to generate a GUID like string, which will be used as the ticket reference number
//Function to generate a guid like string
function guid() {
    function s4() {
        return Math.floor((1 + Math.random()) * 0x10000)
            .toString(16)
            .substring(1);
    }
    return s4() + s4() + '-' + s4() + '-' + s4() + '-' +
        s4() + '-' + s4() + s4() + s4();
}

And that’s it. We are done.

Deploy the bot

Finally deploy the bot in Azure using the publish wizard

BotWithSP-16

  • And it will start saving the user input in SharePoint.

BotWithSP-14

  • You can see the user’s inputs are getting store in the SharePoint list

BotWithSP-15

Once the item gets added in SharePoint list, you can utilize all the features of SharePoint like generating an alert, starting a flow etc.

Enjoy,
Anupam

You may also like

6 comments

  1. I believe chatbots still have ways to go and are still in Generation 1, the exciting future of transactional and conversations commerce with deeper NLP capabilities will be the tipping point. Do read our collection of blogs at Engati, test our platform and provide us feedback at http://www.engati.com – you can also register and build your bot for free in 10 mins and publish it across 8 platforms simultaneously including a web chat widget.

  2. Hi Anupam, Good to see the article How to place the ChatBot queries to SharePoint. is there a way how can we use the ChatBot app in SharePoint Site?

Leave a Reply

Your email address will not be published. Required fields are marked *

five × three =