Professional Code Review for Office Scripts Optimization
Master Office Scripts: Solve complex challenges with power automate and enhance your programming skills as a Microsoft expert
Insights into Office Scripts for Power Automate
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.
Understanding Office Scripts for Power Automate
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.
Learn about Office Scripts - Code Review
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:
- Stability
- Efficiency (Each call has response time and size limit)
- Readability (for better Developer transition)
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:
- High volume of data writes (Power Platform/Excel API throttling)
- High volume of data reads (Power Platform API throttling)
- Time Sensitive runs
- Formatting
- No table data
- And missing Power Automate functionality
Now that you are positive that Office Scripts is the best solution, consider these factors:
- Connector
- Naming
- Formatting
- Error Handling
- Coding Practices
Let's delve into the above factors:
- Connector: Leverage the new Run script from SharePoint library. This should be the connector of choice. The OneDrive version might be difficult for promoting between environments/accounts hence the preference for the SharePoint version.
- Naming: Naming conventions are equally fundamental to Office Scripts as they are for Power Automate. Aim for a similar structure:
- Type Structure Worksheet wsName
- Workbook wbName
- String sVariable
- Regex regExVariable
- Range rRange
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’).
- Formatting: Your code should track single tab nesting, with comments added where necessary. Unnecessary code should be removed before production.
- Error Handling: Simpler scripts can avoid error handling as these can be captured in the flow. However, for anything beyond simple and if you plan to employ outside of Power Automate, a Try Catch should be brought into play.
- Coding Practices: This is an umbrella category that encompasses any issue that doesn't fall into the above classes.
Here's a quick run down:
- Ensured all variables and input parameters are used. If it's not needed, remove any excess variables or script inputs.
- Omit all console logs, they don't add value to Power Automate calls and can inhibit performance, especially in loops.
- Avoid using copy / paste as Excel actions like these can severely impact performance. Steer clear of copy and paste and set one range to another range instead.
Ready to dive deeper into Office Scripts? Check out the following resources:
Keywords
Office Scripts, Code Review, Excel Automation, Power Automate, Microsoft 365, JavaScript API, Excel on Web, Office JavaScript APIs, Office Add-ins, Script Lab.