Excel’s Office Script Is Now Generally Available

Imagine downloading a .csv file for your work. What follows is long hours of removing unnecessary parts and then adding formulae and tables that allow you to actually start working with your data and get on with your job. Microsoft is finally addressing all our woes. Excel’s new feature, Office Scripts, takes care of this for you by allowing you to automate these day-to-day tasks. 

The feature, which Microsoft first introduced in November 2019, has now become generally available to anyone with access to Excel on the web (other platforms are yet to be compatible with the novel feature) and any commercial or educational Microsoft 365 license with access to the Microsoft 365 desktop office apps. Once you enter your Excel on the web platform, you can find the Office Scripts feature in the Automate tab on the upper ribbon. 

Recording your work

Office Scripts in Excel allows users to record their actions and then replay them—and edit them if necessary. If you want to document the steps you would have to repeat daily via the Action Recorder, running the script will take care of the entire .csv conversion process. Doing so takes care of time, along with the possibility of errors in these everyday tasks. Office Scripts also allows users to share this script with others, allowing everyone to benefit from such automation. 

THE BELAMY

Sign up for your weekly dose of what's up in emerging technology.

The first part of Office Script is recording actions. Here, the user will use the Action Recorder. As the name suggests, this records the actions the user takes and saves them as a script. These actions entail editing cells, creating tables, formatting changes, and more. The subsequently made script can be then run on other worksheets or workbooks—allowing the user to recreate the recorded actions. 

Microsoft used the following data to explain this better. Enter this data into an Excel (for web) worksheet, starting with cell A1. 


Download our Mobile App



Fruit20182019
Oranges10001200
Lemons800900
Limes600500
Grapefruits900700

The Automate tab, located in the upper banner of the platform, will include the Record Actions button. Now, select cells A2:C2 (i.e. the Oranges row) and press the Stop Recording button. The action of changing A2:C2 to orange is now recorded and saved to the cloud by MS Excel. 

Editing code

MS Excel’s Office Script also includes a Code Editor, which allows users to edit all their scripts recorded via the Action Recorder. Continuing the example above, the user should also be able to see a Details pane on their screen. Upon pressing the Edit button, they should see the following code:

function main(workbook: ExcelScript.Workbook) {

  // Set fill color to FFC000 for range Sheet1!A2:C2

  let selectedSheet = workbook.getActiveWorksheet();

  selectedSheet.getRange(“A2:C2”).getFormat().getFill().setColor(“FFC000”);

}

This details the action of changing the Oranges row to orange. One can also edit this code by adding lines before the closing }. For instance, adding the line:

selectedSheet.getRange(“A3:C3”).getFormat().getFill().setColor(“yellow”);

right before the } and pressing Run would make your workbook look like this:

Source: Microsoft

Through this, one can tweak the auto-generated script to suit their needs better. Moreover, some logical functions, such as conditional statements like if/else and loops, are not directly accessible through the Excel UI. The edit code feature then allows users to add these aspects to their code themselves. 

The scripts can also be shared with other users of the Excel workbook, allowing everyone added to the workbook to view and run the script. 

First, select the script you wish to share. The Code Editor menu for the said script will then show a Share button. 

Source: Microsoft

Sharing the script will allow you to share it with everyone added to the Excel Workbook. The script will also display a blue icon, as shown in the picture above. 

Further applications

The example above details a simple demonstration of Office Script. The feature allows various abilities—such as adding comments to cells, copying images or creating tables—and thus can solve a myriad of problems faced by individuals and companies.

Users can also work with Office Scripts through Microsoft’s Power Automate. Power Automate, which was formerly known as Microsoft Flow, is a service that allows users to create automated workflows between apps and services to synchronise files, receive notifications, collect data, and more. This can be adopted into Office Script—letting users set a specific time to run a script on a schedule. People can use this to create hourly reports or declutter incoming information.

For example, the following image shows a Power Automate flow that begins when a GitHub issue is assigned to a user.  

Source: Microsoft

The flow, as shown above, runs a script that adds the issue to a table in an Excel workbook, and if multiple problems are found, the flow sends an email to remind the user. The following script (on Office Script) allows the user to see additional elements about the issue.

function main(

  workbook: ExcelScript.Workbook,

  issueId: string,

  issueTitle: string): number {

  // Get the “GitHub” worksheet.

  let worksheet = workbook.getWorksheet(“GitHub”);

  // Get the first table in this worksheet, which contains the table of GitHub issues.

  let issueTable = worksheet.getTables()[0];

  // Add the issue ID and issue title as a row.

  issueTable.addRow(-1, [issueId, issueTitle]);

  // Return the number of rows in the table, which represents how many issues are assigned to this user.

  return issueTable.getRangeBetweenHeaderAndTotal().getRowCount();

}

To sum up, Office Script can allow individuals to streamline their workday, especially if it heavily involves running the same day-to-day tasks. Office Script is designed to solve problems through secure, cloud-based solutions, which sets them apart from earlier solutions such as VBA macros.

More Great AIM Stories

Mita Chaturvedi
I am an economics undergrad who loves drinking coffee and writing about technology and finance. I like to play the ukulele and watch old movies when I'm free.

AIM Upcoming Events

Early Bird Passes expire on 3rd Feb

Conference, in-person (Bangalore)
Rising 2023 | Women in Tech Conference
16-17th Mar, 2023

Conference, in-person (Bangalore)
Data Engineering Summit (DES) 2023
27-28th Apr, 2023

3 Ways to Join our Community

Telegram group

Discover special offers, top stories, upcoming events, and more.

Discord Server

Stay Connected with a larger ecosystem of data science and ML Professionals

Subscribe to our Daily newsletter

Get our daily awesome stories & videos in your inbox
AIM TOP STORIES