Office Scripts for Excel, as the name suggests, is a programming language that indeed requires coding. To ease the learning curve and offer a straightforward starting point for script creation, Office Scripts includes a point-and-click code-generating tool called Record Actions. If you are familiar with the Macro Recorder in VBA, this experience will be quite similar.
In this post, we’ll cover the basics of using Record Actions, demonstrate some quick wins, and explore its limitations and drawbacks.
To follow along with this demonstration, please download the exercise file provided below:
We have a small timekeeping dataset in the first worksheet of the workbook that we need to format consistently. This sounds like a perfect task for Office Scripts! Let’s explore how we can achieve this without writing any code from scratch, using the Record Actions feature.
Creating a script with Record Actions
To get started, head to the “Automate” tab on the ribbon and select “Record Actions.”
Once you do so, a Record Actions menu should appear to the right of your data. It’s on and alive! Any Any actions you perform will be recorded. Go ahead and execute these steps on your worksheet:
- Convert column A to the Date format.
- AutoFit the column widths.
- Highlight the cells in the “Hours Spent” column where the value exceeds 3.
When you are finished, click “Stop.” If you accidentally included incorrect steps, such as formatting the wrong column, you can remove them by clicking the “X” button next to the respective step.
Keep in mind that applying Conditional Formatting triggers several steps in the recording process because you need to define the cell font and fill, along with the range and other details, making it a more complex task.
Once you click “Stop,” the script will be saved. Next, let’s click on “Edit” to view the underlying code generated by Office Scripts.
Your script should look something like this, and it even includes comments explaining what the code does—pretty cool!
Now that this script has been generated and created, you are free to use it on different datasets, such as the next worksheet in the sample workbook. The steps will run just as before, making this a reproducible report formatter—awesome.
Advantages of Record Actions
As you can see, Record Actions in Office Scripts can be extremely beneficial, particularly for users who are not proficient in scripting. It simplifies the automation process by allowing users to record their actions in Excel, which are then automatically converted into a script. This approach saves time and ensures accuracy by capturing the exact user actions.
Additionally, it serves as an effective educational tool, helping users understand how their actions correlate with script commands.
Disadvantages of Record Actions
Despite its benefits, Record Actions in Office Scripts comes with several limitations. It only records actions within a single worksheet at a time, which is a significant restriction for tasks that span multiple sheets. Moreover, it’s not very dynamic. For example, we hard-coded that cells D2:D7 should be conditionally formatted. To make this more adaptable to different table sizes, we would need more dynamic scripting, which the recorder isn’t set up to handle as it operates on a “What you see is what you get” basis. It does little to make the code dynamic and extensible for responding to as yet unseen data situations.
The scripts it generates are often not optimized and may include unnecessary steps that can degrade performance. These scripts can be challenging to maintain due to their verbose and sometimes unclear coding, and they may not scale well or adapt easily to changes in workbook structure.
Additionally, there’s a risk of inadvertently recording sensitive data, and users might execute scripts without fully understanding the implications, potentially leading to errors or data mishandling.
Understanding relative vs absolute mode
Before we conclude this exploration of Record Actions, let’s examine one more setting: the distinctions between Absolute mode and Relative mode. Go ahead and start a new Record Actions session on a blank worksheet, and you’ll notice at the top of the menu a dropdown to toggle between the two.
When using Relative mode, then Absolute mode, record two different scripts with the following actions:
- Type 10 into cell B2.
- Type 10 into cell A1.
In Absolute mode, the script will specifically target and reference these cells exactly as you interacted with them during recording, which means the actions are fixed to the specific cells you selected. This results in a script where the cell references are hard-coded, reflecting the exact cells manipulated during the recording.
In relative mode, the script will not hard-code specific cell references. Instead, it will perform actions relative to the initially active cell. For example, if you start recording with B2 as the active cell, the script will describe actions relative to this position, making it adaptable to different starting points in the spreadsheet.
To recap the difference: in absolute mode, every action you record is tied to a specific, fixed cell address. This approach is straightforward, ensuring that the script always affects the exact cells you specify, regardless of where you start or what the active cell was when you began recording.
The primary advantage of absolute mode is its precision, as it targets specific cells directly, making it reliable for tasks that consistently affect the same cells. It also provides clarity, as each action directly corresponds to a specific cell.
However, this mode lacks flexibility and is not suitable for tasks that need to adapt to different starting points or data layouts. Scripts created in absolute mode can become cumbersome and less reusable for dynamic tasks.
In contrast, relative mode records actions in relation to the active cell at the start of the recording, allowing for scripts that adapt based on where they are initiated. This mode offers flexibility, enabling the script to be run from any active cell, making it adaptable to different data layouts and more reusable across various situations.
The drawbacks of relative mode include a higher potential for errors if the script is run from an incorrect starting position, leading to unexpected results. Additionally, the complexity of understanding and maintaining the script is greater, as its operation depends on the starting position.
Conclusion
In conclusion, using Record Actions in Office Scripts provides a streamlined approach to automating routine Excel tasks, making it an invaluable tool for those looking to boost their productivity without extensive programming knowledge. Although this feature simplifies script creation by recording user actions and translating them into a script, it’s important to acknowledge its limitations when handling complex logic or custom conditions.
Do you have any questions about Record Actions in Office Scripts specifically, or Office Scripts in general? Let me know in the comments.
Leave a Reply