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 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. 

Fruit	2018	2019
Oranges	1000	1200
Lemons	800	900
Limes	600	500
Grapefruits	900	700

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:

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. 

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.  

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.