Requirements:

  • Leave requests stored in SharePoint (Office 365)
  • Leave applicant data (staff number, employee type, etc.) stored in Azure
  • Show the leave requests of the last two weeks in a grid, being able to edit the data locally & generate a custom export file.

We have implemented it with the following components:

  • A Web API with two controllers:
    • LeaveRequestController to communicate with Office 365 and retrieve the leave requests
    • PeopleController to communicate with Azure Graph AD to retrieve custom AD properties
  • A SharePoint Hosted Add-in with a custom action in the leave requests list. By clicking this button the user is taken to the leave export screen.

For the leave export screen I’ve used jqgrid-free, a fork of the popular jqgrid component. I’ve chosen to use jqgrid-free instead, as the “official” version is no longer free.

LeaveRequestController : App-Only Access Token

Our Web API is being used by multiple components and I don’t always have a client access token. So I decided to try the App-Only Access Token approach. In the past, doing something like this in the server-side object model this was super easy. Just run SPSecurity.RunWithElevatedPrivileges and your code runs as the application pool.

In the modern era we are in now, this is harder as your code does not run on a SharePoint Server anymore. Doing App-Only calls to Office 365 SharePoint involves the following:

  • Configure an Azure AD application and configure required App-Only permissions
  • Create a self-signed certificate. Upload it to your Azure AD Application and include it in your Web API project
  • In your code, retrieve an access token by passing client id and your certificate.

Initially it looked complex. But, I’ve followed this and this guide, and it worked!

With the App-Only access token I called the SharePoint REST API to retrieve my leave requests. This should be quite straight-forward. More information to do that on MSDN.

PeopleController: Connect to Azure AD via Graph API

The next thing to do was to query Azure AD to retrieve additional people information. I am using the same access token as I’m using to talk to SharePoint. The only thing I had to do is to configure the app-only permissions to allow my app to access Azure AD.

See here for more information to query Azure AD Graph API.

This is how my Azure Application permissions are looking like:

SharePoint Hosted Add-in with jqGrid-Free

I created a SharePoint Hosted Add-in as I’m only using Javascript and HTML. I added a custom action to show in the “Connect & Export” group in the ribbon for my leave requests list. First problem to be solved was: How to set a ribbon button image? Turns out you can’t use ~appweburl token in the image URL. So, I converted the two images to a data URL using dataurl.net and set that as the value for Image32by32 & Image16by16.

Then the fun part. How to configure jqgrid? The documentation of jqgrid is awesome, and when you Google for issues you will find a lot of resources. Some handy tips:

  • To load data, simply configure the URL property and point it to your Web API. I configure the dataType property to be JSON. However, by default Web API returns XML. You can change that to be JSON with this line of code in your Startup.cs:
    config.Formatters.JsonFormatter.SupportedMediaTypes.Add(new MediaTypeHeaderValue("text/html"));
  • So yes, I’m using OWIN, and I’m not using global.asax. Just because I think that’s the future and you’re not depending on IIS anymore. If you are using IIS, and things are not working (i.e. Startup.cs not being called), make sure you’ve installed the NuGet package Microsoft.Owin.Host.SystemWeb…
  • I wanted to implement client-side only cell editing, the data wasn’t saved back to the server. There is a difference between inline row editing and inline cell editing. Turns out the last one was the easiest one, you have to set the following two properties:
    cellEdit: true, 
    cellsubmit: 'clientArray',
    editurl: 'clientArray',
  • To receive the (probably edited) data, call the following method:
    var rowData = $(“#leaveRequestsTable”).jqGrid(‘getRowData’);
  • Then I ran into the following issue. Assume you edit a cell, and while editing, you click on the Export button which calls above method to retrieve the data. The cell you are currently editing, will then be empty! I’ve struggled a while to fix this, as there is not much documentation I could find. In the end it’s quite simple:
    • Define a JavaScript variable: var currentCell = null;
    • Save the cell that is being edited in that variable, and set it back to null afterwards:
      beforeEditCell: function(rowid, cellname, value, iRow, iCol) {
        currentCell={ 
          rowid:rowid, cellname:cellname, value:value, iRow:iRow, iCol:iCol
        } 
      },
      afterSaveCell : function(rowid, cellname, value, iRow, iCol) {
        currentCell = null;
      },
    • In the button click event to export the data, currentCell will be not null when a cell is currently being edited. By calling the jqGrid method saveCell in that case, it will be saved:
      if (currentCell != null) {
         $("#leaveRequestsTable").jqGrid("saveCell", currentCell.iRow, currentCell.iCol);
      }
  • Every leave request had an applicant, which in SharePoint is a user field (essentially a lookup field). I didn’t want to show this, but I wanted to keep track of it. But, it’s not a string value, but a JSON object. So, to store it in the grid, I had to create a custom formatter to format it as a JSON string, and unformat it back to an object. This is easy in jqGrid:
    function objectFormatter(cellValue, options, rowObject) {
     return JSON.stringify(cellValue);
    }
    function objectUnformatter(cellValue, options, rowObject) {
     return JSON.parse(cellValue);
    }


    In the colModel:

    { label: 'LeaveApplicant', name: 'LeaveApplicant', hidden: true, formatter: objectFormatter, unformat: objectUnformatter },

    This way the value was serialized and deserialized properly and returned in the getRowData method in my button click event.

  • How to reload after initialization? Calling the initialization method again won’t work. There is a method reloadGrid, but As I’ve defined loadOnce to be true (which I couldn’t set to FALSE), the method reloadGrid does not work immediately. After some Googling I found the solution:
    if (gridLoaded) {
      $("#leaveRequestsTable").setGridParam({ url: url });    
      $("#leaveRequestsTable").trigger("reloadGrid", { fromServer: true, page: 1 });  
      return false; 
    }

    I set gridLoaded to true after initializing (i.e. calling jqGrid with all the options).

  • jqGrid assumes the response in a certain format, which is not what Web API returns by default if you just return a list of data objects. Additional information is expected in the response to support paging. As I don’t use paging, I decided to change the configuration so that I don’t have to change my Web API:
    • Set loadOnce to true
    • Configure the jsonReader property as follows:
      jsonReader: {
        repeatitems: false,
        page: function () { return 1; },
        root: function (obj) { return obj; },
        records: function (obj) { return obj.length; }
      }

Probably these are the most important lessons learned. The end result looks like this:

Overview

Inline Cell Editing