How to get next empty cell position in a specific column, Google Apps Script

Question

Im building a apps script to process existing data in a spreadsheet, and build new columns of data based on the existing data. How do I find the next empty cell(not the last cell) in any given column?

Here is the code I initially wrote.

function isempty(x, y){
  alert(SpreadsheetApp.getActiveSheet().getRange(y, x).isBlank());
    return SpreadsheetApp.getActiveSheet().getRange(y, x).isBlank();
}
function nextopencell(column){
  var x = 0;
  var b = 1;
  while(b != 2){
   x++;
   var data = isempty(x, column);
    if(data == true){
      b = 2;
    }
  }
  alert(x);
return x;
}

When there is 5 non empty cells in a column, the first of these being first cell in the column, I expect the return value to be 6, but the function returns 3 regardless of the actual states(not-empty, empty) of cells in a column.
Any help would be greatly appreciated.

Answer 1

Sheet.getRange() expects the row first and the column second, so flip the x & y in your getRange() calls.

function isempty(x, y) {
  alert(SpreadsheetApp.getActiveSheet().getRange(x, y).isBlank());
  return SpreadsheetApp.getActiveSheet().getRange(x, y).isBlank();
}

Also, please read the Apps Script Best Practices and consider ways to improve your script. For example, in the snippet above, you're executing isBlank() twice, but you only need to do so once.

Answer 2

This is a different approach for what you are intending to do. In my code, I'm using the .getDataRange() method. This will get all data in your sheet and then with the for-loop, you can iterate over all rows and specify with your column argument, from what column you want the values.

function nextopencell(column){

  // Get the active spreadsheet and sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  // This represents ALL the data
  var range = sheet.getDataRange();
  var values = range.getValues();

  // Build a new array to fill it with the desire data
  var newColArray = [];

  // Iterate over all rows
  for (var i = 0; i < values.length; i++) {
    // With the column we specify in what column we want to extract the data
    if (values[i][column]) {
       // fill array
       newColArray.push(values[i][column]);
    } else{
       // Show what cells are empty
       Logger.log("This is an empty cell at column " + (column+1).toString() + " and row " + (i+1).toString());
    }
  }

  // This is the built array to fill a new column as you want
  Logger.log(newColArray);

}

For more info, you can check this.