How to Find the Last Row of A Single Column in Google Sheets in Apps Script – Definitive Guide

The getLastRow() method in google sheets returns the end row position of the entire sheet. No method is available to get the last row of a specific column.

This tutorial teaches you how to find the last row of a single column in Google Sheets in Apps script.

The demonstration Google sheet looks like the following.

• The first column has 3 values
• The second column has 6 values, along with one empty value

Find the Last Row of a Single Column Using getNextDataCell() Method

The getNextDataCell() method returns the next cell in the given direction that is the edge of a contiguous range of cells with data in them.

To find the last row of a specific column using the getNextDataCell() method

• Get the last row of the entire sheet using the getLastRow() method
• Get the range of the specific column until the last row. For example, A1:A5 where 5 is the last row of the entire sheet
• If the last row of the current column contains a value, then you can use the last row value
• If the cell is empty, use the getNextDataCell() and pass the UP direction and get that row using getRow(). It will return the index of the row which is not empty

This method works even when there are blank cells in between rows of the specified column.

Code

The following code demonstrates how to find the last row of column A.

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var colName = "A";

  var lastRow = sheet.getLastRow();

  var range = sheet.getRange(colName + lastRow);

  if (range.getValue() !== "") {

    Logger.log(lastRow);

  } else {

    Logger.log(range.getNextDataCell(SpreadsheetApp.Direction.UP).getRow());

  }

Output

3.0

Find the Last Row of a Single Column Using getValues() and FindIndex() Methods

This section teaches you how to use the findIndex() method to find the last row of a specific column.

The getValues() method returns the rectangular grid of values of the range.

Use this method when you want to get the value of the given range and also find the last row of the given range.

• Get the range from the first cell of a given column until the last row of the entire sheet
• Get the values of the range using the getValues() method. It will return an array of values
• Reverse the array using the reverse() method and find the first non empty index
• Subtract it with the last row value. The resultant value will be the last row of a single column in the sheet

This method also works even when there are blank cells in between rows of the specified column.

Code

The following code demonstrates how to find the last row of column B.

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var colName = "B";

  var lastRow = sheet.getLastRow();

  var range = sheet.getRange(colName +"1:" +colName + lastRow);

  var values = range.getValues();

  var lastRowWithData = lastRow - values.reverse().findIndex(c => c[0] != '');

  Logger.log(lastRowWithData);

Output

6.0

This is how you can determine the last row of a single column in sheets using the Google Apps Script.

If you have any questions, feel free to comment below.