Office Scripts : publish a serverless function from Excel365

Abstract

Continuing my effort in using and understanding the best use cases for "Low Code" platform, and in particular "Power Platform", I've had a classical challenge in trying to automate a business process consisting in use excel as "single source of truth" and/or as the main target to present process output.

The Use Case

The use case that I'd want to describe here is "manage the timesheets of the resources allocated by customers".
Obviously there are a lot of software that are able to do it, but in this particular case the complication is that the customer use an own system for manage the consultants' timesheets and it isn't open to the providers so them, to keep control on billing hours, need to be aligned on that in someway.

Use the Mail

As you can image to solve problem the "quick & easy" solution adopted has been the "mail notification", delegating the alignment process to an internal resource (in my case the HR person) that, mainly, receive mails concerning timesheet's entries and put them in an excel sheet that uses to double check with the customer the billing hours.

How to Automatise

So, how to automatise as much as possible this process in order to minimise the required manual effort avoiding the probably errors that can happens during such task?

A Power Platform Solution

After the above considerations I've proposed to use the "Microsoft Power Platform" and to avoid extra licenses costs I've decided to use "Powerapps for Teams" that is a limited version of "Power Platform" integrated with "Microsoft Teams"

Assumptions

Before start design & implementation I've made the following assumption:

Reuse the already in place process to reuse both the mail and the excel sheet "as-is" because this will minimise impact in its adoption.

Power Platform Architecture of Solution

Pic.1 - Reference Architecture (rc1)

Above there is the first "reference architecture" that I've designed to accomplish the requirements.

Trigger Flow from Mail

As you can see each mail sent/forwarded to a pre-configured mail group triggers a "Power Automate Flow" that :

1. Identifies the sender
2. Analyses the subject to understand the nature of time entry (Hours Off, Holiday, Availability, Overtime, ...). Currently this is achieved by a server-less function but is planned to use the Power Platform AI extension

Save Data

After that the gathered information are saved in Dataverse in the state: pending for approval

Approve Data

In this first release I've preferred introduce a manual step for approve timesheet's entry processed by flow in order to verify eventually errors during processing, so the Approver (see Pic.1) for a while has been me self

Approver vs Validator.

Idea is that the Approver and Validator could be the same person

For this I've developed a Canvas App that read timesheet entries to approve from Dataverse and present a screen allowing to approve, reject or delete each entry

Generate Excel

The final step is to create/overwrite a file excel in sharepoint and for this purpose I choose to call a Flow from canvas App. There are lot of available "how to" concerning that and I don't want go in deep on it however below there are the main steps applied :

1. Canvas App call a Flow passing the data, got from Dataverse, converted in JSON format
2. The Flow get JSON data, convert them in CSV format and create a new Excel file with such content

Delivery of first release

After tests I've delivered solution in the production environment and after a while I got the first critical issue but it wasn't related to technical problem rather an uncovered (and unespected) use case.

The issue (ie. new use case)

The new use case was that the user that play role of Validator needed to updated Excel file (also writing complex formulas) before a new generation so this means that the Excel cannot be overwritten but it must be a live file and this implies that the Flow has to work on a pre-existent file updating selectively the cells within.

The Office Script comes to rescue

To solve the issue, I've tried to update the Excel cells directly from flow using Excel connector but It requires that such file adhere to several constraints moreover it highly increases the complexity of the Flow itself.
So I've search for other possible solutions and luckly I came across in the "Office Scripts"

Office Script

The "Office Scripts" are designed for the Office365 on the web and they are scripts allow you to record and replay your Excel actions on different workbooks and worksheets. If you have to perform the same tasks over and over again, you can turn all that work into an easy-to-run Office Script. Such scripts can be combined with Power Automate to streamline your entire workflow.

Script Anatomy

An Office script is essentially a typescript module that must contain a main function with the ExcelScript.Workbook type as its first parameter.

function main(workbook: ExcelScript.Workbook, ...args: any[]) {
}

Form there you can access to Office Script object model which features are outlined below

Office Script object model

• A Workbook contains one or more Worksheets.
• A Worksheets gives access to cells through Range objects.
• A Range represents a group of contiguous cells.
• Ranges are used to create and place Tables, Charts, Shapes, and other data visualization or organization objects.
• A Worksheet contains arrays filled with those objects that are present in the individual sheet.
• A Workbook contains arrays of some of those data objects for the entire Workbook.

Script Development & Deployment

The Office Script IDE has provided directly inside the Excel itself. It is available from menu Automate where you could create/edit new/existent scripts using a handy typescript editor available also if you are editing Excel in the web. Take a note that such editor provides full support of intellisense. When we save a new script it is stored, by default, in OneDrive Documents/Office Scrips as OSTS file and it is immediately available for run.
For further details take a look at "Record, edit, and create Office Scripts in Excel on the web"

Let's get back on track: let use "Office Script" in Solution

As you see there are endless possibilities to manipulate the Excel content but, in my opinion, the feature that is the real game changer is that each script could be invoked by a Flow and it is possible exchange data between them. Essentially each script becomes in effect a serverless function hosted inside Excel that open a number of scenarios never imaged before. So the architecture changes in the follow way:

Pic.2 - Usage of Office Script

Et voila' the issue has resolved just a last consideration: Since the document could be edited live we need to be sure that during process the file cannot be edited how to do this?. Luckly the Shareponint connector give us the possibility to perform Check Out / Check In on file. So the final solution becomes

Pic.3 - Reference Architecture (final)

Conclusion

In this article I tried to provide a better understanding about potential of Office Script integrated in Power Automate by presenting a real use case that is currently in production.
It was not my intention to go into the implementation details, however if you are interested please let me know so I could prepare a new article containing technical insights on the various steps of the workflow

Happy coding and … enjoy Office Script !

Originally published at https://bsorrentino.github.io on December 19, 2021.