Get the last row

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

Methods to use

Use the getUsedRange method of the ExcelScript.Worksheet object, and the getRowIndex and getRowCount methods of the ExcelScript.Range object.

/**
 * @param valuesOnly If true, only cells that contain values are considered used
 * @return The used range (undefined if the entire worksheet is blank)
 */
ExcelScript.Worksheet.getUsedRange(valuesOnly?: boolean): Range;

/**
 * @return The row number of the first cell in the range (starting from 0)
 */
ExcelScript.Range.getRowIndex(): number;

/**
 * @return The total number of rows
 */
ExcelScript.Range.getRowCount(): number;

The getUsedRange method returns the smallest range that includes every cell that has a value or formatting (the used range). If you specify true as the argument, only cells that contain values are included.

The used range doesn’t always start at row 1, so you find the row number of the last row by adding the number of rows to the range’s starting row (note that getRowIndex starts at 0).

Caution

If you call the getRange method without arguments, it returns the range of the entire worksheet, whether or not it contains data. As a result, sheet.getRange().getRowCount() always returns the maximum number of rows in a worksheet (1,048,576), so you can’t use it to get the last row.

Sample code

This sample code specifies a sheet name and gets the row number of the last row that contains data.

If the target sheet isn’t found, it throws an error.

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}`;
  }

  /** Range that contains values */
  const usedRange = sheet.getUsedRange(true);

  if (!usedRange) {
    console.log(`${sheetname} has no data`);
    return;
  }

  /** Row number of the last row in the worksheet */
  const lastRow = usedRange.getRowIndex() + usedRange.getRowCount();

  console.log(`The last row is row ${lastRow}`);
}