Office Scripts (Typescript) in Excel

Office Scripts Purpose

Office Scripts primary concern is to address the automation tasks in the Excel ON THE WEB (Not for the local excel file).

Use Office Scripts in Excel on the web to automate your common tasks.

Office Scripts | Microsoft Docs

On the other hand, since you can program in Office Scripts, it can also handle data in excel in a fast and accurate manner and other stuffs you can imagine.

Also, the same office scripts can be applied to MULTIPLE excel files. So one script for all 😎

Office Scripts Principle & Usage

Principle

As a developer, when I first look at Office Scripts my first impression is "What?😡 Macro again?"

However, thanks God, this is not Macro, but Typescript 👏👏👏, which you can develop base on TypeScript (or JavaScript) experience.

Usage

After talking so much, you may ask how to create a new script, you may follow the instruction below:

• Create a new script by Automate --> New Script

• On the right hand side, you should see a code editor and you can write your code insides the main function

The main function has only one parameter workbook, which you can manipulate the current excel sheet through it.

As you may already notice, the code editor looks very similar to Visual Studio Code. Yes, you are right. 🎉

The Code Editor is based on Visual Studio Code, so if you've used that environment before, you'll feel right at home.

Office Scripts Code Editor environment

No need to adapt a new code editor and a set of new shortcut. 😀

The Office Scripts Code Editor uses the same IntelliSense engine as Visual Studio Code. Intellisense

Most of the keyboard shortcuts for Visual Studio Code also work in the Office Scripts Code Editor. Shortcut

Office Scripts Example & Tips

Example (Read & Write to Excel)

function main(workbook: ExcelScript.Workbook) {
  // Get the "TutorialWorksheet" worksheet from the workbook.
  let worksheet = workbook.getWorksheet("TutorialWorksheet");

  // Get the cells at A1 and B1.
  let dateRange = worksheet.getRange("A1");
  let timeRange = worksheet.getRange("B1");

  // Get the current date and time using the JavaScript Date object.
  let date = new Date(Date.now());

  // Add the date string to A1.
  dateRange.setValue(date.toLocaleDateString());

  // Add the time string to B1.
  timeRange.setValue(date.toLocaleTimeString());
}

It prints the current date in A1 cell and time in B1 cell. Output of the example is as below:

My impression is Office Scripts API itself is very similar to other Excel libraries so if you have used one excel library before, you should find that it is quite familiar.

Tip 1 (Batch Read)

In above example, I would like to point out 2 things:

• When reading large amount of date, getting data one by one by using getRange or getCell will result a performance issue

// Get the cells at A1 and B1.
let dateRange = worksheet.getRange("A1");
let timeRange = worksheet.getRange("B1");
.
.
.

This is because calling getRange or getCell triggers a network call, so calling getRange or getCell multiple times will result multiple network calls, which slows down performance.

The solution is to use getUsedRange, which read all the data in the excel once and put the values in a variable and read data from that variable.

let usedRange = workbook.getActiveWorksheet().getUsedRange();
let usedRangeValues = usedRange.getValues();
// Get values by i (row number) and j (column number)
usedRangeValues[ i ][ j ]

getValues(): (string | number | boolean)[][];

I have done an experiment to check this issue. In the experiment, it loops through 11 x 365 grid to get the values. The code difference and time cost ( 341s vs 0.38s) are as follow:

// Time cost: 341s
let cellValue = metaData.dataWorksheet.getCell(rowNum, 4 + i ).getValue() as string;

// Time cost: 0.38s
let cellValue = usedRangeValues[rowNum][4 + i] as string;

Reference: Read workbook data outside of a loop

Tip 2 (Batch Write)

Similar to tip 1, but this time is about writing data. Instead of writing large amount of data one by one using setValue, you can write all data once by Range.setValues()

setValues(values: (string | number | boolean)[][]): void;

Traps (Power Automate) & Limitation

Traps (Power Automate)

Power Automate can run Office Scripts by using the step Run Script. However, there are times that it runs fine in Excel but prompts ERROR in Power Automate. 😢

Trap 1 ( console.log( Object with circular structure ) )

This following script print out an object

function main(wb: ExcelScript.Workbook) {

    const obj = {
        val: "This is an value",
        wb
    }
    console.log({ obj });
}

Output:

But if we run it in Power Automate, it will show the following error:

We were unable to run the script. Please try again.
Runtime error: Line 7: Converting circular structure to JSON
--> starting at object with constructor 'n'
| property 'm_trackedObjects' -> object with constructor 'e'
--- property 'm_context' closes the circle
clientRequestId: 19eccc72-205f-4fd6-94e7-90c13610e91f

wb is the circular structure which causes the problem.

Trap 2 ( Power Automate CANNOT call external APIs )

You can make call with external API in Excel through Office Scripts but NOT in Power Automate. 😢

Calls to external APIs can be only be made through the Excel application, not through Power Automate under normal circumstances.

There is also other traps with Power Automate.

Limitation

External JavaScript is NOT supported

This one makes your life harder when you need to handle date in Typescript WITHOUT moment.js or any other date library. 😢

No 'any' type in Office Scripts

For more limitation, you may refer this

Conclusion

Office Scripts provide an great alternative to customize (although it only limit to Excel) function in Office 365.