David Wyatt's recent blog post provides a deep-dive into the function of Office Scripts, particularly as it pertains to enhancing functionality in Power Automate. It covers the challenges and complexity that can arise, especially when one needs to debug both Power Automate and Office Scripts concurrently. A shift from NoCode or LowCode to ProCode is evident, which might pose challenges for the Flow developer who might not be trained or experienced in coding. Such shifts emphasize the importance of conducting code reviews in order to ensure the Office Script is stable, efficient, and readable for a smooth developer handover.
Wyatt advises to consider whether a Office Script is necessary before using it. Based on the KISS principle (Keep it Simple, Stupid), it is advisable to retain everything within the flow if possible. Wyatt offers a host of scenarios whereby Office Scripts could be put to optimal usage, including high-volume writes or reads, time-sensitive runs, formatting, and no-table data, among other scenarios.
The author shed light on key pointers to focus on once it has been confirmed that Office Scripts are the right solution. These comprise assessing the Connector, naming, formatting, error handling and various coding practices. With respect to the Connector, the author advocates using the new Run script from SharePoint library.
Next is naming, with a structured format suggesting how Office Scripts should replicate Power Automate’s conventions. The use of single tab nesting is an essential element of formatting, and the author insists on removing any unused code before pushing into production.
As far as Error Handling is concerned, simple scripts are often exempted from error handling since they can be caught in the flow. Try Catch is recommended for scripts beyond simple. In terms of coding practices, several suggestions are offered including the removal of unnecessary variables and input parameters, eradication of all console logs, avoidance of copy/paste actions, loop over rows and row by row writing.
Office Scripts essentially fills a functionality gap in Power Automate, albeit introducing certain operational complexities. It requires a shift from low code or no code to a ProCode approach which might be challenging for those uninitiated in coding.
Office Scripts should be leveraged in scenarios of high-volume data reading/writing, time-sensitive functions, formatting, or when there is lack of table data present. Ensuring appropriate utilization of Office Scripts is quintessential and requires assessing various parameters like Connector, naming, formatting, error handling, and the usage of different coding practices.
Office Scripts for Power Automate offers flexibility and enhances functionality if well-utilized. For more insights, you can visit the original post.
Office Scripts are a great tool bridge for Power Automate, adding functionality that might otherwise be missing. However, they tend to requ considerable complexity which can affect stability of your flow runs. This is evident in two key areas. The most apparent being new moving parts and integrations, which can cause data problems to arise and debugging becomes difficult. The second stems from the fact that Office Scripts require a different skill set. This is not NoCode or LowCode, rather it's ProCode. This might pose a challenge for Flow developers untrained in coding. Code reviews are thus crucial for the following reasons:
However, before employing Office Scripts, it is crucial to question if it's necessary and ensure we keep to the KISS principle. It’s best to contain everything within the flow if possible. Office Scripts are then only employed under the following conditions:
Now that you are positive that Office Scripts is the best solution, consider these factors:
Let's delve into the above factors:
When utilizing for loops, adhere to the convention: Variable Name Loop - i Column - c Row - r. While declaring, const should be applied for constants, and let for variables (do not use ‘var’).
Here's a quick run down:
Ready to dive deeper into Office Scripts? Check out the following resources:
Office Scripts, Code Review, Excel Automation, Power Automate, Microsoft 365, JavaScript API, Excel on Web, Office JavaScript APIs, Office Add-ins, Script Lab.