Skip to main content

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')
    1. 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.

Comments

Post a Comment

Popular posts from this blog

Powershell script for converting JPG to TIFF

The following Powershell script will convert a batch of JPEG files to TIFF format: #This Code is released under MIT license [System.Reflection.Assembly]::LoadWithPartialName("System.Drawing") $files_folder = 'C:\path-where-your-jpg-files-are\' $pdfs = get-childitem $files_folder -recurse | where {$_.Extension -match "jpg"} foreach($pdf in $pdfs) { $picture = [System.Drawing.Bitmap]::FromFile( $pdf.FullName ) $tiff = $pdf.FullName.replace('.PDF','').replace('.pdf','').replace('.jpg','').replace('.JPG','') + '.tiff' $picture.Save($tiff) }
Post a Comment
Read more

Power Automate: SFTP action "Test connection failed"

When I added an SFTP create file action to my Power Automate flow ( https://flow.microsoft.com ) , I got the following error in the action step, within the designer: "Test connection failed" To troubleshoot the Power Automate connection, I had to: go the Power Automate portal then "Data"->"Connections"  the sftp connection was there, I clicked on the ellipsis, and entered the connection info It turns out, that screen provides more details about the connection error. In my case, it was complaining that "SSH host key finger-print xxx format is not supported. It must be in 'MD5' format". I had provided the sha fingerprint that WinScp shows. Instead, I needed to use the MD5 version of the fingerprint. To get that, I had to run in command line (I was in a folder that had openssh in it): ssh -o FingerprintHash=md5 mysftpsite.com To get the fingerprint in MD5 format. I took the string (without the "MD5:" part of the string) and put
Post a Comment
Read more

Alert if file missing using Powershell

The following Powershell script can be used to send an email alert when a file is missing from a folder or it is the same file from a previous check: $path_mask = "yourfile_*.txt" $previous_file_store = "lastfileread.txt" $script_name = "File Check" ###### Functions ########## Function EMailLog($subject, $message) {    $emailTo = "juanito@yourserver.com"    $emailFrom = "alert@yourserver.com"    $smtpserver="smtp.yourserver.com"       $smtp=new-object Net.Mail.SmtpClient($smtpServer)    $smtp.Send($emailFrom, $emailTo, $subject, $message) } Try {    #get files that match the mask    $curr_file = dir $path_mask |  select name    if ($curr_file.count -gt 0)    {        #file found        #check if the file is different from the previous file read        $previous_file = Get-Content $previous_file_store        $curr_file_name = $curr_file.Item(0).Name        if ($
Post a Comment
Read more