Google Apps Script getNextDataCell() Method Returns Incorrect Results

[SwiftCoder15]

I’m having trouble with a Google Apps Script function that used to work fine but now behaves strangely.

The getNextDataCell method with SpreadsheetApp.Direction.UP parameter seems to ignore the direction completely. I’m trying to locate the final cell containing data in a specific column, but it keeps returning row 1 instead of the actual last populated cell.

Here’s my code that demonstrates the problem:

function findLastDataRow() {
  const worksheetName = 'DailyRecords';
  const worksheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(worksheetName);
  Logger.log('Worksheet: ' + worksheet.getName());

  const targetColumn = "B";
  const finalRow = worksheet.getLastRow();
  const searchRange = worksheet.getRange(targetColumn + "2:" + targetColumn + finalRow);
  Logger.log('Search Range: ' + searchRange.getA1Notation());

  try {
    const finalDataCell = searchRange.getNextDataCell(SpreadsheetApp.Direction.UP);
    Logger.log(`Final data cell found at row: ${finalDataCell.getRow()}`);
    // This incorrectly shows "Final data cell found at row: 1"
  } catch (err) {
    Logger.log(`Error occurred: ${err.message}`);
  }
}

Any ideas why this might be happening?

[DancingFox]

The problem is getNextDataCell starts searching from your range’s starting position, not where it should based on the direction. You set your range as B2:B[lastRow] and search UP, so it starts at B2 and immediately hits the boundary at B1. I’ve run into this same issue with inventory tracking sheets. You need to start searching from the actual last row of the sheet, not row 2. Replace your searchRange with a single cell reference at the bottom: worksheet.getRange(targetColumn + worksheet.getMaxRows()) then use Direction.UP. This searches upward from the very bottom until it finds the last populated cell. You could also try worksheet.getRange(targetColumn + ':' + targetColumn).getValues() and iterate backwards through the array to find the last non-empty value, but it’s slower on large datasets.

[Samuel87]

This is actually how getNextDataCell is supposed to work. The method starts from the first cell in your range and moves in the direction you specify, but it stops at your range boundary no matter what direction you pick. You’re starting from B2 and going UP, so it hits the boundary at B1 right away. I ran into this same issue building a data validation script last year. Start from the bottom of your column instead. Change your searchRange line to something like const startCell = worksheet.getRange(targetColumn + finalRow) then call getNextDataCell with Direction.UP from there. Now you’re moving upward from the bottom until you hit the last cell with data. The method works fine once you start from the right spot.

[MiaDragon42]

yeah, i had that issue too. getNextDataCell() looks from the top of your defined range and not bottom. try changing it to worksheet.getRange(targetColumn + finalRow) and set the direction to UP. that should help you find the last cell with data.