Getting Started

Getting started

This section provides details about the basics of Office Scripts including access, environment, script fundamentals, and few basic script patterns.

Content

• Access, environment and editor
• Gentle introduction to script
• Type declarations
• Error handling
• Range basics
• Basic performance considerations
• Note to VBA developers

Environment

Learn about the basics of access, environment, and script editor.

Access

Office Scripts requires admin settings available for M365 administrator under Settings > Org settings > Office Scripts. By default, it is turned on for all users. There are two sub-settings, which the admin can turn on/off.

• Ability to share scripts within the organization
• Ability to use scripts in Power Automation

You can tell if one has access to Office Scripts by opening an Excel file on the web (browser) and seeing if the "Automate" tab appears in the Excel ribbon or not. If you still can't see the Automate tab, check this troubleshooting section.

Availability

Office Scripts is available only in the Excel for the web for Enterprise E3+ licenses (Consumer, E1 accounts are not supported). Excel desktop/MacOS versions are not yet supported.

Scripts and editor

The code editor is built right into Excel for web (online version). If you have used VS Code or Sublime editors, this editing experience will be quite similar. Most of the short-cut keys that VS Code editor uses works in the Office Script editing experience as well. Check out the following short-cut keys handout:

• https://code.visualstudio.com/shortcuts/keyboard-shortcuts-macos.pdf
• https://code.visualstudio.com/shortcuts/keyboard-shortcuts-windows.pdf

Key things to note

• Office Scripts is only available for files stored in OneDrive for Business, SharePoint sites, Team sites.
• The editor doesn't show the script's extension. In reality, these are TypeScript files - but they are stored with a custom extension called .osts.
• The scripts are stored in your own OneDrive for Business folder My Files/Documents/OfficeScripts. You'll not need to manage this folder. For your part, you can ignore this aspect as the editor manages the viewing/editing experience.
• Scripts are not stored as part of Excel files. They are stored separately.
• You can share the script with an Excel file - which in effect means you are linking the script with the file (not attaching it). Whoever has access to the Excel file will also be able to 'view' or 'run' or 'make a copy' of the script. This is a key difference compared to VBA macros.
• Unless you share your scripts, no one else can access it as it resides in your own library.
• Scripts cannot be linked from a local disk or custom cloud locations. Office Scripts only recognizes/runs a script that is on pre-defined location (your OneDrive folder mentioned above) or shared scripts.
• While editing, files are temporarily saved in the browser - but you'll have to 'save' the script before closing the Excel window to save it to the OneDrive location. Don't forget to save the file after edits.

Gentle introduction to script

Office Scripts consists of stand-alone scripts written in TypeScript language, which contains instructions to perform some automation against the Excel workbook. All automation instructions are self-contained within a script and scripts doesn't invoke or call other scripts. All scripts are stored in stand-alone files and stored on the user's OneDrive folder. You can record a new script, edit the recorded script or write a whole new script from the scratch all within a built-in editor interface. The best part of scripts is that they don't need any further setup from users. No external libraries, web pages or UI elements, setup, etc. All of the environment setups are handled by Office Scripts and it allows easy and fast access to automation through a simple API interface.

Some of the basic concepts helpful to understand to edit and navigate around scripts include:

• Basic TypeScript language syntax
• Understanding of main function and arguments
• Objects and hierarchy, methods, properties
• Collection (array): navigation and operations
• Type declarations
• Environment: record/edit, run, examine results, share.

This video and section explains some of the above concepts in detail.

Language - TypeScript

Office Scripts is written using TypeScript language, which is an open-source language that builds on JavaScript, one of the world’s most used tools, by adding static type definitions. As the website says, Types provide a way to describe the shape of an object, providing better documentation, and allowing TypeScript to validate that your code is working correctly.

The language syntax itself is written using JavaScript with additional typings defined in the script using TypeScript conventions. For the most part, you can think of Office Script as written in JavaScript. It is essential that you understand the basics of JavaScript language to begin your Office Scripts journey; though you don't need to be proficient at it to begin your automation journey. Office Scripts' action recorder makes it easy to understand the script statements by adding code comments and allowing you to follow along and make small edits.

Office Scripts APIs, which allows the script to interact with Excel is designed for end-users who may not have much coding background. APIs can be invoked synchronously and you don't need to know advanced topics such as promises or callbacks. Office Scripts API design provides:

• Simple object model with methods, getter/setters
• Easy to access object collections as regular arrays
• Simple error handling options
• Optimized performance for select scenarios helping users to focus on the scenario at hand

main function - script's starting point

Office Scripts' execution begins at the main function. A script is a single file containing one of many functions along with type/interface declarations, variable declaration, etc. To follow along with the script, begin with main function as Excel always invokes the main function when you execute any script. The main function will always have at least one argument (or parameter) named workbook, which is just a variable name identifying the current workbook against which the script is running. One can define additional arguments for usage with Power Automation (offline) execution.

• function main(workbook: ExcelScript.Workbook)

A script can be organized into many smaller functions to aid with code re-usability, clarity, etc. Other functions can inside or outside of the main function - but always in the same file. A script is self-contained and can only use functions defined in the same file. Scripts cannot invoke or call another Office Script.

So, in summary:

• main function is the entry point for any script. When the function is executed, Excel application invokes this main function by providing the workbook as its first parameter.
• It is important to keep the first argument workbook and it's type declaration as it appears. You can add new arguments to main function (See below section) - but do keep the first argument as is.

You can send or receive data from other apps

You can connect the Excel to other parts of your organization by running scripts in Power Automation. See here for more details about running Office Scripts in Power Automation flows.

The way to receiving or sending data from/to Excel is through the main function. Think of it as the information gateway that allows incoming and outgoing data to be described and used in the script. You can receive data from outside in the form of string data type and return any TypeScript recognized data such as string, number, boolean, or any objects in the form of interface you define in the script.

Use functions to organize and re-use code

Objects, hierarchy, methods, properties, collections.

All of the Excel's object model is defined in a hierarchical structure of objects, beginning with workbook object of type ExcelScript.Workbook. An object can contain methods, properties, and other objects within it. Objects are linked to each other using the methods - an object's method can return another object or collection of objects. Using code editor's IntelliSence (code completion) feature is a great way to explore the object hierarchy. You can also use the official reference documentation site to follow along with the relationships among objects.

An object is a collection of properties, and a property is an association between a name (or key) and a value. A property's value can be a function, in which case the property is known as a method. In the case of Office Scripts object model, an object represents a thing in the Excel file that users interact with such as chart, hyperlink, pivot-table, etc. It can also represent the behavior of an object such as the protection attributes of a worksheet.

The topic of TypeScript objects and properties vs methods is quite deep. In order to get started with the script and be productive, you can remember few basic things:

• Both objects are properties are accessed using . dot notation, with the object on the left side of the . and property or method on the right side. Example: hyperlink.address, range.getAddress()
• Properties are scalar in nature (strings, booleans, numbers). E.g., name of a workbook, position of a worksheet, the value of whether the table has a footer or not.
• Methods are 'invoked' or 'executed' using the open-close parenthesis. e.g.,: table.delete(). Sometimes an argument is passed to a function by including them between open close parenthesis: range.setValue('Hello'). You can pass many arguments to a function (as defined by its contract/signature). simply separate them using ,. e.g.,: worksheet.addTable('A1:D6', true). You can pass arguments of any type as required by the method such as strings, number, boolean or even other objects. e.g., worksheet.addTable(targetRange, true), where targetRange is an object created elsewhere in the script.
• Methods can return a thing such as a scalar property (name, address, etc.) or another object (range, chart), or not return anything at all (such as the case with delete methods). You receive what method returns by declaring a variable or assigning to an existing variable. You can see that on the left hand side of statement such as const table = worksheet.addTable('A1:D6', true).
• For the most part, Office Script object model consists of objects with methods that link various parts of the Excel object model. Very rarely you'll come across properties that are of scalar or object values.
• In Office Scripts, an Excel object model method has to contain open-close parenthesis. Usage of methods without them is not allowed (such as assigning a method to a variable).

Let's look at some of the methods of workbook object.

function main(workbook: ExcelScript.Workbook) {
    // Returns a boolean (true or false) setting of whether the workbook is set to auto-save or not. 
    const autoSave = workbook.getAutoSave(); 
    // Get workbook name 
    const name = workbook.getName();
    // Get active cell range object 
    const cell = workbook.getActiveCell();
    // Get table named SALES
    const cell = workbook.getTable('SALES');
    // Get all slicer objects 
    const slicers = workbook.getSlicers();
}

In the above example,

• The methods of workbook object such as getAutoSave() and getName() returns a scalar property (string, number, boolean).
• Where as methods such as getActiveCell() returns another object.
• The getTable() method accepts an argument (table name in this case) and returns a specific table in the workbook.
• The getSlicers() method returns an array (referred to in many places as collection) of all slicer objects within the workbook.

You'll notice that all of the above methods have a prefix of get, which is just a convention used in the Office Scripts object model to convey that the method is returning something. They are also commonly referred to as 'getters'.

There are two other types of methods that we'll now see in the example below:

function main(workbook: ExcelScript.Workbook) {
    // Get a worksheet named 'Sheet1 
    const sheet = workbook.getWorksheet('Sheet1'); 
    // Set name to SALES
    sheet.setName('SALES');
    // position the worksheet at the beginning
    sheet.setPosition(0);
}

In the above example,

• setName() method sets the name to a a new name. setPosition() sets the position to 1st place.
• Such methods modify the Excel file by setting some property or behavior of the workbook. These methods are called 'setters'.
• Typically 'setters' have a companion 'getters'. e.g., worksheet.getPosition and worksheet.setPosition - both of which are methods.

undefined and null primitive types

There are two primitive data types that one must be aware of:

• The value null represents the intentional absence of any object value. It is one of JavaScript's primitive values. It is used to indicate that a variable has no value.
• A variable that has not been assigned a value is of type undefined. A method or statement can also return undefined if the variable that is being evaluated does not have an assigned value.

The above two types crop up as part of error handling and can cause quite a bit of headache if not handled properly. Fortunately, TypeScript/JavaScript language offers an easy way to check if a variable is of type undefined or null easily. We will talk about some of those checks in the below sections, including error handling.

Method chaining

You can use dot notation to connect objects being returned from a method to shorten your code. Sometimes this technique makes the code easy to read and manage. However, there are few things to be aware of. Let's look at the following examples:

Below code gets the active cell and gets the next cell and sets the value. This is a good candidate to use chaining as this code will succeed all the time.

function main(workbook: ExcelScript.Workbook) {
    workbook.getActiveCell().getOffsetRane(0,1).setValue('Next cell');
}

However, the below code which, gets a table named SALES and turns its banded column style on, has an issue.

function main(workbook: ExcelScript.Workbook) {
    workbook.getTable('SALES').setShowBandedColumns(true);
}

What if the SALES table doesn't exist? The script will fail with an error as shown below because the getTable('SALES') returns undefined type (which is a JavaScript type indicating that there is no table such as SALES). Calling setShowBandedColumns method on undefined makes no sense - undefined.setShowBandedColumns(true) and hence the script ends in an error.

Line 2: Cannot read property 'setShowBandedColumns' of undefined

You could use optional chaining operator that provides a way to simplify accessing values through connected objects when it's possible that a reference or method may be undefined or null (which is JavaScript way of indicating unassigned or non-existent object or result) to handle this condition.

function main(workbook: ExcelScript.Workbook) {
    // This line will not fail as the setShowBandedColumns method is executed only if the SALES table is present
    workbook.getTable('SALES')?.setShowBandedColumns(true); 
}

If you wish to handle non-existing object conditions or undefined type being returned by a method, then it is better to assign the return value from the method and handle that separately.

function main(workbook: ExcelScript.Workbook) {
    const salesTable = workbook.getTable('SALES');
    if (salesTable) {
        salesTable.setShowBandedColumns(true);
    } else { 
        // Handle this condition
    }
}

Get object reference

the workbook object is given to you directly in the main function. You can begin to use the workbook object and access its methods directly.

function main(workbook: ExcelScript.Workbook) {
    // Get workbook name
    const name = workbook.getName();
    // Display name to console
    console.log(name);
}

For using all other objects within the workbook, begin with workbook object and go down the hierarchy until you get to the object you are looking for. You can get object reference by fetching the object using get method or by retrieving the collection of objects as shown below:

function main(workbook: ExcelScript.Workbook) {
    // Get the active worksheet.
    const sheet = workbook.getActiveWorksheet();
    // If you want to fetch using an ID or key
    const sheet = workbook.getWorksheet('SomeSheetName');
    // Invoke methods on the object 
    sheet.setPosition(0); 
    
    // Get collection of methods
    const tables = sheet.getTables();
    console.log('Total tables in this sheet: ' + tables.length);
}

Check if an object exists, delete and Add

For creating an object, say with a pre-defined name, it is always better to remove similar object that may exist and then add it. You can do that using this pattern:

function main(workbook: ExcelScript.Workbook) {
  // Name of the worksheet to be added. 
  let name = "Index";
  // Check if the worksheet already exists. If not, add the worksheet.
  let sheet = workbook.getWorksheet('Index');
  if (sheet) {
    console.log(`Worksheet by the name ${name} already exists. Deleting it.`);
    // Call the delete method on the object to remove it. 
    sheet.delete();
  } 
    // Add a blank worksheet. 
  console.log(`Adding the worhseet named  ${name}.`)
  const indexSheet = workbook.addWorksheet("Index");
}

Alternately, for deleting an object that may or may not exist, use this pattern:

    // The ? preceding the delete() will ensure that the API is only invoked if the object exists. 
    workbook.getWorksheet('Index')?.delete(); 

Note about adding an object

To create or insert or add an object such as slicer, pivot-table, worksheet, etc. simply use the corresponding add_Object_ method. Such method will be available on its parent object. Example - addChart() method will be available on worksheet object. The add_Object_ returns the object it creates. Simply receive the returned value and use it later in your script.

function main(workbook: ExcelScript.Workbook) {
  // Add object and get a reference to it. 
  const indexSheet = workbook.addWorksheet("Index");
  // Use it elsewhere in the script 
  console.log(indexSheet.getPosition());
}

Alternately, for deleting an object that may or may not exist, use this pattern:

    workbook.getWorksheet('Index')?.delete(); // The ? preceding the delete() will ensure that the API is only invoked if the object exists. 

Collections

Collections are objects such as tables, charts, columns, etc. that can be retrieved as an array and iterated over for processing. You can retrieve a collection using the corresponding get method and process the data in a loop using one of many TypeScript array traversal techniques such as:

• for or while

• for..of

• forEach, etc.

• Language basics of arrays

This script demonstrates how to use collections used in Office Scripts Excel APIs. It colors each worksheet tab in the file with a random color.

function main(workbook: ExcelScript.Workbook) {

  // Get all sheets as a collection. 
  const sheets = workbook.getWorksheets();
  const names = sheets.map ((sheet) => sheet.getName());
  console.log(names);
  console.log(`Total worksheets inside of this workbook: ${sheets.length}`);
  // Get information from specific sheets within the collection 
  console.log(`First sheet name is: ${names[0]}`);
  if (sheets.length > 1) {
    console.log(`Last sheet's Id is: ${sheets[sheets.length -1].getId()}`);
  }  
  // Color each worksheet with random color
  for (const sheet of sheets) {
    sheet.setTabColor(`#${Math.random().toString(16).substr(-6)}`);
  }
}

Type declarations

Type declarations help users understand the type of variable they are dealing with. It helps with auto-completion of methods and assists in development time quality checks. You can find type declarations in the script in various places including function declaration, variable declaration, IntelliSence definitions, etc.

Examples:

• function main(workbook: ExcelScript.Workbook)
• let myRange: ExcelScript.Range;
• function getMaxAmount(range: ExcelScript.Range): number

You can identify the types easily in the code editor as it appears distinctly usually in a different color. The colon : usually precedes the type declaration.

Writing types can be optional in TypeScript, because type inference allows you to get a lot of power without writing additional code. For the most part TypeScript language is good at inferring the types of variables. However, in certain cases, Office Script will require the type declarations be explicitly defined if the language is unable to clearly identify the type. Also, explicit or implicit any is not allowed in Office Script. More on that below.

ExcelScript types

In Office Scripts, you will use following kinds of types.

• Native language types such as number, string, object, boolean, null, etc.
• Excel API's types. They begin with ExcelScript. e.g., ExcelScript.Range, ExcelScript.Table, etc.
• Any custom interfaces you may have defined in the script using interface statements. See examples of each of them below.

Native language types

In the example below, notice places where string, number, boolean have been used. These are native TypeScript language types.

function main(workbook: ExcelScript.Workbook)
{
  const table = workbook.getActiveWorksheet().getTables()[0];
  const sales = table.getColumnByName('Sales').getRange().getValues();
  console.log(sales);
  // Add 100 to each value
  const revisedSales = salesAs1DArray.map(data => data as number + 100);
  // Add a column
  table.addColumn(-1, revisedSales);  
}
/**
 * Extract a column from 2D array and return result.
 */
function extractColumn(data: (string | number | boolean)[][], index: number): (string | number | boolean)[] {

  const column = data.map((row) => {
    return row[index];
  })
  return column;
}
/**
 * Convert a flat array into 2D array that can be used as range column
 */
function convertColumnTo2D(data: (string | number | boolean)[]): (string | number | boolean)[][] {

  const columnAs2D = data.map((row) => {
    return [row];
  })
  return columnAs2D;
}

ExcelScript types

In the example below, a helper function takes two arguments. The first one is the sheet variable which is of type ExcelScript.Worksheet type.

function main(workbook: ExcelScript.Workbook) {
    const sheet = workbook.getWorksheet('Sheet5');
    const data = ['2016', 'Bikes', 'Seats', '1500', .05];
    addRow(sheet, data);
    return;
}

function addRow(sheet: ExcelScript.Worksheet, data: (string | number | boolean)[]): void {

    const usedRange = sheet.getUsedRange();
    let startCell: ExcelScript.Range;
    // IF the sheet is empty, then use A1 as starting cell for update
    if (usedRange) { 
      startCell = usedRange.getLastRow().getCell(0, 0).getOffsetRange(1, 0);
    } else {
      startCell = sheet.getRange('A1');
    }
    console.log(startCell.getAddress());
    const targetRange = startCell.getResizedRange(0, data.length - 1);      
    targetRange.setValues([data]);
    return;
}

Custom types

The custom interface ReportImages is used to return images to another flow action. The main function declaration includes : ReportImages instruction to tell TypeScript that an object of that type is being returned.

function main(workbook: ExcelScript.Workbook): ReportImages {
  let chart = workbook.getWorksheet("Sheet1").getCharts()[0];
  const table = workbook.getWorksheet('InvoiceAmounts').getTables()[0];
  
  const chartImage = chart.getImage();
  const tableImage = table.getRange().getImage();
  return {
    chartImage,
    tableImage
  }
}

interface ReportImages {
  chartImage: string
  tableImage: string
}

Type assertion (overriding the type)

As the TypeScript documentation states, "sometimes you’ll end up in a situation where you’ll know more about a value than TypeScript does. Usually, this will happen when you know the type of some entity could be more specific than its current type.Type assertions are a way to tell the compiler “trust me, I know what I’m doing.” A type assertion is like a type cast in other languages, but it performs no special checking or restructuring of data. It has no runtime impact and is used purely by the compiler."

You can assert the type using as keyword or using angle-brackets as shown in below code.

function main(workbook: ExcelScript.Workbook) {
  let data = workbook.getActiveCell().getValue();
  // Since the add10 function accepts only number, I have to assert data's type as number - otherwise the script cannot be run.
  const answer1 = add10(data as number);
  const answer2 = add10(<number> data);
}

function add10(data: number) { 
  return data + 10;
}

'any' type in the script

As the TypeScript website states, in some situations, not all type information is available or its declaration would take an inappropriate amount of effort. These may occur for values from code that has been written without TypeScript or a 3rd party library. In these cases, we might want to opt-out of type checking. To do so, we label these values with the any type:

declare function getValue(key: string): any;
// OK, return value of 'getValue' is not checked
const str: string = getValue("myString");

Explicit any is not allowed

let someVariable: any; 
// ^^ This is not allowed ^^

The any type presents challenges to the way Office Script processes the Excel APIs. It causes issues when the variables are sent to Excel APIs for processing. Knowing the type of variables used in the script is essential to the processing of script and hence explicit definition of any variable with any type is prohibited. You will receive a compile-time error (error prior to the running of the script) if there is any variable with any type defined in the script. You will see an error in the editor as well.

]

]

In the above code [5, 16] Explicit Any is not allowed indicates that line # 5 column # 16 defines any type. This helps to locate the error line.

To get around this issue, always define the type of the variable.

If you are uncertain about the type of a variable, one cool trick in TypeScript allows you to define union types. This can be used for variables to hold range values, which can be only of many types.

   let value: (string | number | boolean);
   // ^^ define this as a union type rather than any type ^^
   value = someValue_from_another_source;
   //...
   someRange.setValue(value);

Type inference

In TypeScript, there are several places where type inference is used to provide type information when there is no explicit type annotation. For example, in this code..

let x = 3;
//  ^ = let x: number

the type of the x variable is inferred to be a number. This kind of inference takes place when initializing variables and members, setting parameter default values, and determining function return types.

no-implicit-any rule

A script requires the types of the variables used to be explicitly or implicity defined. If TypeScript compiler is unable to determine the type of a variable (either because type is not defined explicitly or type inference is not possible), then you will receive a compilation time error (error prior to the running of the script). You will see an error in the editor as well.

Following scripts will receive compilation time error as variables are defined without any types and TypeScript cannot determine the type at the time of declaration.

function main(workbook: ExcelScript.Workbook) {
    let value; 
    // ^^ the variable 'value' gets 'any' type as no type is defined. ^^
    value = 10; 
    // ^^Even when a numer type is assigned, the type of 'value' remains any. ^^
    workbook.getActiveCell().setValue(value);
    // ^^ Above line will fail because Office Scripts can't send an argument of type 'any' to Excel for processing. ^^
    return;
}

function main(workbook: ExcelScript.Workbook) {
    let cell; 
    // ^^ the variable 'cell' gets 'any' type as no type is defined. ^^
    cell = workbook.getActiveCell().getValue();
    console.log(cell.getValue());
    // ^^ Office Scripts cannot assign Range type object to 'any' type variable. ^^    
    return;
}

To avoid this, use the following instead. In each case, the type is declared at the time of declaration of the variable.

function main(workbook: ExcelScript.Workbook) {
    const value: number = 10; 
    workbook.getActiveCell().setValue(value);
    return;
}

function main(workbook: ExcelScript.Workbook) {
    const cell: ExcelScript.Range = workbook.getActiveCell().getValue();
    console.log(cell.getValue()); 
    return;
}

Error handling

Office Scripts error can be classified into one of the following categories:

1. Compile time warnings shown in the editor
2. Compile time error that appears when you run - it occurs before the execution begins
3. Runtime error

Editor warnings can be identified using the red underlines in the editor:

At times, you may also see orange warning underlines and grey informational messages. They should be examined closely - though they are not going to cause errors.

It is not possible to distinguish between compile-time and runtime errors as both error messages will look identical. They both occur when you actually execute the script. See below for an example of compile-time error followed by runtime error.

In both cases, you will see the line # where the error occurred. You can then examine the code, fix the issue, and run again.

Below are few best practices to avoid runtime errors.

Check for object existence before deletion

Alternately, for deleting an object that may or may not exist, use this pattern:

    workbook.getWorksheet('Index')?.delete(); // The ? preceding the delete() will ensure that the API is only invoked if the object exists. 

    // Alternate way -- 
    const indexSheet = workbook.getWorksheet('Index');
    if (indexSheet) {
        indexSheet.delete();
    }

Do pre-checks at the beginning of the script

As a best practice, always ensure that all your inputs are present in the Excel file prior to running your script. You may have made certain assumptions about objects being present in the workbook. If those objects don't exist, your script may encounter an error when you read the object or its data. Rather than beginning the processing and erroring in the middle after part of the updates/processing has already finished, it is better to do all pre-checks at the start of the script.

Example:

The following script requires two tables by the name of Table1 and Table2 to be present. Hence it checks their presence and ends the script with return statement if they are not present with an appropriate message.

function main(workbook: ExcelScript.Workbook) {

  // Tables that should be part of the workbook for the script to work:
  const TargetTableName = 'Table1';
  const SourceTableName = 'Table2';

  // Get the table objects
  let targetTable = workbook.getTable(TargetTableName);
  let sourceTable = workbook.getTable(SourceTableName);


  if (!targetTable || !sourceTable) {
    console.log(`Tables missing - Check to make sure both source (${TargetTableName}) and target table (${SourceTableName}) are present before running the script. `);
    return;
  }
  // Continue....

If the verification to ensure input data is present or not is happening in a separate function, it is important to end the script by issuing return statement from the main function.

For example, main function calls inputPresent function to do the pre-checks. The inputPresent just returns a boolean (true or false) indicating whether all required inputs are present or not. It is then the responsibility of the main function to issue return statement (from within same the main function) to end the script immediately.

function main(workbook: ExcelScript.Workbook) {

  // Get the table objects
  if (!inputPresent(workbook)) {
    return;
  }
  // Continue....

}

function inputPresent( workbook: ExcelScript.Workbook): boolean {

  // Tables that should be part of the workbook for the script to work:
  const TargetTableName = 'Table1';
  const SourceTableName = 'Table2';

  // Get the table objects
  let targetTable = workbook.getTable(TargetTableName);
  let sourceTable = workbook.getTable(SourceTableName);

  if (!targetTable || !sourceTable) {
    console.log(`Tables missing - Check to make sure both source (${TargetTableName}) and target table (${SourceTableName}) are present before running the script. `);
    return false;
  }
  return true; 

}

When to abort (throw) the script

For the most part, you don't need to abort (throw) from your script. This is because the script's purpose here is to inform the user that script failed to run due to the absence of input data. Ending the script with an error message is sufficient in most cases and is lot simpler to simply return out of the main function.

However, if your script is running as part of Power Automation, you may want to abort the flow if certain conditions are not met. It is therefore important to not return upon an error but rather issue throw statement to abort the script so that the following step doesn't run.

function main(workbook: ExcelScript.Workbook) {

  // Tables that should be part of the workbook for the script to work:
  const TargetTableName = 'Table1';
  const SourceTableName = 'Table2';

  // Get the table objects
  let targetTable = workbook.getTable(TargetTableName);
  let sourceTable = workbook.getTable(SourceTableName);


  if (!targetTable || !sourceTable) {
    // Abort script
    throw `Tables missing - Check to make sure both source (${TargetTableName}) and target table (${SourceTableName}) are present before running the script. `;
  }
  

As mentioned in the following section, another scenario is when you have several functions involved (main calls functionX which calls functionY, etc.) which makes it hard to propagate the error. Aborting/throwing from the nested function with a message may be easier than returning an error all the way up to main and returning from main with an error message.

When to use try..catch (throw exception)

try..catch technique is a way to detect if an API call failed and handling such error in your script. It may be important to check the return value of an API to verify that it was completed successfully.

Example: consider the following snippet -

function main(workbook: ExcelScript.Workbook) {

  // somewhere in the script, perform a large data update
  range.setValues(someLargeValues);

}

The setValues() may fail resulting in the script failure. You may wish to handle this condition in your code and perhaps customize the error message or break-up the update into smaller units, etc. In that case, it is important to know that the API returned an error and interpret or handle that error.

    try {
        range.setValues(someLargeValues);
    } catch (error) {
        console.log(`The script failed to update the values at location ____. Please inspect and run again.`);        
        console.log(error);      
        return; // End script (assuming this is in main function)  
    }

    // or...

    try {
        range.setValues(someLargeValues);
    } catch (error) {
        console.log(`The script failed to update the values at location ____. Trying a different approach`);        
        handleUpdatesInSmallerChunks(someLargeValues);
    }
    // Continue

Another scenario is when main function calls another function, which in turn calls another function (and so on..), and the API call that you care about happens down in the bottom function. Propagating error up all the way to main may not be feasible or convenient. In that case, throwing an error in the bottom function will be most convenient.

function main(workbook: ExcelScript.Workbook) {
    ...
    updateRangeInChunks(sheet.getRange("B1"), data);
    ...
}

function updateRangeInChunks(
    ...
    updateNextChunk(startCell, values, rowsPerChunk, totalRowsUpdated);
    ...
}

function updateTargetRange(
      targetCell: ExcelScript.Range, 
      values: (string | boolean | number)[][]
    ) {
    const targetRange = targetCell.getResizedRange(values.length - 1, values[0].length - 1);
    console.log(`Updating the range. ${targetRange.getAddress()}`);
    try {
      targetRange.setValues(values);
    } catch (e) {
      throw `Error while updating the whole range: ${JSON.stringify(e)}`;
    }
    return;
}

Warning - Using try..catch inside of a loop will slow down your script. Avoid using this inside of or around loops.

Range basics

Check out the Range Basics before you go further on your journey.

Basic performance considerations

Avoid slow operations in the loop

Certain operations when done inside/around the loop statements such as for, for..of, map, forEach, etc. can lead to slow performance. Avoid the following patterns:

• get* APIs

Read all the data you need outside of the loop rather than reading it inside of the loop. At times, it is hard to avoid reading inside of loops - in such a case, make sure your loop counts are not too large or manage them in batches to avoid having to loop through a large data structure.

Note: If the range/data you are dealing with is quite large (say >100K cells), you may need to use advanced techniques like breaking-up your read/writes into multiple chunks. The following video is really for a small-mid size data setup. For large dataset, refer to advanced data write scenario

• console.log in the loop slows down the script

    // Color each cell with random color
    for (let row = 0; row < rows; row++) {
        for (let col = 0; col < cols; col++) {
            range
                .getCell(row, col)
                .getFormat()
                .getFill()
                .setColor(`#${Math.random().toString(16).substr(-6)}`);
            /* Avoid such console.log in side loop */    
            // console.log("Updating" + range.getCell(row, col).getAddress()); 
        }
    }

• try {} catch () statement

Avoid exception handling for loops. Both inside and outside loops.

Note to VBA developers

TypeScript language differs from VBA both syntactically as well in naming conventions.

Check out the following snippets that achieve the same thing:

Worksheets("Sheet1").Range("A1:G37").Clear

workbook.getWorksheet('Sheet1').getRange('A1:G37').clear(ExcelScript.ClearApplyTo.all);

Few things to call out are:

• You'll notice that all methods will need to have open-close parenthesis to execute. Arguments are passed identically - but some arguments maybe are required for execution (required vs optional).
• The naming convention follows camelCase as opposed to PascalCase convention.
• Methods usually have get or set prefixes indicating whether it is reading or writing object members.
• The code blocks are defined and identified by open/close of curly braces: { }. Blocks are required for if conditions, while, for loops, function definition, etc.
• Functions can call other functions and you can even define functions within a function.

Overall, TypeScript is a different language and there are few similarities between them. However, the Office Scripts API themselves uses similar terminology and data-model (OM) hierarchy as VBA APIs and that should help you navigate around.