Faster way to find the first empty row in a Google Sheet column

Using indexOf is one of the ways to achieve this:

function firstEmptyRow() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var rangevalues = sh.getRange(1,1,sh.getLastRow(),1).getValues(); // Column A:A is taken
  var dat = rangevalues.reduce(function (a,b){ return a.concat(b)},[]); // 
 2D array is reduced to 1D//
  // Array.prototype.push.apply might be faster, but unable to get it to work//
  var fner = 1+dat.indexOf('');//Get indexOf First empty row
  return(fner);
  }

I have gone through way too many of these implementations of last-row for a specific column. Many solutions work but are slow for large or multiple datasets. One of my use cases requires me to check the last row in specific columns across multiple spreadsheets. What I have found is that taking the whole column as a range and then iterating through it is too slow, and adding a few of these together makes the script sluggish.

My "hack" has been this formula:

=ROW(index(sheet!A2:A,max(row(sheet!A2:A)*(sheet!A2:A<>""))))-1

• Example: Add this to Cell A1, to find the last row in column A. Can be added anywhere, just make sure to manage the "-1" at the end depending on which row the formula is placed. You can also place this is another col, rather than the one you're trying to count, and you don't need to manage the -1. You could also count FROM a starting Row, like "C16:C" - will count values C16 onwards

• This formula is reliably giving me the last row, including blanks in the middle of the dataset

• To use this value in my GS code, I am simply reading the cell value from A1. I understand that Google is clear that spreadsheet functions like read/write are heavy (time-consuming), but this is much faster than column count last-row methods in my experience (for large datasets)

• To make this efficient, I am getting the last row in a col once, then saving it as a global variable and incrementing in my code to track which rows I should be updating. Reading the cell every-time your loop needs to make an update will be too inefficient. Read once, iterate the value, and the A1 cell formula (above) is "storing" the updated value for the next time your function runs

• This also works if the data has filters turned on. Actual last row is maintained

Please let me know if this was helpful to you! If I encounter any issues I will comment on this answer.

Here is a list of what the code should do:

• Give a correct answer if there are no empty cells
• Be fast
• Return the correct row number - not the index number of the array
• Get the correct row number of the empty cell even when other columns in the sheet tab have more rows with data
• Have good variable names
• Answer the original question
• Avoid unnecessary data processing
• Provide comment explanations for what the code does
• Be generic enough to adapt to the readers conditions

This solution uses the array method some which will stop iterating the loop when the condition is true. This avoids wasting time spent looping through every element of the array, and yet uses an array method rather than a for or while loop.

The some method only returns true or false, but there is a way to capture the index number because the some method halts looping when the condition is true.

The index number is assigned to a variable in the scope outside of the array function. This does not slow down the processing.

Code:

function getFirstEmptyCellIn_A_Column(po) {
  var foundEmptyCell,rng,sh,ss,values,x;

  /*
    po.sheetTabName - The name of the sheet tab to get
    po.ssID - the file ID of the spreadsheet
    po.getActive - boolean - true - get the active spreadsheet - 
  */

  /*  Ive tested the code for speed using many different ways to do this and using array.some
    is the fastest way - when array.some finds the first true statement it stops iterating -
  */

  if (po.getActive || ! po.ssID) {
    ss =  SpreadsheetApp.getActiveSpreadsheet();
  } else {
    ss = SpreadsheetApp.openById(po.ssID);
  }

  sh = ss.getSheetByName(po.sheetTabName);
  rng = sh.getRange('A:A');//This is the fastest - Its faster than getting the last row and getting a
  //specific range that goes only to the last row

  values = rng.getValues(); // get all the data in the column - This is a 2D array

  x = 0;//Set counter to zero - this is outside of the scope of the array function but still accessible to it

  foundEmptyCell = values.some(function(e,i){
    //Logger.log(i)
    //Logger.log(e[0])
    //Logger.log(e[0] == "")

    x = i;//Set the value every time - its faster than first testing for a reason to set the value
    return e[0] == "";//The first time that this is true it stops looping
  });

  //Logger.log('x + 1: ' + (x + 1))//x is the index of the value in the array - which is one less than the row number
  //Logger.log('foundEmptyCell: ' + foundEmptyCell)

  return foundEmptyCell ? x + 1 : false;
}

function testMycode() {

  getFirstEmptyCellIn_A_Column({"sheetTabName":"Put Sheet tab name here","ssID":"Put your ss file ID here"})

}

this is my very first post on stackOverflow, I hope to meet all your netiquette needs, so please be nice to me.

considerations

I think the fastest way to find the first blank cell in a column (I couldn't run the performance checks, anyway) is to let the Google engine do sequential tasks itself; it is simply much more efficient. From a programmer's point of view, this translates into NOT using any kind of iteration/loops, i.e. FOR, WHILE, etc. (By the way, this is the same programming approach on database engines - any activity should NOT use loops to find information.)

the idea

1. Go all way DOWN and find the cell in last row of the Sheet (considering all columns),
2. from there, go UP find the first cell containing data in the specified column (selecting the column),
3. shift down one cell to find a free place.

The following function does this in just one command (neglecting the var declarations, here just to improve readability):

code

function lastCell() {    
  var workSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = workSheet.getLastRow();
  var columnToSearch = 1; //index of the column to search. 1 is 'A'.

  workSheet.getRange(lastRow, columnToSearch).activateAsCurrentCell().
    getNextDataCell(SpreadsheetApp.Direction.UP).activate();
  workSheet.getCurrentCell().offset(1, 0).activate(); // shift one cell down to find a free cell
}