How to Get the Last Row of a Column in Google Sheets Using Google Apps Script

This post helps you get the last row of the specified Google sheet column and also the row of the last occurrence of a value in a column.

During a project for a client, I needed to find the value of the last row in a specific column. Although Google Apps Script provides a function to get the last row of a sheet, it does not have a function to get the last row of a specific column. I discovered a function online, made some adjustments, and found it to be incredibly useful in many of my projects. Several fellow Apps Script developers have asked the same question, so I guess this function may be useful. Furthermore, I will also discuss another function that uses the same principle to get the row of the last occurrence of a value in a specific column.

Getting the last row of a specific column

The function I’m going to explain first is designed to get the last row of a given column in a given sheet. Here is the function:

function lastRow(column, sheet_name, ss) {
  var lastRow = ss.getSheetByName(sheet_name).getMaxRows();
  var values = ss.getSheetByName(sheet_name).getRange(column + "1:" + column + lastRow).getValues();

  for (; values[lastRow - 1] == "" && lastRow > 0; lastRow--) {}
  return lastRow;
}

This function takes three parameters:

1. column – The column for which you want to find the last row
2. sheet_name – The name of the sheet where this column is located
3. ss – The Spreadsheet object where the sheet is located

The function first gets the maximum number of rows in the specified sheet using the getMaxRows() function. It then gets the values of all the cells in the specified column from the first row to the last row using the getRange() and getValues() functions.

The function then loops backward from the last row to the first row until it finds a row that is not empty. It returns the number of this row, which is the last row in the specified column that contains data.

To use this function, you need to pass the column letter, the sheet name, and the Spreadsheet object as arguments. Here is an example:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var lastRow = lastRowOfColumn("A", "Sheet1", ss);
Logger.log("The last row is: "+ lastRow)

Getting the last row where a value occurs in a column

The lastOccurrenceRow function finds the last row number where a specific word occurs in a given column of a Google Sheet. It searches backward from the last row to the first row until it finds the word or reaches the first row. It returns the row number or a value indicating that the word was not found.

function lastOccurrenceRow(column, sheet_name, ss, searched_word) {
  var lastRow = ss.getSheetByName(sheet_name).getMaxRows();
  var values = ss.getSheetByName(sheet_name).getRange(column + "1:" + column + lastRow).getValues();

  for (; values[lastRow - 1] != searched_word && lastRow > 0; lastRow--) {}
  return lastRow;
}

These two functions have proven to be very useful in my projects, and I hope they will be the same for you.

Joseph Asinyo

Google Workspace Developer

I’m Joseph. I love building applications and writing Google scripts to automate Google Workspace for greater productivity. Learn more about me.

• YouTube
• LinkedIn
• Twitter