Executing Business Rules with Excel

Introduction

I know, not really the title you’d expect, right? There are dozens of business rule engines out there and they are not inexpensive, to say the least. There are however numerous use cases that would benefit from a simpler business rule engine, that doesn’t add huge set-up costs or high recurring costs while still allowing extensive possibilities to automate rules so you don’t have to hard-code them.

Okay okay, but Excel??

Yeah sure, you can do that with Excel. There are numerous functions that will allow you to model business rules like SUM(), LOOKUP(), MATCH(), INDEX(), … And you have all the freedom of your workbook to organize your rules into a reusable set of rules which remain readable to business users.

The trick is to use one sheet for input and one sheet for output. That way you can easily write the inputs in bulk to the worksheet and read the outputs in bulk from another worksheet.

Let me nuget that for you…

For those who don’t know what NuGet is, it’s a package manager for .NET that you can use to easily add libraries so you don’t have to code everything yourself (and other fantastic reasons).

Anyway, the next step is not to find a library that can read to/from Excel because none of those libraries actually have a calculation engine inside of it. Some do have some function support, but this is rather limited and the really useful functions like VLOOKUP are not implemented there.

So, we’re stuck with only one great alternative: Excel Services.

I first met Excel Services in 2012 when working with SharePoint 2010. We had a lot of legislative rules to automate and SharePoint 2010 promoted the use of Excel Services heavily. So we tried it out – and it worked out rather nicely. But that was 2012…

Fast foward to 2019

Today we have Microsoft Graph to help us out, we don’t need to use the Excel Services SOAP service anymore (hooray!). Nowadays Excel Services works quite differently as it uses the Graph REST API to access, calculate and retrieve data from Excel. Let me step you through how to set it up for your first project.

Getting our hands dirty

 

1. Make your Excel rules

Firstly, create your Excel file and structure it into 3 sheets: Input, Calculations, Output. You can also add things like Lookup lists in separate sheets if needed.

Define a range you want to write to in the Input sheet and define a range you want to read from in the Output sheet. Name these ranges using Name Manager. This is important to make integration easy. You can also just use classic range names like A1:B7.

2. Save it to SharePoint or OneDrive

Make sure you save it to either SharePoint Online or OneDrive for Business.

3. Get programmatic access

Make sure you get your favorite REST client ready (I like RestSharp) or you can use the Microsoft Graph SDK NuGet package.
Next, you need to set up access to your file. Thanks to the wide adoption of OAuth, you can simply create an Azure Active Directory App in the Azure Portal of the tenant that hosts your file.

Make sure the Files.Read permission is selected and that you grant permissions as an administrator. Otherwise, the app-only authorization won’t work.
This will generate your client ID and client secret.

Then, there are 2 ways you can authenticate your code. Either through an interactive flow with an end user granting you access using their “user” policy. This is typically not very handy in business rule execution scenarios. That’s why I chose to use the “app only” permission. This requires using the Microsoft.IdentityModel.Clients .ActiveDirectory NuGet package.

To invoke Excel Services and the Graph API you need an Access Token. To get it, you can use the following code:

var clientId = “_your client id_”;
var clientSecret = “_your client secret_”;
var clientCredential = new ClientCredential(clientId, clientSecret);
var ac = new Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext(

“https://login.microsoftonline.com/_your tenant_.onmicrosoft.com”);

var authResult = ac.AcquireTokenAsync(“https://graph.microsoft.com”, clientCredential);
return authResult.AccessToken

There are other ways to get your token (using Msal or ADAL), but let’s not get into that for this post.

4. Identify your file

The most complex thing you’ll read in this blog post is how to identify your file. This is tricky, it’s not just the URL to your file like:

https://yourtenant.sharepoint.com/sites/pathtoyoursite/
library_name/pathtoyourfile/filename.ex

It relies on the concept of “Drives”, being a modern concept of how files are accessed – mainly to make an abstraction of SharePoint and OneDrive I’m guessing.

First, we reference the graph API base URL.

https://graph.microsoft.com/v1.0

Then comes the fact that we want to reference a SharePoint site, being the root site collection. Alternatives would of course be realized using the Site ID instead of root.

/sites/root

Next, we get all lists and select the list with the given ID.

/lists/

Then we get the items in its “Drive”.

/drive/items

And each item in the drive has a funky ID like the one you can see here.

/01YPFJC4EJDHYMQCGPKZELNHPRRWVUFM

The entire URL is used as the REST endpoint.

So let’s connect to our file!

PS: you can use the Microsoft Graph Explorer to interactively find these IDs as you build up your URL.

5. Create an Excel Services Session

Excel Services Sessions improve performance and allow you to write and read data from a copy of the file that is not persisted to SharePoint or OneDrive. For concurrent access to the rules, you need to make sure you use non-persistent sessions.

This can be achieved easily with the REST API:

RestClient client = new RestClient(“https://graph.microsoft.com/v1.0”);
var request = new RestRequest(FileGraphUrl + “/workbook/createSession”,

Method.POST, DataFormat.Json);

request.AddHeader(“Authorization”, “Bearer ” + AccessToken);
request.AddHeader(“Content-Type”, “application/json”);
request.AddJsonBody(new { persistChanges = false});
var result = client.Execute(request);
var sessionId = result.Data[“id”];

Note how we are using a POST request to the /workbook/createSession endpoint on top of our file base URL. We are also adding the persistChanges = false JSON object as a body parameter to the request.

This returns a session ID. This session will be used throughout all future requests so Excel Services knows which file we are manipulating. Well actually, which copy of a file we are manipulating in Graph “memory”.

6. Getting the IN and OUT worksheets

We need to know what the worksheets in the document are referenced by. This piece of code gets us that:

var request = new RestRequest(FileGraphUrl + $”/workbook/worksheets”,

Method.GET, DataFormat.Json);

request.AddHeader(“Authorization”, “Bearer ” + AccessToken);
request.AddHeader(“Content-Type”, “application/json”);
if (!string.IsNullOrEmpty(SessionId))

request.AddHeader(“workbook-session-id”, SessionId);

var result = client.Execute(request);
return result.Data.value.SingleOrDefault(v => v.name == worksheetName).id

Note how we are using the session ID through the workbook-session-id request header.

Once we have the IDs for the worksheets, we can start writing and reading!

7. Write input values

We have access to the file, have a session and we know on which worksheet we will write our input data. So let’s see how we can write these values to the input sheet:

var request = new RestRequest(FileGraphUrl + $

“/workbook/worksheets/{worksheetId}/range(address='{rangeName}’)”,
Method.PATCH, DataFormat.Json);

request.AddHeader(“Authorization”, “Bearer ” + AccessToken);
request.AddHeader(“Content-Type”, “application/json”);
if (!string.IsNullOrEmpty(SessionId))

request.AddHeader(“workbook-session-id”, SessionId);

request.AddJsonBody(new { values = newValue });
client.Execute(request);

Note here that the rangeName can be both a named range like “SA_IN_ZipCode” and a range in the form of “A3” or “A3:A6” when writing multiple values at once.

We are also using the HTTP PATCH method, rather than POST or PUT, mind you.

The values parameter is used to supply the new value. This is typically a string value, a string array or a string 2D array, depending on the range you are writing to. Strings are used, just like you would input manually in Excel.

8. Reading output values

Last but not least, we need to read the values back so we can display them in whatever application is calling the business rule:

var request = new RestRequest(FileGraphUrl + $

“/workbook/worksheets/{worksheetId}/range(address='{rangeName}’)”,
Method.GET, DataFormat.Json);

request.AddHeader(“Authorization”, “Bearer ” + AccessToken);
request.AddHeader(“Content-Type”, “application/json”);
if (!string.IsNullOrEmpty(SessionId))

request.AddHeader(“workbook-session-id”, SessionId);

var result = client.Execute(request);
var objArray = JsonConvert.DeserializeObject(result.Data[“values”]);
return (T)Convert.ChangeType(objArray[0][0], typeof(T))

Of course we are using the HTTP GET method here.

The most complicated thing here is to convert the JSON array to a single type – depending on how you’re using it of course. In this example, we are converting to Type T which is mostly a double that contains a calculated number, but could also be a boolean, string, …

You could also read multiple values from the OUT sheet at once, which would change the objArray code a bit.

Conclusion

Wow, what a journey. The Graph API has created many opportunities for us to create a simple and understandable Excel sheet filled with business rules and allows us to execute these rules programmatically. Hence eliminating the need for a large-scale business rule engine business model, typically requiring you to invest more into day-to-day business rule automation, which was never the initial question anyway.

You could run this as a Web API in Azure easily, authenticating using Azure AD and even using pass-through authentication from your user, when you supply the access token from the user.

Keep in mind that you still need SharePoint Online or OneDrive for Business, although this functionality to OneDrive for home should also be supported with the v2 auth API.

For more information, please get in touch and we’ll gladly help you:
info@loqutus.com

Written by Sebastiaan Mindreau, Lead Strategist Digital at LoQutus.