getLastRow() happily counts formatting, old formulas, and phantom blanks. When you need the real last cell with data in a column — for appends, charts, or "next empty row" math — walk the column from the bottom instead.
What you'll need
- A
Sheetobject - A 1-based column number (defaults to column A)
- Awareness that this reads the full column height once — fine for normal sheets, expensive if you call it in a tight loop across dozens of columns
Why not getLastRow()?
Sheet.getLastRow() returns the last row that ever had content or certain kinds of formatting. Delete values but leave a stray space, a leftover formula, or paint formatting, and you'll append far below your real data. Client sheets pick this up constantly.
How to use this snippet
function getLastDataRow(sheet, column) {
column = column || 1;
var values = sheet.getRange(1, column, sheet.getMaxRows(), 1).getValues();
for (var i = values.length - 1; i >= 0; i--) {
var cell = values[i][0];
if (cell !== '' && cell !== null) {
return i + 1; // 1-based sheet row
}
}
return 0; // empty column
}
Tips:
- Treat
0as "no data" — your next write should start at row 2 if row 1 is headers. - For "last row across several columns," take the
Math.maxofgetLastDataRowper key column (or read a bounded block once and scan). - Prefer a known data range over
getMaxRows()on huge unused sheets if you're hitting time limits.
Example
function appendUnderRealData() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Leads');
var last = getLastDataRow(sheet, 1); // column A is the key
var nextRow = Math.max(last + 1, 2);
sheet.getRange(nextRow, 1, 1, 3).setValues([[new Date(), 'Ada', 'ada@example.com']]);
}
Pair this with header-aware helpers like appendObjectAsRow when you're writing objects instead of raw arrays.
Happy Coding!
