I am trying to find an empty cell in a specific column in google sheets. I am familiar with getLastRow(), but it returns the last row in whole sheet. I want to get the first empty cell in a specific column. So, I used a for loop and if. But I don't know why it is not working. The problem is that for loop does not return anything. I am getting 10 rows of the column H (position 8) in the sheet test (line2). first 5 rows already have content. Data will be added to this cell later using another code. so, I want to be able to find the first empty cell in this column to be able to put the new data.

var sheettest = SpreadsheetApp.getActive().getSheetByName("test");
var columntest = sheettest.getRange(4, 8, 10).getValues();  
    for(var i=0; columntest[i]=="" ; i++){
      if (columntest[i] ==""){ 
        var notationofemptycell = sheettest.getRange(i,8).getA1Notation();
        return notationofemptycell 
      }

  }

As I said, I want to find the first empty cell in that column. I defined the for loop to go on as long as the cell is not empty. then if the cell is empty, it should return the A1 notation of that cell. It seems the for loop does go on until it find the empty cell, because I get no definition for the var "notationofemptycell" in debugging.

I am trying to find an empty cell in a specific column in google sheets. I am familiar with getLastRow(), but it returns the last row in whole sheet. I want to get the first empty cell in a specific column. So, I used a for loop and if. But I don't know why it is not working. The problem is that for loop does not return anything. I am getting 10 rows of the column H (position 8) in the sheet test (line2). first 5 rows already have content. Data will be added to this cell later using another code. so, I want to be able to find the first empty cell in this column to be able to put the new data.

var sheettest = SpreadsheetApp.getActive().getSheetByName("test");
var columntest = sheettest.getRange(4, 8, 10).getValues();  
    for(var i=0; columntest[i]=="" ; i++){
      if (columntest[i] ==""){ 
        var notationofemptycell = sheettest.getRange(i,8).getA1Notation();
        return notationofemptycell 
      }

  }

As I said, I want to find the first empty cell in that column. I defined the for loop to go on as long as the cell is not empty. then if the cell is empty, it should return the A1 notation of that cell. It seems the for loop does go on until it find the empty cell, because I get no definition for the var "notationofemptycell" in debugging.

• javascript
• for-loop
• google-apps-script
• google-sheets

Share Improve this question Follow edited Jun 7, 2019 at 1:44 Wicket 38.5k99 gold badges7878 silver badges193193 bronze badges asked Dec 5, 2017 at 21:31 Jubin RossJubin Ross 6311 silver badge33 bronze badges

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 2

This may be faster:

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

  /*  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 -


  */

  sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test');
  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;

  values.some(function(ca,i){
    //Logger.log(i)
    //Logger.log(ca[0])

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

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

  return x + 1;
}

You need to loop through the length of columntest. Try this:

function myFunction() {
  var sheettest = SpreadsheetApp.getActive().getSheetByName("test");
  var lr=sheettest.getLastRow()
  var columntest = sheettest.getRange(4, 8, lr).getValues();  
    for(var i=0; i<columntest.length ; i++){
      if (columntest[i] ==""){ 
        var notationofemptycell = sheettest.getActiveCell().getA1Notation();
        Logger.log( notationofemptycell)
        break;//stop when first blank cell on column H os found.
      }
  }
}