Get a worksheet

This article explains how to use Office Scripts to get a specific worksheet, with sample code.

Get a worksheet by name

First, here’s the approach you’ll use most often: getting a worksheet by its sheet name.

Method to use

Use the getWorksheet method of the Workbook object.

Type definition of getWorksheet

ExcelScript.Workbook.getWorksheet(key: string): Worksheet | undefined;

Specify the sheet name (or the worksheet ID) as the argument. If the specified worksheet doesn’t exist, undefined is returned, so check that you got the worksheet before you use it.

Sample code

Sample using getWorksheet

function main(workbook: ExcelScript.Workbook) {
  /** Target sheet name */
  const sheetname = 'Sheet1';

  /** Target worksheet */
  const sheet = workbook.getWorksheet(sheetname);

  if (!sheet) {
    throw `There is no worksheet named ${sheetname}`;
  }
}

Get a worksheet by index

This approach gets a worksheet by its position, such as the nth sheet.

Method to use

Use the getWorksheets method of the Workbook object.

Type definition of getWorksheets

ExcelScript.Workbook.getWorksheets(): Worksheet[];

All the worksheets in the workbook are returned as an array, so you get the one you want by specifying its index (starting from 0).

Sample code

Sample using getWorksheets

function main(workbook: ExcelScript.Workbook) {
  /** All worksheets in the workbook */
  const sheets = workbook.getWorksheets();

  /** The second sheet in the workbook */
  const secondSheet = sheets[1];
}

Get the first or last sheet

You can also do this with the indexes described above, but here’s how to get the first or last sheet directly.

Methods to use

Use the getFirstWorksheet and getLastWorksheet methods of the Workbook object.

Type definitions of getFirstWorksheet and getLastWorksheet

ExcelScript.Workbook.getFirstWorksheet(visibleOnly?: boolean): Worksheet;

ExcelScript.Workbook.getLastWorksheet(visibleOnly?: boolean): Worksheet;

Each method has one optional argument. If you specify true, the method returns the first or last sheet among the worksheets that aren’t hidden.

Sample code

Sample using getFirstWorksheet and getLastWorksheet

function main(workbook: ExcelScript.Workbook) {
  /** The first worksheet in the workbook */
  const firstSheet = workbook.getFirstWorksheet();

  /** The last visible worksheet in the workbook */
  const lastSheet = workbook.getLastWorksheet(true);
}