Google app script: find first empty cell in a row

Question

Im learning Google app script while building a dashboard. I'm collecting data from several sheets. My goal is to see by how many rows each sheet grows every week. This gives me insight in how my business is doing.

I can get the length of all the sheets I want to check, however I cant find any code which helps me to find the first empty cell in a specific row. I want to place the length of each sheet there (in my dashboard datacollection sheet) to create a graphs later on.

What I have is:

var range = ss.getRange(2, 1, 1, 1000);
var waarden = range.getValues();
Logger.log(waarden);
var counter = 0
for (var j = 0; j < ss.getLastColumn(); j++) {
    Logger.log(waarden[0][j]);
    if (waarden[0][j] == ""){
        break
    } else {
        counter++;
    }
    Logger.log(counter);
}

This works but I can't image this being the best solution (or quickest solution). Any tips in case my length goes beyond 1000 without me noticing it (although it would take a couple of years to do so in this case ;) )?! Why does getLastColumn() behave so much different than getLastRow()?

Thanks for helping me learn :)

*** edited I figured out I have to use if (waarden[0][j] === ""){ with three = otherwise if my sheet in the row that I use as a check has a length of 0 than this is also counted as empty with two =operators.

ss.getRange(), when used with 4 parameters uses a starting row and column then a row and column count so you are getting the second row starting at the first column and retrieving only 1 row with 1000 cells. If you only want the one row, I would create a variable for the value of ss.getLastColumn() and use that in place of the 1000 as well as in place of the point where you call it. If you are looking for additional Rows, then a complete Answer may be in order. — Karl_S
– Karl_S, Commented Aug 8, 2017 at 13:59

random-parts · Accepted Answer · 2017-08-08 16:27:52Z

2

Try indexOf()

function firstEmptyCell () {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];

  var range = ss.getRange(2, 1, 1, ss.getMaxColumns());
  var waarden = range.getValues();

  // Get the index of the first empty cell from the waarden array of values
  var empty_cell = waarden[0].indexOf("");

  Logger.log("The index of the first empty cell is: %s", empty_cell);
}

This will give you the column position of the empty cell starting from a 0 index. So if the returned index is 4, the column is "E".

edit: As for the getLastColumn() question; you could use getMaxColumns() instead. Updated code to get all columns in the sheet.

edited Aug 8, 2017 at 16:27

answered Aug 8, 2017 at 16:10

random-parts

2,2252 gold badges17 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Google app script: find first empty cell in a row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related