Office Scripts (Typescript) in Excel
Tags: excel, microsoft, typescript
Published: 2022-12-15 16:00:00

## 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](https://docs.microsoft.com/en-us/office/dev/scripts/)

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

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1649724756899/Piu1dciTY.png)

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

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1649725222494/2VGcDyR2v.png)

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](https://docs.microsoft.com/en-us/office/dev/scripts/overview/code-editor-environment#scripting-language-typescript-or-javascript)

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](https://docs.microsoft.com/en-us/office/dev/scripts/overview/code-editor-environment#intellisense)
>
>Most of the keyboard shortcuts for Visual Studio Code also work in the Office Scripts Code Editor. [Shortcut](https://docs.microsoft.com/en-us/office/dev/scripts/overview/code-editor-environment#keyboard-shortcuts)

## Office Scripts Example & Tips

### Example (Read & Write to Excel)

```typescript
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:
![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1649727992536/IzMn57ZTu.png)

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
```typescript
// 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.

```typescript
let usedRange = workbook.getActiveWorksheet().getUsedRange();
let usedRangeValues = usedRange.getValues();
// Get values by i (row number) and j (column number)
usedRangeValues[ i ][ j ]
```
```typescript
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:
```typescript
// 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](https://docs.microsoft.com/en-us/office/dev/scripts/develop/web-client-performance?view=office-scripts#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()](https://docs.microsoft.com/en-us/javascript/api/office-scripts/excelscript/excelscript.range?view=office-scripts#excelscript-excelscript-range-setvalues-member(1))

```typescript
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](https://docs.microsoft.com/en-us/office/dev/scripts/tutorials/excel-power-automate-manual#:~:text=Select%20the%20Standard%20tab%2C%20then,a%20OneDrive%20or%20SharePoint%20site.). 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
```typescript
function main(wb: ExcelScript.Workbook) {

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

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1649738226623/qnF_mJHve.png)

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](https://stackoverflow.com/a/4816258) 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.](https://docs.microsoft.com/en-us/office/dev/scripts/develop/external-calls#external-calls-from-power-automate)

There is also other [traps with Power Automate](https://docs.microsoft.com/en-us/office/dev/scripts/testing/power-automate-troubleshooting).

### Limitation
#### External JavaScript is [NOT supported](https://docs.microsoft.com/en-us/office/dev/scripts/develop/javascript-objects#use-of-external-javascript-libraries-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](https://docs.microsoft.com/en-us/office/dev/scripts/develop/typescript-restrictions#no-any-type-in-office-scripts)

For more limitation, you may refer [this]( https://docs.microsoft.com/en-us/office/dev/scripts/develop/typescript-restrictions )

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