Skip to main content

#Acquisition of the final line

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

Method to use​

Use the getrange method contained in the Excelscript.worksheet object and the GetrowCount method included in the Excelscript.range object.

/**
 * @param address Name or address in the range
 * @return ExcelScript.Range Object
 */
ExcelScript.Worksheet.getRange(address?: string): Range;

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

If the getrange method does not specify an argument, the scope of the entire target worksheet is returned.

You can judge the last line by counting the number of rows acquired by using this.

Sample code​

This is a sample code that specifies the sheet name and gets the number of lines in the final line.

If you can't find the target sheet, throw the 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}`;
  }

  /** Number of lines of the final line of the worksheet */
  const lastRow = sheet.getRange().getRowCount();
}