// Sheets · Apps Script

Find the last row with data in a column in Google Sheets.

Get the last populated row of one specific column in Apps Script, not the whole sheet. Covers why getLastRow() gives the wrong answer when columns have different lengths or trailing gaps.

“I need the last row that has data in column C specifically, but getLastRow() keeps returning the bottom of the whole sheet.”

The script

copy · paste · trigger
getLastRowInColumn.gs
Apps Script
// Return the last row that has data in a single column (1-based
// colIndex). Reads the whole column once, then scans upward for the
// first non-empty cell. Returns 0 if the column is empty.
function getLastRowInColumn(colIndex) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var maxRows = sheet.getMaxRows();
  var values = sheet.getRange(1, colIndex, maxRows, 1).getValues();

  for (var row = values.length - 1; row >= 0; row--) {
    if (values[row][0] !== '') {
      return row + 1;  // convert 0-based index to 1-based row
    }
  }
  return 0;
}
Generate yours

Need a variant? Gnaw writes a custom version from one sentence — fields, triggers, edge cases handled.

Walkthrough

Why getLastRow() lies to you

getLastRow() returns the last row that has content anywhere in the sheet. Not in your column. The whole sheet. If column A runs to row 500 and column C stops at row 40, getLastRow() says 500, and your code that wanted the bottom of column C is now off by 460 rows of empty cells. getLastRow lying to you is a rite of passage; everyone meets it once.

The same trap catches getDataRange(), which spans from A1 to the last row and last column with any data. Both functions describe the bounding box of the populated region. Neither one knows or cares that you only asked about one column. There is no built-in getLastRowOfColumn, which is exactly why this question gets asked over and over.

Trailing gaps make it worse. Say column C has values in rows 2-10, then a blank stretch, then a stray value in row 38 someone pasted and forgot. The 'last row with data' is genuinely ambiguous, and you have to decide what you mean: the last non-empty cell (38), or the end of the first contiguous block (10). The script above answers the first. If you want the second, scan downward from the top and stop at the first blank instead.

Reading the column once, then scanning up

The reliable move is one read, then a scan. getRange(1, colIndex, maxRows, 1).getValues() pulls the entire column into a JavaScript array in a single call to the Sheets backend. That single round trip matters: reading cell by cell in a loop is the classic Apps Script performance killer, because every getValue() is a separate call across the network boundary and a column of a few thousand cells can take tens of seconds that way.

Once the values are in memory, the loop walks from the bottom up and returns the first row that isn't an empty string. Apps Script reports empty cells as the empty string '' from getValues(), so that's the comparison to make. The + 1 at the end converts the 0-based array index back to a 1-based sheet row, because the array's element 0 is sheet row 1.

There is a tidier built-in for one common case. sheet.getRange(maxRows, colIndex).getNextDataCell(SpreadsheetApp.Direction.UP) starts at the very bottom of the column and jumps to the last cell containing data, the same as pressing Ctrl+Up from the bottom. It's a single call and reads cleanly. The catch: if the last data cell sits directly against the bottom edge of the sheet's allocated rows, or the column is entirely empty, the jump behaves like the keyboard shortcut and can land somewhere you didn't expect, so the explicit scan is the version I trust when the column might be empty or ragged.

Want a custom version?

Describe your sheet and the rule you want. Gnaw writes the Apps Script — fields, triggers, edge cases — in one shot.

Generate yours Browse BullPack

FAQ

4 questions
Why not just use getLastRow()?
Because it returns the last populated row of the entire sheet, not your column. It only happens to be correct when your column is the longest one in the sheet. The moment another column outruns it, getLastRow() overshoots.
Is reading the whole column slow?
One getValues() over maxRows is a single backend call and is fast even for tens of thousands of rows. What's slow is calling getValue() in a loop, one cell at a time. Read once into an array, then scan the array in memory.
What does it return for a completely empty column?
0. There is no row 0 in Sheets, so 0 is a safe sentinel for 'no data'. Check if (last === 0) before you use the result as a row number.
How do I find the last row by column letter instead of index?
Convert the letter to an index first (A is 1, AB is 28), then call getLastRowInColumn. Or skip the index entirely and read the named column with getRange('C1:C') if a fixed letter is fine.

Related scripts