Office Scripts in Excel: A Complete Guide to Automating Your Workbooks

The Office Scripts are the modern way to automate Excel Available on the web, Windows, and Mac, Office Scripts lets you record actions, edit them as TypeScript code, and run them whenever you want—from a button, on a schedule, or integrated with Power Automate. If you frequently repeat the same tasks in your workbooks, Office Scripts can turn all that tedious work into a self-running process.

Far from being just “another way to do macros”, the Office Scripts combine the best of Excel, the cloud, and automated workflowsYou can share your scripts with your team, connect them to other services, and keep your spreadsheets up to date without having to constantly open files or click buttons.

What exactly are Office Scripts and when does it make sense to use them?

An Office Script is, essentially, a set of instructions that Excel executes for youThese instructions can come from a recording of actions you perform manually or from handwritten TypeScript code. Once created, the script can be reused across different workbooks and worksheets, saving time and reducing human error.

The typical situation where they shine is when You repeat the same sequence of steps every day or every weekOpen a file, clear columns, format, apply formulas, generate pivot tables or charts, etc. All that process, which might take you several minutes each time, can be captured just once and then launched with a single click.

Imagine that every workday You download a CSV file from your accounting system, you open it in Excel and automate the mergingYou remove columns you don't need, convert a range into a table, apply filters, add formulas, and end up with a pivot table on a new sheet. Instead of manually recreating the process, you run your script and in seconds the file is ready, always following the same steps and without any missed steps.

Another great advantage is that You can share that script with your colleagues.They don't need to understand how you built it or know any programming; they just need to run the script to get the same result. This helps standardize processes across finance, reporting, management control, data analysis, and any area where Excel is a daily tool.

Action recorder: automation without programming

The Stock Recorder is the gateway for anyone You want to automate Excel without writing a single line of code.Its operation is very similar to that of a classic macro recorder, but the result is saved as a TypeScript-based script instead of VBA.

When you activate recording, Excel Record the operations you perform on the sheetCell editing, formatting changes, table creation, chart insertion, etc. When you stop recording, everything becomes a script that you can run again in other workbooks, as long as the structure is compatible (sheet names, ranges, tables, etc.).

The great advantage of this approach is that You don't need any prior technical knowledgeYou simply work as you normally would and let the recorder handle converting it into code. Later, if you want to fine-tune the script's behavior, you can open it in the Code Editor and adjust specific details.

In Microsoft documentation you will find guides of this type “Record actions as Office scripts”where good practices, limitations (for example, certain Excel features are not yet logged) and how to interpret what the recorder generates are explained so that it is easier for you to edit it later.

Code editor: maximum power with TypeScript

The code editor is the environment where You go and modify the generated script or where you write a new one from scratch. This is where TypeScript, Excel's object model, and all the advanced capabilities that you can't control with just the interface come into play.

TypeScript is a typed superset of JavaScriptThis means that everything you know about JavaScript is useful, but you also have editor types, checks, and help features that make it easier to detect errors. Some programming knowledge (even at a basic level) is highly recommended to get the most out of Office Scripts.

Every Office script must have a main function called mainThis function receives as its first parameter an object of type ExcelScript.Workbook, which represents the Excel workbook on which the script will operate. Broadly speaking, the structure always follows this form:

function main(workbook: ExcelScript.Workbook) {
// Aquí va tu código
}

The code you place inside The main This will be what is executed when you run the scriptFrom there, you can call other functions you declare in the same file, but nothing outside of a function will be executed automatically. Furthermore, a script cannot directly call another Office script, although it can coordinate with others through Power Automate using input parameters and return values. main.

Official tutorials, such as the one for “Creating and formatting an Excel table”They guide you step by step through the editor: how to interpret the code generated by the recorder, how to add conditionals if/else, loops and auxiliary functions, and how to debug small errors that may arise as you refine your scripts.

Excel object model in Office Scripts

To work effectively with Office Scripts, it's helpful to understand How is the Excel object model organized?This model is very similar to what you see in the interface itself: books, sheets, ranges, tables, charts, etc., all related to each other hierarchically.

At the top is the object Workbookwhich represents the entire book. From there you can access its spreadsheets using methods such as getWorksheets() o getActiveWorksheet()Each sheet, in turn, allows you to access specific cells and ranges.

spreadsheets (Worksheet) contain ranges (Range)Ranges are simply groups of contiguous cells. A range can be a single cell (for example, "B3") or an entire block ("C2:F4"). Most data structures—tables, charts, pivot tables, shapes, images, and other data visualization elements—are built upon ranges.

The Office Scripts APIs are organized into two main namespaces: OfficeScript y ExcelScriptThe APIs directly related to Excel workbooks and worksheets are located in ExcelScript, while those that do not depend on a specific book are located under OfficeScriptThe official reference details all the classes and methods available in both packages.

A very simple example of the object model is a script that retrieves the active leaf and displays its name on the console From the editor:

function main(workbook: ExcelScript.Workbook) {
let sheet = workbook.getActiveWorksheet();
console.log(sheet.getName());
}

Working with ranges: values, formulas, and formatting

Ranges are at the heart of working with Office Scripts, because They are used to manipulate data, formulas, and the visual appearance of cells.To refer to a range, the notation A1 is commonly used, both for individual cells and for blocks.

Each range has three key dimensions: the values ​​contained in the cells, the formulas that are evaluated, and the formatting appliedWith methods such as getValues y setValues You manage the raw data; with getFormulas y setFormulas You work with formulas; and through getFormat you access an object RangeFormat which allows you to change fill colors, fonts, borders, etc.

Internally, The ranks are represented as two-dimensional matricesFor example, a range of 3 rows by 2 columns will be a any[][] with three elements (rows), each with two values ​​(columns). This gives you a lot of flexibility to iterate through and process data using loops or JavaScript/TypeScript array functions.

A typical script might create a small sales report: Set up headers, format them, fill in data, and calculate totals using formulas within a specific range. This is all done by varying the properties of the ranges and using formatting methods to make the result readable and visually appealing.

It is important to be clear that The actual value of a cell does not always match what you see on the screen.. For example, a date displayed For example, “02/05/2021” may internally correspond to the number 44318. What you see depends on the number format, but the underlying type remains a number until you change the value.

When using range values ​​in TypeScript, it's good practice explicitly state the type of data you expectA cell can contain one string, an number or booleanIf you know that a specific range will always contain numbers (for example, a price column), you can cast to it. number[][] so that we can perform calculations without TypeScript complaining.

For example, to get the average price from a column of data, you could declare something like let prices = priceRange.getValues() as number[][]; and then traverse that matrix to sum the values ​​and divide by the number of rows.

Tables, charts, and other data objects

In addition to manipulating individual cells, Office Scripts lets you create and modify richer data structures such as tables, charts, pivot tables, shapes, or images. These objects are stored in collections associated with worksheets or the workbook itself.

Tables are built from ranges of data and allow Apply automatic formatting, filters, and predefined stylesA script can add a table that has headers with addTable(rango, true) and from there, access its columns, rows, and specific properties.

It is also possible to generate graphs programmatically, using methods such as addCharta script can create column, line, pie charts, etc. using a data range as the source. You can even adjust properties such as position on the sheet, size, or chart type.

Similarly, pivot tables, shapes, and images can be managed from scripts, whether to create, update, or delete them. Office Scripts technical documentation It includes in detail all the classes and methods available for each type of object, as well as practical examples of use.

In many real-world scenarios, the combination of automated tables and charts allows you to Generate presentation-ready reports with a single click.without having to manually rebuild the same report structure every time new data arrives.

Collections and access to multiple objects

When an Excel object groups several elements of the same type, it does so through collections that They are represented as arrays in TypeScript. For example, a Workbook has a collection of Worksheet[], which you access through getWorksheets().

The general convention is that methods with plural names, such as getTables(), getCharts() o getColumns()They return the complete collection of objects of that type. Once you have that array, you can use the usual array operations: iterate through it with forEach, access by index, filter, search, etc.

To query a specific object, there are usually singular methods such as getTable(name) o getWorksheet(name)These expect an identifier or name that you will normally have assigned beforehand, either in the script or from the Excel interface. If the element exists, they return the corresponding instance; otherwise, they return null. undefined.

A very common pattern is to check beforehand. if an object already exists in the collectionFor example, you can try to retrieve a table called "MyTable" and, if you find it, work with it; if not, create it from scratch. This approach avoids errors when the script runs on workbooks with slightly different structures.

It's also common practice, when generating reports that must always have the same table or chart name, to first delete the previous object (if it exists) and then create a new one. This avoids name conflicts or remnants of previous runs that could confuse other users.

Adding and deleting Excel objects with scripts

Office Scripts lets you create and delete workbook objects programmatically, which is key when you want to Each execution leaves the book in a clean and controlled stateTo add, methods are used add... in the "parent" object; to delete, simply call delete() in the object itself.

For example, a spreadsheet allows create new tables with addTablepassing the data range and whether it includes headers. That method returns the newly created table, which you can name with setName() to quickly locate it in future executions or other scripts.

If you want to ensure that no remnants of an old table remain, you can Try to retrieve it by name and delete the existing one before creating the new one.This pattern is widely used in processes that run daily with fresh data, where you don't want to keep the history in the same workbook.

Deletion works similarly for other objects, such as spreadsheets, charts, or pivot tables. Simply retrieve the object and call... delete() so that it disappears from the book. From there, you can reconstruct what you need with the new data.

However, it's advisable to be careful with remove critical elements from the bookFor example, deleting the first spreadsheet might confuse users who expect to see it there. A good practice is to thoroughly document the script's behavior and, if possible, work on spreadsheets specifically created for the automated process.

Share scripts and run them from buttons

One of the great strengths of Office Scripts is that They don't just stay in your accountYou can share them with other users in your organization, especially when they are working on shared books in the cloud.

When sharing a script in a shared workbook, All team members with access to the file can view and run it. from the Automate tab. This is very useful for standardizing how certain data is processed and for preventing each person from "inventing" their own version of a key process.

In addition, Excel allows add buttons that launch scripts directly From the ribbon itself or from the script gallery. These buttons are a simple way for someone without scripting knowledge to have highly visible access to the automations they need in their daily work.

From the Code Editor's context menu (accessible via the ellipsis next to the script name) you can Change the script name, delete it, or integrate it with Power AutomateYou can also add it to the ribbon so that it appears as another action in your work environment.

In the documentation you will find sections dedicated to “Sharing Office scripts in Excel” and “Creating a button to run an Office script”, which details how to configure permissions, what users can do with shared scripts, and what security limitations Microsoft 365 applies.

Schedule the automatic execution of scripts

Beyond running them manually, Office Scripts can be schedule them to run automatically at a specific timeTaking advantage of its integration with Power Automate, this is ideal for ensuring that certain books are kept up-to-date without human intervention.

The idea is simple: once you have a script that does the job you need, You connect it to a Power Automate flow. that triggers it at regular intervals (for example, every morning at 8:00). Even if the book is closed, the workflow takes care of opening it in the background, running the script, and saving the changes.

To configure this schedule, open the script in the Code Editor and look for the section of “Scripting”From there, you log in with your Excel account through Power Automate, define the execution frequency, and click on "Create flow." From that moment on, the script will run automatically according to the schedule you have defined.

This approach greatly reduces friction in tasks such as update daily reports, or recalculate models that depend on external values. Once the flow is set up, you can forget about manually launching the process.

However, it's important to keep an eye on things at the beginning. the flow executes without errorsIf it fails because a sheet name is changed, a table is deleted, or the workbook structure is modified, Power Automate will reflect those errors, and you can review them in the run logs.

Connecting to Power Automate and other services

Power Automate is Microsoft's service for creating automated workflows between applications and servicesIntegration with Office Scripts allows your scripts to not depend exclusively on actions within Excel, but to be triggered by external events and combined with other tools.

With this combination you can, for example, Run a script every time an email arrives with a specific attachment.For example, Office Scripts can update a workbook when an item is added to a SharePoint list or trigger data processing when an approval workflow begins. This makes Office Scripts one more piece within a much broader automation ecosystem.

Data is exchanged between the Power Automate flow and the script through the input parameters and the value returned by the function mainIn other words, you can pass information from the flow to the script (for example, a file name or a target range) and return a result that the flow will use later (such as a calculated value or a state).

Microsoft provides specific tutorials, such as “Running Office Scripts with Power Automate”This section details the available actions, the connector structure, and examples of typical use cases. It is especially useful for those coming from the world of Excel who want to make the leap to cross-application automation.

In practice, many users combine Office Scripts with Power Query and other Power Platform componentsFor example, Power Query to import and transform data, Office Scripts to apply specific business logic to the workbook, and Power Automate to orchestrate the entire flow in the background.

Availability, script execution, and troubleshooting

To use Office Scripts you need a compatible Microsoft 365 license and that your organization has not disabled this feature since Admin Center or through group policies. Additionally, Excel on the web requires that third-party cookies be enabled.

In some Windows environments it is necessary install WebView2 for certain parts of the experience to function correctly. It's also important to ensure you're not using an external or guest account on the tenant, as that can restrict access to scripts.

To run a script, you usually go to the tab Automate > View scriptsThere you'll see the gallery with the latest scripts. Selecting one will open it in the Code Editor, where you can click the "Run" button and see a brief notification while the script is processing.

If something goes wrong during execution, The errors appear in the Code EditorA "View logs" button is available, which displays a summary of the problem at the bottom of the panel. These are often due to differences between workbooks: sheet names that have changed, tables that no longer exist, altered ranges, etc.

Keep in mind that Not all Excel features are supported by the recorder or the APIs. at all times. Microsoft is gradually expanding compatibility, but certain actions may appear marked as "not supported" in the recording panel and will not be included in the script.

If you need additional help, the Excel technical community and Microsoft support forums are places where You can share your experiences, questions, or problems with Office ScriptsMany users comment on how they combine these tools with VBA, Power Automate, Power Query, and other components to get more out of their data.

Overall, Office Scripts offers a very powerful way to Take Excel automation to the next levelIntegrating modern scripting, cloud execution, task scheduling, and cross-application flows. While there is a learning curve, especially if you're coming from VBA or have never used TypeScript, the time invested translates into faster processes, fewer errors, and teams working more consistently on the same data.