Post
Ask Question

Accessing/Updating SharePoint Online Data In Azure Chat Bot Framework With NodeJS

In part 1, we have seen how to create an Azure bot using FormFlow Template in NodeJS. In this article, we will learn how to connect to SharePoint online during the conversation to perform CRUD operations to SharePoint site collections. 
 

Why would we need to connect the SharePoint bot?

 
There can be n number of use cases where a bot might be required to connect to SharePoint. For example, consider that you are creating a support bot and wanted to create/store conversation in the SharePoint List. Or you are using SharePoint List to create tickets. Or altogether you are creating a SharePoint support bot to automate certain use cases like knowing site collection admins, site quota, cleaning the recycle bin, etc.
 
In part 1 of the article, we created a basic bot. Now, let us extend it to connect to SharePoint online and access data.
 

High-level design of the solution 

  • Chatbot using Bot Framework using Form flow template in Node JS
  • Node module 'csom-node' to connect to SharePoint. The library provides a SharePoint Client Object Model (CSOM) API for Node.js applications.
  • Authenticate via user credentials or authenticate with app principal credentials (client id & client secret). We will use the first approach via user credential, though we will also see how to do it via app principal credentials.
I'm assuming you already have a basic bot created running as described in part 1.
 
The first thing we need to do is to install the csom-node package in our NodeJS bot.
  • Login to Azure portal
  • Select your Web App Bot 
  • Click on Build under Bot Management from the left pane.
  • Use Open online code Editor.
  • Open kudu console
Accessing/Updating SharePoint Online Data in Azure Chat Bot Framewok with Node JS
  • It will open the console in a new window like below.
Accessing/Updating SharePoint Online Data in Azure Chat Bot Framewok with Node JS
  • Go to path - cd D:\home\site\wwwroot>
  •  Run install command 
    1. npm i csom-node  
Alternatively, if you want to use app principal install it via the below command. This is a module from my GitHub repo. The reason behind using my repo is explained at the end of the article.
  1. npm install --save csom-node@siddharth-vaghasia/CSOMNode#master   
After installation you will get a success message, for me, as it was already installed, it says it updated 1 package.

Accessing/Updating SharePoint Online Data in Azure Chat Bot Framewok with Node JS
Go back to app.js, let us add the required code snippets.

The first thing we will do here is to create a function which will connect to SharePoint online by taking URL as parameters and return a callback function which returns web title. This will make sure we are able to connect to SharePoint Online.
 
Note
We could also ask the user to enter username and password one by one and then use it rather than hardcoding in code, but as it would have increase dialog flow code, I have kept it simple.
  1. N//Connect to SharePoint, take url as parameters and return Site Title  
  2. function ConnectToSP(siteurl, fn) {  
  3.     var csomapi = require('csom-node');  
  4.       
  5.       var settings = {  
  6.             url: siteurl,  
  7.             username: "[email protected]",  
  8.             password: "JokesOnYou"  
  9.         };  
  10.     csomapi.setLoaderOptions({ url: settings.url });  //set CSOM library settings  
  11.     var authCtx = new AuthenticationContext(settings.url);  
  12.     authCtx.acquireTokenForUser(settings.username, settings.password, function (err, data) {  
  13.         console.log(JSON.stringify(data));  
  14.         var ctx = new SP.ClientContext(siteurl);  //SM  
  15.         authCtx.setAuthenticationCookie(ctx);  //authenticate  
  16.                         
  17.         //retrieve SP.Web client object  
  18.         var web = ctx.get_web();  
  19.         ctx.load(web);  
  20.         ctx.executeQueryAsync(function () {  
  21.             console.log(web.get_title());  
  22.             fn(web.get_title());  
  23.         },  
  24.         function (sender, args) {  
  25.             console.log('An error occured: ' + args.get_message());  
  26.         });        
  27.     });  
  28. }  
Now, let us change our root bot dialog flow and ask the user to enter site URL and return the site title in response after the connection is sucessful.
  1. bot.dialog('/', [  
  2.     function (session) {  
  3.         builder.Prompts.text(session, "Hello... What's your name?");  
  4.     },  
  5.     function (session, results) {  
  6.         session.userData.name = results.response;  
  7.         session.send("Hi " + results.response);  
  8.         builder.Prompts.text(session,"Please enter SharePoint Online Site Colllection URL");  
  9.            
  10.     },  
  11.     function (session, results) {  
  12.         session.userData.siteurl = results.response;  
  13.         ConnectToSP(session.userData.siteurl, function (siteTitle) {  
  14.                 session.send("Connected - Your site name is " + siteTitle);  
  15.         });  
  16.     }  
  17. ]);  
Test your bot again and if everything is correct, it will show the below output.
 
Accessing/Updating SharePoint Online Data in Azure Chat Bot Framewok with Node JS
 
Awesome, give yourself a pat on the back. We were able to connect SharePoint online site from the bot. Now, let us now extend to get all list and library names under a particular site collection.
 
First, let us add function to get List and Libraries. Please note we have filter list libraries which are not hidden.
  1. function GetLists(siteurl, fn)  
  2. {   
  3.     var csomapi = require('csom-node');  
  4.     var settings = {    
  5.             url: siteurl,    
  6.             username: "[email protected]",    
  7.             password: "JokesOnYou"    
  8.         };        
  9.     csomapi.setLoaderOptions({ url: settings.url });  //set CSOM library settings  
  10.      var authCtx = new AuthenticationContext(settings.url);  
  11.     authCtx.acquireTokenForUser(settings.username, settings.password, function (err, data) {          
  12.         var ctx = new SP.ClientContext(siteurl);  //SM  
  13.         authCtx.setAuthenticationCookie(ctx);  //authenticate    
  14.         var web = ctx.get_web();  
  15.         var lists = web.get_lists();            
  16.         var libraries = [];    
  17.         ctx.load(lists);       
  18.         ctx.executeQueryAsync(  
  19.            function(){   
  20.                 var listEnumerator = lists.getEnumerator();    
  21.                 while (listEnumerator.moveNext())  
  22.                 {             
  23.                     var oListItem = listEnumerator.get_current();  
  24.                     if(!oListItem.get_hidden()){ // do not add hidden list  
  25.                         //Document Library Template = 101, Custom Lists Template = 100  
  26.                         // This is just to demostrate we can use other properties as well available in CSOM  
  27.                     if (oListItem.get_baseTemplate() == 101) {  
  28.                         var libname = oListItem.get_title();  
  29.                         libraries.push(libname);                          
  30.                     }    
  31.                     else if (oListItem.get_baseTemplate() == 100) {  
  32.                         var listname = oListItem.get_title();  
  33.                         libraries.push(listname);  
  34.                     }        
  35.                     }                           
  36.                 }     
  37.                 fn(libraries);  
  38.            },  
  39.         logError);  
  40.       
  41.         function logError(sender,args){  
  42.            console.log(args.get_message());  
  43.         }   
  44.      });  
  45. }  
Modify the bot root dialog flow.
 
Please note that here, we are using builder.Prompt.choice to display choices: One without style so that the user has to type, and another one using button style, so that the user can select among the choices provided, like buttons.
  1. bot.dialog('/', [  
  2.     function (session) {  
  3.         builder.Prompts.text(session, "Hello... What's your name?");  
  4.     },  
  5.     function (session, results) {  
  6.         session.userData.name = results.response;  
  7.         session.send("Hi " + results.response);  
  8.           
  9.         builder.Prompts.text(session,"Please enter SharePoint Online Site Colllection URL");  
  10.            
  11.     },  
  12.     function (session, results) {  
  13.         session.userData.siteurl = results.response;  
  14.         ConnectToSP(session.userData.siteurl, function (siteTitle) {  
  15.                 session.send("Connected - Your site name is " + siteTitle);  
  16.                 builder.Prompts.choice(session, "Do you want to get name of list and libraries?", ['Yes','No']);  
  17.         });  
  18.     },  
  19.     function (session, results) {  
  20.         if(results.response.entity == 'Yes'){  
  21.                 GetLists(session.userData.siteurl, function (arrayListLibraries) {  
  22.                 builder.Prompts.choice(session, "Below are list and libraries in you site.", arrayListLibraries,{ listStyle: builder.ListStyle.button });  
  23.                  });  
  24.         }  
  25.         else{  
  26.            session.send("Thank you, have a nice day.");  
  27.         }  
  28.     },  
  29.     function (session, results) {  
  30.         session.send("Connected - You have selected - " + results.response.entity);  
  31.          session.send("Thank you, have a nice day.");  
  32.          
  33.     }  
  34. ]);  
Test the bot again. If everything seems correct, you will get the below output. The screenshot is after the bot is connected to SharePoint. 
 
Accessing/Updating SharePoint Online Data in Azure Chat Bot Framewok with Node JS
 
Before concluding the article, let us also see how can we use the app principal to authenticate and connect to the user. To connect via App Principal we should have client id and secret generated and also appropriate permission granted to the app. How to generate this is nicely described in this article by Nanddeep Nachan. Below is what I did at a high level for testing.
  1. Register new app on https://tenant.sharepoint.com/_layouts/15/appregnew.aspx 
  2. Granted permission on https://tenant-admin.sharepoint.com/_layouts/15/appinv.aspx ( by using client Id generated from step 1)
I have provided full control permission but scope and permission can be given as per user requirements.
 
 Once we have client Id and client secret noted down, we can use the below method in NodeJs to connect to SharePoint 
  1. function ConnectToSPViaAppPrincipal(siteurl, fn) {  
  2.     var csomapi = require('csom-node');  
  3.      var settings = {  
  4.            url: siteurl,  
  5.             clientId: "CLIENTID HERE",  
  6.           clientSecret: "CLIENT SECRET HERE"  
  7.      };  
  8.     // MAKE SURE SITE URL IS ENDING WITH '/',  THERE IS BUG IN THIS MODULE, WHERE DUE TO SLASS MISSING acquireTokenForApp  
  9.     // method was giving 404 file not found.   
  10.     csomapi.setLoaderOptions({ url: settings.url });  //set CSOM library settings  
  11.     var authCtx = new AuthenticationContext(settings.url);  
  12.     authCtx.acquireTokenForApp(settings.clientId, settings.clientSecret, function (err, data) {  
  13.     console.log("got context");  
  14.     var ctx = new SP.ClientContext(siteurl);  //set it '/' for tenant root web  
  15.     authCtx.setAuthenticationCookie(ctx);  //authenticate       
  16.     //retrieve SP.Web client object  
  17.     var web = ctx.get_web();  
  18.     ctx.load(web);  
  19.     ctx.executeQueryAsync(function () {  
  20.         console.log("query ");  
  21.         console.log(web.get_title());  
  22.          fn(web.get_title());  
  23.     },  
  24.     function (sender, args) {          
  25.         console.log('An error occured: ' + args.get_message() + args.get_stackTrace());  
  26.     });  
  27.         
  28. });  
  29.   
  30. }  
Note
Please make sure site URL passed to the above method ends with 'slash'. If the URL does not end with slash '/', the acquireTokenForApp method will return a "404 file not found" error. I spent around 2 hours debugging this issue. So to save others' time, created a new branch with a fix. If you are going to use App Principal, install csom-node using the below command. 
  1. npm install --save csom-node@siddharth-vaghasia/CSOMNode#master   

Conclusion

 
In this article, we have learned:
  • How to connect Azure bot with SharePoint online.
  • What is Node JS FormFlow template?
  • How to install node module in Azure bot framework.
  • How to connect SharePoint online via User Credentials and via App only Principal.
This will give us basic direction on how to connect SharePoint online from Azure bot based on Node JS. You can extend this as per your requirement. Basically, you will be able to perform any operations on SharePoint online which are supported based on the client object model.
 
Hope this helps ... happy coding!


Similar Articles