Excel as the Business Rules Engine for your Application

How can you allow a Business User to maintain the business logic of your application? If your business users are Excel-savvy (as much business users are), then you could use Microsoft’s Graph API to access and run the logic within those Excel workbooks (workbook resource type - Microsoft Graph v1.0 | Microsoft Learn).

This is not the OLE Automation of years past. We are talking about calling an HTTP based API.

The approach is simple, from the business user side:

1. Have the business users create the workbook and adjust it so that it performs the business rules it needs. They can designate some of the cells to be used as the “parameters” for the business logic. For example, given the values of cells A2 and B2, run a series of formula calculations whose final result is in cell B30.
2. Save the excel workbook in a folder in your business’ OneDrive.

Then, from your application code, make a series of HTTP calls to the MS Graph API:

1. Get the Auth Token
2. Call the createSession endpoint to create a non-persistent session. The non-persistent session will prevent changes from being saved but still allow you to get the calculated results within the session. E.g.:

POST https://graph.microsoft.com/v1.0/me/drive/root:/sources/public/excel-as-bz-rules-sample.xlsx:/workbook/createSession

1. Call the worksheets endpoint with a PATCH verb (make sure you pass the session id of your non-persistent session) and the values to use to update the cells your business user designated as the “parameters”. E.g.:

PATCH https://graph.microsoft.com/v1.0/me/drive/root:/sources/public/excel-as-bz-rules-sample.xlsx:/workbook/worksheets/Sheet1/range(address='A3') 

1. Read the “results” cell(s) from the workbook by calling the worksheets endpoint with a GET. E.g.:

GET https://graph.microsoft.com/v1.0/me/drive/root:/sources/public/excel-as-bz-rules-sample.xlsx:/workbook/worksheets/Sheet1/range(address='B8')

    (Again, make sure you pass the session id of your non-persistent session)

Having your business rules encapsulated in an Excel workbook would allow your business users to ensure the business logic is always right. Please note that this approach requires the following:

1. The Business User is now responsible to ensure the logic is correct, which should be fine as they are the domain experts. What the business units are probably not familiar with is versioning of that logic. Thus, it is important to help them create a flow where the “ready” version of the Excel file gets delivered to the appropriate OneDrive folder so that unfinished or incorrect files are not used by the application.
2. You can still do integration testing from your code to test the accuracy of the business logic in an automated way. This means that you still need to talk to the business users and understand the success and failure conditions and create automated tests for that. This is not different from your unit tests on your domain logic code, with the exception that you are now calling APIs (i.e.: integration tests). But you should only need to do that testing when the business unit passes down in the flow a file ready to be used. Again, it is highly important to establish the proper “release” flow with the business.