Finding the first empty cell in a specific column using Apps Script

Other Questions Google Sheets
1

I’m trying to locate the first empty cell within a particular column range, but my current script seems to have some issues with targeting the correct column.

Here’s what I’ve been working with:

function findFirstBlankCell() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  spreadsheet.setActiveSelection(spreadsheet.getRange("F" + locateEmptyCell()))
}

function locateEmptyCell() {
  var activeSheet = SpreadsheetApp.getActiveSheet();
  var dataRange = activeSheet.getDataRange();
  var cellValues = dataRange.getValues();
  var startRow = 30;
  for (var currentRow = 30; currentRow < cellValues.length; currentRow++) {
    if (!cellValues[currentRow].join("")) break;
  }
  return (currentRow + 1);
}

The problem is that when I execute this code, it selects a cell in column F but determines emptiness based on column B data instead of column F. For example, if I’m working with rows 15-25 and column B has data in rows 15-17, the script picks F18 even though F15 might be completely empty.

I also tried a modified version:

function findFirstBlankCell() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  spreadsheet.setActiveSelection(spreadsheet.getRange("C" + locateEmptyCell()))
}

function locateEmptyCell() {
  var activeSheet = SpreadsheetApp.getActiveSheet();
  var finalRow = activeSheet.getLastRow();
  var targetRange = activeSheet.getRange(32, 3, finalRow - 32, 1);
  var cellValues = targetRange.getValues();
  var startRow = 32;
  
  for (var index = 0; index < cellValues.length; index++) {
    if (!cellValues[index].join(" ")) break;
  }
  return (startRow + 1);
}

But this version always picks row 33, probably because I’m starting at row 32 and adding 1 to the return value. How can I fix this to properly check the intended column?

2

the issue is in how you’re checking emptiness. instead of cellValues[currentRow].join(""), try checking specifically with cellValues[currentRow][5] for column F. and you should return currentRow + 1 without the extra parentheses.

3

Your second approach is close, but there are two issues causing problems. First, you’re using cellValues[index].join("") which won’t work for empty cells. Instead, check specifically for cellValues[index][0] === "" to target the correct column. Second, your return value logic is off; you’re adding 1 to startRow instead of the current index. Here’s what works better:

function locateEmptyCell() {
  var activeSheet = SpreadsheetApp.getActiveSheet();
  var finalRow = activeSheet.getLastRow();
  var targetRange = activeSheet.getRange(32, 3, finalRow - 31, 1);
  var cellValues = targetRange.getValues();
  
  for (var index = 0; index < cellValues.length; index++) {
    if (cellValues[index][0] === "") {
      return 32 + index;
    }
  }
  return finalRow + 1;
}

This way, you’re checking column C specifically for emptiness.