Skip to main content

Get worksheets

We will explain how to get a specific worksheet using an Office Scripts in conjunction with a sample code.

Get by sheet name​

First of all, it is a method of acquiring the sheet name that has the most opportunities to use.

Method to use​

Workbook Use the getWorksheet method included in the object.

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

Sample code​

Sample
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 from the index​

This is the case when it is obtained by specifying it as the nth sheet.

Method to use​

Workbook Use the getWorksheet method included in the object.

GetWorksheet type definition
ExcelScript.Workbook.getWorksheets(): Worksheet[];

Sample code​

Sample using getWorksheet
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 seats​

It can be achieved with the index mentioned above, but it is a method of obtaining the first and last sheets.

Method to use​

Use the getFirstWorksheet method and getLastWorksheet method included in the workbook object.

getFirstWorksheet, getLastWorksheet type definition
ExcelScript.Workbook.getFirstWorksheet(visibleOnly?: boolean): Worksheet;

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

If you have one optional argument and specify True, return the first and last sheets of the displayed worksheets.

Sample code​

Sample using getFirstworksheet, getlastworksheet
function main(workbook: ExcelScript.Workbook) {
  /** The first worksheet in the workbook */
  const sheets = workbook.getFirstWorksheet();

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