If you’re an Excel user aiming to streamline your workflow and that of your coworkers, Office Scripts are an exciting new tool to explore. These scripts can automate repetitive tasks, making your day-to-day work more efficient and error-free. At the heart of every Office Script is the main() function. Understanding this function is key to mastering Office Scripts and enhancing your Excel automation. In this blog post, we’ll delve into writing some basic scripts and gain a deeper understanding of how the main() function works.
function main() is the heart of Office Scripts
The main() function is the foundation of Office Scripts, originating from TypeScript. Office Scripts are built on TypeScript, a statically typed superset of JavaScript. In TypeScript, defining entry points for applications is common practice to ensure structure and clarity.
The main() function in Office Scripts follows this principle, enabling developers to write scripts in a familiar and organized manner. To better understand how the main() function operates, let’s look at an example. Navigate to the Automate tab and select New Script:
The Office Scripts code editor will appear to the right of your worksheet, displaying a basic pre-filled script that starts with function main():.
function main(workbook: ExcelScript.Workbook) {
// Get the active cell and worksheet.
let selectedCell = workbook.getActiveCell();
let selectedSheet = workbook.getActiveWorksheet();
// Set fill color to yellow for the selected cell.
selectedCell.getFormat().getFill().setColor("yellow");
// TODO: Write code or use the Insert action button below.
}The way to interpret this is that we are using a function, just as you’ve done countless times in Excel. In this particular function, which will always be named main(), we take one parameter called workbook, which represents the current workbook object. This is where the script interacts with the Excel file.
The next two lines retrieve the currently active cell and the worksheet containing that cell. The getActiveCell method returns the cell that is currently selected or active when the script runs, and getActiveWorksheet returns the worksheet containing this active cell.
Finally, the last line of the script changes the background color of the selected cell. It does so by accessing the format settings of the selectedCell, specifically the fill settings, and setting the color to “yellow”.
You’ll also notice comments sprinkled throughout this code using two slashes (//). Comments are a great way to explain code choices, assumptions, and so forth.
Variables inside vs outside function main()
In Office Scripts, when you declare variables inside the parentheses of the main function, these variables act as parameters. They are essentially empty containers waiting to be filled with values when the function is invoked. These values could be anything the function needs to operate effectively, such as specific data or objects.
For instance, consider the previous script provided by Office Scripts where workbook is a parameter. It’s declared inside the parentheses of the main function like this: function main(workbook: ExcelScript.Workbook). Here, the workbook parameter serves as a placeholder that is filled with a specific ExcelScript.Workbook object when the function is called. This design makes the function both flexible and reusable.
When you run an Office Script in Excel, it automatically supplies the active workbook to the script. The Excel application handles the script’s execution context and assigns the active workbook to the ‘main’ function. Thus, the workbook parameter is populated by Excel with the active workbook in which the script is running. This automatic mechanism ensures that the script operates on the correct workbook without needing you to specify which workbook to use manually.
Conversely, variables declared outside the parentheses but within the body of the function are referred to as local variables. In this script, selectedCell and selectedSheet are examples of local variables. These are instantiated within the function to perform specific tasks or store temporary data. For example, selectedCell is assigned the value returned by workbook.getActiveCell(), which retrieves the active cell in the workbook. Similarly, selectedSheet is assigned the value returned by workbook.getActiveWorksheet(), which fetches the active worksheet in the workbook.
These local variables leverage the workbook input parameter to execute specific operations within the function. In this scenario, they use methods of the workbook parameter to access the active cell and worksheet, and then the script sets the fill color of the selected cell to yellow using selectedCell.getFormat().getFill().setColor("yellow").
Parameters within the parentheses are crucial for the function’s flexibility and reusability, as they accommodate different inputs each time the function is called. Meanwhile, the local variables inside the function body utilize these inputs to perform the necessary operations.
If this explanation isn’t clear yet, don’t worry! We’ll explore more examples to help you become more comfortable with this new programming language.
Creating Hello, World
Alright, now that you’ve grasped the basic mechanics of the main() function, let’s try writing some code on our own! A common starting point for programmers is the “Hello World” program. Please go ahead and modify the provided sample Office Script to fill in the following:
function main(workbook: ExcelScript.Workbook) {
// Get the current worksheet
let worksheet = workbook.getActiveWorksheet();
// Set "Hello World" in cell A1
worksheet.getRange("A1").setValue("Hello World");
}
Go ahead and run your script. You should see “Hello, World” added to cell A1, as expected.
The previous script was set up to only populate “Hello World” into cell A1. However, we could have configured this to run across multiple cells by adjusting the parameters in getRange(). We’ll now change the setup to target cells A1:A5 and run the script. As expected, “Hello World” will appear in multiple cells, not just in A1:
function main(workbook: ExcelScript.Workbook) {
// Get the current worksheet
let worksheet = workbook.getActiveWorksheet();
// Set "Hello World" in cells A1-A5
worksheet.getRange("A1:A5").setValue("Hello World");
}
This is pretty cool, but we’re starting to clutter the workbook with all these populated cells. Additionally, modifying the script isn’t straightforward—you need to know exactly where to go and what to do. What if we want to clear the data in cells A1 and revert to adding “Hello World!” only to cell A1, and also color it yellow?
Creating scripts with the Script Recorder
As our script becomes more complex, it might be wise to utilize the script recorder to help automate the writing process. You can find this tool under the Automate tab of the ribbon, under Record Actions.
This feature is very similar to recording a macro with VBA. It is more user-friendly than writing the script from scratch, acting as a point-and-click code generator. However, it can be somewhat messy, as it captures every action you take, including minor errors. Moreover, the code generated might not be as clean and precise as manually written code. Let’s go ahead and give it a try. I’ll point-and-click through the desired actions, and they will appear as a list of steps under “Record Actions.” Once I’m done, I can click “Stop” to see the final code.
This will generate the following script. It’s quite impressive that Office Scripts even added some comments for us, and you’ll notice it even performed actions like autofitting the columns, which was indeed a step I took in the script. As mentioned, this can be a really good starting point, but as your scripts grow in complexity, it’s more likely you will need to modify the results provided by the script recorder.
function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
// Clear ExcelScript.ClearApplyTo.contents from range A1:A5 on selectedSheet
selectedSheet.getRange("A1:A5").clear(ExcelScript.ClearApplyTo.contents);
// Set range A1 on selectedSheet
selectedSheet.getRange("A1").setValue("Hello World");
// Set fill color to #ffff00 for range A1 on selectedSheet
selectedSheet.getRange("A1").getFormat().getFill().setColor("#ffff00");
// Auto fit the columns of range A:A on selectedSheet
selectedSheet.getRange("A:A").getFormat().autofitColumns();
}Creating scripts with additional function main() parameters
You will notice that this script continues to use ExcelScript.Workbook as the sole parameter of the main function. This parameter is essential, as it’s challenging to manipulate a workbook without declaring a variable for it! However, you will see other parameters being added to enhance functionality.
For example, suppose we wanted to predefine the color used in our “Hello World” statement to green. We could do this by adding a color variable to the main function. We will need to declare the variable type—again, similar to VBA—and I’ll set it to green. We’ll then refer to this variable when it’s time to add the fill color:
function main(workbook: ExcelScript.Workbook, color: string = 'green') {
// Get the current worksheet
let worksheet = workbook.getActiveWorksheet();
// Set "Hello World" in cell A1
let cell = worksheet.getRange("A1");
cell.setValue("Hello World");
// Set the background color of cell A1 based on the parameter
cell.getFormat().getFill().setColor(color);
}When you run this script, you’ll see a userform pop-up with green set as the default value. Click OK and the cell will be filled in with green.
This allows you to add a default value for color, but you can further enhance the user interactivity using the main() function. For example, in the script below, I am going to set two variables, message and color, both of which will be strings. Instead of presetting these values, the user will have complete control over them. We’ll use message as the text the user inputs into cell A1 and color as the fill color for that cell.
function main(workbook: ExcelScript.Workbook, message: string, color: string) {
// Get the current worksheet
let worksheet = workbook.getActiveWorksheet();
// Set "Hello World" in cell A1
let cell = worksheet.getRange("A1");
cell.setValue(message);
// Set the background color of cell A1 based on the parameter
cell.getFormat().getFill().setColor(color);
}
When you run the script, a userform popup will appear, allowing you to fill in both fields. The message will display exactly as typed, and the color input is not case-sensitive. There’s much more to explore with Office Scripts, but I hope this introduction helps you grasp the basics of what the main() function entails.
Conclusion
In this post, we’ve explored the basics of the main() function in Office Scripts, from creating a simple “Hello, World!” script to customizing it through direct coding and the script recorder. We’ve tweaked parameters, understood variables inside and outside the main() function, and more. By now, you should have a solid foundation for beginning to automate tasks within Excel using Office Scripts.
Do you have any specific questions about the main() function, or about Office Scripts in general? Please share them in the comments.
Leave a Reply