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.

The post How to understand function main() in Office Scripts for Excel first appeared on Stringfest Analytics.

The Basics: JavaScript

JavaScript is a programming language originally designed to make web pages more interactive. Over the years, it has become one of the most widely used programming languages in the world. If you’ve ever interacted with a website that updates dynamically without needing to reload the page, chances are that JavaScript was at work behind the scenes.

In essence, JavaScript instructs the computer on what actions to perform. For instance, it can be used to validate form inputs on a web page, create animations, or fetch data from servers without reloading the page. For an Excel user, think of JavaScript as a way to automate actions that would otherwise be manual and time-consuming.

Enter TypeScript

While JavaScript is incredibly powerful, it can sometimes be difficult to manage, especially as your scripts become more complex. This is where TypeScript comes in. TypeScript is a language built on top of JavaScript, which means it includes everything JavaScript does but adds some useful features. The most important of these features is static typing.

Static typing allows you to define the types of variables and functions in your code, which helps catch errors early on. For example, if you declare that a function should only accept numbers as input, TypeScript will alert you if you accidentally try to pass a text string instead. This makes your code more reliable and easier to debug.

For someone new to coding, think of TypeScript as a version of JavaScript that helps you avoid common mistakes and makes your scripts easier to read and understand. It’s like having an extra set of eyes looking over your shoulder, ensuring that you’re doing things correctly.

Bridging the Gap with Office.js

Now, let’s talk about Office.js. Office.js is a library, or a set of tools, that allows you to use JavaScript (or TypeScript) to interact with Microsoft Office applications like Excel, Word, Outlook, and PowerPoint. It serves as a bridge between your code and the Office applications, providing a set of APIs (Application Programming Interfaces) that you can use to manipulate Office documents programmatically.

With Office.js, you can create Office add-ins, which are essentially web apps that run within Office applications. These add-ins can extend the functionality of Office applications, automate repetitive tasks, and integrate with other services and data sources. For instance, you can build an add-in that fetches data from an external database and populates your Excel spreadsheet, or an add-in that sends automated emails through Outlook based on specific triggers.

Simplifying Automation with Office Scripts

While Office.js is incredibly powerful, it can also be complex and requires a good understanding of JavaScript or TypeScript. This is where Office Scripts come in. Office Scripts provide a simplified scripting environment that makes it easy for you to automate tasks in Excel without needing to become a full-fledged programmer.

Office Scripts serve as an accessible layer on top of Office.js, designed to simplify the process of writing and executing scripts. They are written in a streamlined version of TypeScript, which retains the core advantages of TypeScript—such as static typing and enhanced error checking—while stripping away much of the complexity that can be daunting for beginners. This makes it possible for users who are not professional developers to still create powerful scripts that can automate repetitive tasks, manipulate data, and customize Excel functionality to better meet their needs.

The scripting environment in Office Scripts is intentionally user-friendly, providing an intuitive interface that guides users through the process of writing and running scripts. It offers immediate feedback on errors and results, facilitating a smoother learning curve. This real-time feedback loop is particularly valuable for those new to programming, as it allows them to experiment and see the outcomes of their code instantly, thereby accelerating the learning process.

By lowering the barrier to entry, Office Scripts enable a broader range of users to leverage the full power of Excel automation. Whether you are a business analyst looking to automate data processing tasks, a financial professional seeking to streamline reporting processes, or an administrative assistant aiming to reduce the manual effort in managing spreadsheets, Office Scripts provide the tools you need to enhance productivity without requiring extensive programming knowledge. This democratization of automation tools empowers users across various domains to become more efficient and effective in their roles, transforming how tasks are approached and completed in Excel.

Understanding the Lineage

By taking the time to understand how JavaScript, TypeScript, and Office.js contribute to the functionality of Office Scripts, you gain a deeper appreciation for the technology at your fingertips. This knowledge not only helps you use Office Scripts more effectively but also opens the door to exploring more advanced coding techniques and possibilities in the future.

Office Scripts are your gateway to harnessing the power of coding in Excel. They simplify the complex world of JavaScript and TypeScript, providing a user-friendly way to automate tasks and improve productivity. By understanding their roots and how they fit into the broader ecosystem of programming languages and tools, you can confidently start your journey into coding, enhancing your Excel skills and expanding what you can achieve with this powerful tool.

What questions do you have about the lineage from JavaScript to Office Scripts? Let me know in the comments.

The post How to understand the relationships between JavaScript, TypeScript, Office.js and Office Scripts first appeared on Stringfest Analytics.

To summarize, although many Excel automation tasks can be accomplished using either platform, Office Scripts provide a modern, cloud-based solution for automating Excel Online. This enhances ease of sharing and accessibility across various devices, a notable advantage over VBA.

Now, let’s examine a straightforward example. You’ll notice that much of what we’re about to discuss could be achieved using VBA. However, the mechanisms for storage and sharing are somewhat different. Additionally, we have yet to explore the more advanced capabilities of Office Scripts, such as integrating with APIs.

Getting started with sample scripts

For this post, I’m assuming you have Excel for Office 365 and can see the Automate tab in the ribbon of your Excel application. If that’s not the case, consider checking out this post from Microsoft, which details some issues related to organizational access to Office Scripts.

Once you’re in the Automate tab, head to “All Scripts,” then “Sample” to access an expanded menu and review the built-in scripts provided.

One nice first aspect of Office Scripts is its provision of basic scripts for experimentation. These include functionalities such as unhiding all rows and columns, freezing panes, counting empty cells, and more.

To explore these features, select the script titled “Freeze Selection.” Then, click on the three dots adjacent to this sample and choose “Make a Copy.” This action underscores the cloud-first nature of the application, as it directly navigates you to your OneDrive within an Office Scripts folder, where you can save a copy of the script. I will name my new script “Freeze and Format Selection.”

While it’s not necessary to save a sample script for its use, I wanted to do so because we plan to modify it. But first, let’s explore the script as it currently stands and experiment with it. Please select a range of data and then click “Run” to observe the macro in action.

The provided raw script primarily functions to freeze selected panes, though it currently lacks formatting capabilities. We will delve into potential enhancements for this script.

This script is presented as an illustrative example. The subsequent instructions, originally provided by ChatGPT, can be pasted into your script for now. Familiarity with TypeScript, the language utilized by Office Script (a superset of JavaScript), is not required. If you are adept with ChatGPT, achieving the desired outcomes with Office Scripts is typically feasible.

That said, gaining an understanding of the fundamental principles and syntax of TypeScript for Office Scripts is advisable. This guide from Microsoft offers a helpful starting point.

Okay, now proceed to run the script. You will notice that the cells are colored as well! We successfully managed to enhance the sample script with a bit of assistance from ChatGPT.

Creating your own script

The previous example was based on a sample script. However, it’s likely that you’ll need to create your own script, as the samples may not meet your specific requirements. How will this process unfold?

Get started by pasting this dataset into your workbook:

Let’s proceed to create our own Office Script to generate a small report using this dataset. This process will closely resemble the macro recorder functionality in VBA. Head to Automate on the ribbon, then select Record Actions:

To automate the production of this report, I will:

1. Convert the source data into a table.
2. Apply conditional formatting to the Sales column: highlight values greater than 500 in green.
3. Add a total row to the Sales column.
4. Format the Sales column as currency.

You can create your own script by following these steps or use the recording provided below:

When you are done click “Stop” under the Record Actions menu. You should have a script that closely resembles the one below:

You will then have the option to save your script, which could be named something like Format Sales Report. While the script recorder functions in a manner similar to the VBA macro recorder, I find it to be more forgiving. It does not necessarily record every single cell or range selection that might occur accidentally during the process. Additionally, it provides a clear, running log of the steps being applied, lending a more modern feel to the experience.

Improving the script

However, similar to the VBA macro recorder, the script recorder also has significant deficiencies when attempting to build a scalable solution. For instance, what if you later realize you omitted a step? Unless you’re proficient in the language, it can be challenging to know how to modify the script. Moreover, repeatedly executing all the point-and-click steps correctly until you achieve the desired outcome can be exhausting. For example, it appears I forgot to name the resulting table sales.

Or, consider a scenario where the dataset starts in a different row than cell A1 or if it contains more than five rows of data. In such cases, the generated script can become unreliable. It’s somewhat naive to assume that the circumstances under which you might invoke this script again will remain identical to your current setup.

Therefore, our goal now is to revise this script to make it more adaptable. Again, I suspect that as an Excel user, you might not be familiar with the TypeScript being used in this script. Fortunately, having a basic understanding of programming through experience with VBA, Python, etc., can assist you in leveraging ChatGPT to obtain a significantly improved script.

Please replace your current script with the following code and ensure you save the changes.

Now, you should possess a more dynamic solution for generating this formatted sales report. Try appending a few rows to the dataset and relocating it within the workbook. It should function seamlessly without issues.

Sharing and saving your script

Once you’ve finished modifying your script, please proceed back to the Script Details to add a description of the script, modify its sharing settings, and so on. By default, the script is saved to your OneDrive, but you have the option to relocate it, including to a SharePoint site, among other locations.

Office Scripts in Excel are stored in the workbook’s script library by default. This setup ensures that users with access to the workbook can execute or alter the scripts, contingent upon their permissions. To alter the sharing or saving preferences, you should adjust the workbook’s sharing settings on OneDrive or SharePoint. This action directly influences who can access the scripts contained within the workbook.

You’ve written an Office Script… now what?

Congratulations on creating your first Office Script for Excel! This accomplishment marks a significant milestone in automating tasks and boosting your productivity with Excel online.

Currently, there are limited comprehensive resources available for Office Scripts and Excel. However, I strongly recommend exploring the materials available on Microsoft Learn. Should you have any preferred additional resources, feel free to share them in the comments.

A noteworthy aspect for beginners in Office Scripts is understanding their integration with Power Automate. Power Automate is a service that enables the creation of automated workflows between applications and services, facilitating task automation without the need for coding. By integrating Excel Office Scripts with Power Automate, you can set up flows that activate these scripts in Excel Online. This integration enables automated tasks such as data processing or report generation in Excel. I look forward to sharing more insights on Power Automate, Office Scripts, and related topics in future posts.

In the meantime, do not hesitate to reach out with any questions or feedback regarding Office Scripts. I’m eager to hear about your experiences and where you see the greatest potential for their application in your work. Let’s discuss in the comments.

The post How to write your first Office Script for Excel first appeared on Stringfest Analytics.

Download a summary of this article below:

Fortunately, Excel has introduced a wealth of new features to help users spend less time on manual tasks. One notable tool is Power Query, an extract-transform-load (ETL) tool for automating repetitive data transformation tasks. However, Power Query’s utility is mostly confined to data cleaning and transformation, leaving a gap in automation for tasks ranging from adding a table of contents, applying conditional formatting, or even emailing workbooks to colleagues.

“Now, wait a minute,” experienced Excel users might be thinking, “I already have a tool for these types of automation tasks… three letters, ever heard of it? VBA.” While it’s true that VBA can manage many of these tasks, the unfortunate reality is that VBA is not well-suited for today’s cloud-based computing world.

VBA’s primary drawback is its lack of native support for web and modern API integrations. This limitation significantly reduces its utility in environments that rely heavily on cloud services. Additionally, VBA scripts are tied to specific Office files, necessitating manual sharing and security adjustments. This can lead to collaboration and version control challenges, hindering productivity and efficiency in team-based projects.

Office Scripts is designed to overcome these limitations. Built on JavaScript, a language at the heart of web development, Office Scripts offers superior integration with online services and APIs. Its cloud-based nature facilitates easier sharing and collaboration within the Excel on the web environment. This ensures that scripts are accessible and up-to-date across teams, enhancing productivity and addressing the collaboration and version control issues associated with VBA.

Comparing and Contrasting Office Scripts and VBA

To better understand the differences between Office Scripts and VBA, let’s explore a summary table that contrast their features, use cases, and integration capabilities:

Conclusion

While VBA remains a powerful tool for specific desktop-based tasks, the modern cloud computing landscape demands tools like Office Scripts that can leverage web technologies and facilitate team collaboration.

What questions do you have about Office Scripts, whether in general or in relation to VBA? Please share them in the comments. I’m planning to add some posts to the blog here covering Office Scripts, Power Automate, and other tools for web-based Excel automation soon, so don’t hesitate to ask.

The post How to compare Office Scripts and VBA for Excel first appeared on Stringfest Analytics.