Getting Started with Office Scripts

What are Office Scripts?

Office Scripts in Excel are an automation tool. Something that makes it possible to take a frequent task and have it performed at the touch of a button. Office Scripts are written in the Typescript programming language. Not VBA, the traditional Excel Macro language. Office Scripts operate via the cloud and work with both web and desktop versions of Excel.

Additionally, Office Scripts interact with the larger Microsoft 365 ecosystem via Power Automate.

Where to Find Them

You will find Office Scripts in the Automate tab of the Excel Ribbon.

The saving of a script does not have to be to a workbook. The default is OneDrive, and can also be to SharePoint when a team needs access to a script.

Holding scripts in SharePoint makes deployment to a team straightforward. For an individual, the use of OneDrive means using one script in multiple spreadsheets is also straightforward.

Creating Scripts

Scripts get created via recording actions in Excel, by directly editing Typescript or some combination of both.

Let’s do a walkthrough of creating a simple script.

The Scenario

Let’s assume that a CSV file arrives, and we need to format it as a table to a standard style with a total. To make this example easy to distribute on a blog post without downloads, we’ll start with the data already loaded into Excel.

The Starting Point

Step 1: Start Recording

In Excel select the Automate tab and click on Record Actions.

Now record the formatting of the data as a table. Try:

• Selecting the data
• Use Format As Table to turn it into a table. Pick a style.
• Select the table, and in Table Design, put a tick in Total Row

We’re aiming for something like this:

After pressing record actions on the right-hand side of Excel a record panel would have opened. Ignore it. All you have to do is run through the actions to record.

Just remember to press the stop button when finished.

Once you hit stop, you’ll get a panel like this.

The script has automatically been saved as script.ost in your Office Scripts Folder.

To rename the script to something meaningful, click on the ellipsis and select rename. I’ll keep it simple with “Format Table” for now.

That’s it. A script now exists that will take raw data starting at A1. Convert it to a table and apply a style.

Running Your Script

Go to the Automate tab and select the name of your script.

Then in the Code Editor Panel click on Run.

What does the script look like?

Here’s the code from this example.

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	// Add a new table at extended range obtained by extending right, then down from range A1 on selectedSheet
	let newTable = workbook.addTable(selectedSheet.getRange("A1").getExtendedRange(ExcelScript.KeyboardDirection.right).getExtendedRange(ExcelScript.KeyboardDirection.down), true);
		// Table style applied on table newTable
	newTable.setPredefinedTableStyle("TableStyleLight9");
	// Toggle total row on table newTable
	newTable.setShowTotals(true);
}

Next Steps

Now you know a little more about Office Scripts. Suggested next steps would be.

• Learn the limits of Office Script recording by creating a few more scripts this way.
• Edit the scripts you have in the Code Editor. You’ll start learning TypeScript which opens up the possibilities.
• Look at how Power Automate can interact with Office Scripts. This greatly improves the integration of spreadsheets with all parts of Microsoft 365.

Learn more automation with Power Automate and Office Scripts here.