Office Scripts add much missed functionality to Power Automate (cough regex, cough come on Microsoft). But this adds in 2 areas of complexity that can make your flow runs less stable.
The obvious one is a new moving part and integration. Where ever 2 systems pass data problems arise, and trying to debug both Power Automate and Office Scripts together is exponentially harder than just one of them.
The next is it's a whole different skill set, this is no longer NoCode, or LowCode, this is ProCode. I know some ProCode developers will disagree, but it's TypeScript, just a particular library (admittedly limited). This can mean challenges as the Flow developer isn't trained or experienced in coding. With this is mind code reviews are crucial to ensure that the Office Script is:
- Stable
- Efficient (Each call has response time and size limit)
- Readable (Developer hand over)
A starting point before using a Office Script should be is it necessary, following KISS it is best to keep everything in the flow if we can. Office Scripts should only be used for:
- High volume of writes (Power Platform/Excel API throttling)
- High volume of reads (Power Platform API throttling)
- Time Sensitive runs
- Formatting
- No table data
- Missing Power Automate functionality e.g. Office Scripts can be used as a regex matcher, in this case Excel isn't used, only the Office Script.
Once confirmed Office Scripts are the right solution I look at the following:
- Connector
- Naming
- Formatting
- Error Handling
- Coding Practices
1. Connector
Quick and easy one here, the new Run script from SharePoint library is the connector that should be used.
Trying to use the OneDrive version is a pain for promoting between environments/accounts (I wrote a full blog with a workaround here), so the new SharePoint version is a must.
2. Naming
Naming conventions are key to Office Scripts just like Power Automate, I follow the similar structure:
| Type | Structure |
|---|---|
| Worksheet | wsName |
| Workbook | wbName |
| String | sVariable |
| Regex | regExVariable |
| Range | rRange |
| Object | oVariable |
| Interface | intStructure |
| Integer | iVariable |
| Float | fVariable |
| Boolean | bVariable |
| Array | aVariable |
With the addition of below when using for loops:
| When | Variable Name |
|---|---|
| Loop | i |
| Column | c |
| Row | r |
When declaring constshould be used for constants, and let for variables (do not use var).
Interfaces should use the int as the structure e.g. intData, with its keys following naming convention. The only exception is when the interface is using Excel columns, in this case colA,colB,colC etc can be used.
3. Formatting
Code should follow single tab nesting, with comments added where necessary. Unused code should be removed before production.
4. Error Handling
Simple scripts can normally get away without error handling, as this can be caught in the flow. For anything above simple (and if you plan to use outside of Power Automate) a Try Catch should be used.
5. Coding Practices
This bucket catches anything that doesn't fall into above categories, for me they are:
All variables and input parameters are used
Easy one, if it's not needed remove any variables or inputs of the script.
Remove all console logs
They add no value in Power Automate calls and can impact performance (especially in loops)
Do not use copy / paste
Excel based actions like this have big impact on performance, so avoid copy and paste and set one range to another range
Do not loop over rows
Any interaction with the file has big impacts on the script performance, instead get range into variable and loop over array.
Do not write by row
Same as above, we don't want to be interacting with Excel, especially in loops, use set range to array.
Use functions for reusable code
Just like all ProCode, Office Scripts can have reusable functions that are called anywhere within your main script.
Use getRangeBetweenHeaderAndTotal().delete(0) to delete rows
Still not 100 sure why this is more efficient but use it instead of selectedSheet.getRange("4:16").delete(ExcelScript.DeleteShiftDirection.up)
or
selectedSheet.getRange("A4:D16).clear(ExcelScript.ClearApplyTo.contents)
Further Reading
Top comments (0)