First Empty Row in Google Script with dynamic column range

Question

I'm trying to get the first empty row of a column. The number of this column is determined by a variable.

var values = pnl.getRange('A:A').getValues();      //Returns 2D array
var firstEmptyRow = 4;
while(values[firstEmptyRow]&&values[firstEmptyRow][0]!=""){
  firstEmptyRow++;
}
firstEmptyRow++;

pnl.getRange(firstEmptyRow,1+24*month).setValue(firstEmptyRow);

This works fine, however in this function the range of the column is always the same.

Here is what I'm trying to do

var lastRow = pnl.getLastRow()
var columnRange = pnl.getRange(4,1+24*month,lastRow,1);

var values = columnRange.getValues();      //Returns 2D array?
var firstEmptyRow = 4;
while(values[firstEmptyRow]&&values[firstEmptyRow][0]!=""){
  firstEmptyRow++;
}
firstEmptyRow++;

pnl.getRange(firstEmptyRow,1+24*month).setValue(firstEmptyRow);

I don't get any errors, the code simply doesn't work.

Marios · Accepted Answer · 2021-01-29 09:50:05Z

1

Explanation:

You can use the indexOf method after you have flatten the values array to get the first empty cell of a particular column.

Solution:

Based on your first code snippet:

var startingRow = 4;
var values = pnl.getRange(startingRow,1+24*month,pnl.getLastRow(),1).getValues();      
var firstEmptyRow = values.flat().indexOf('') + startingRow;
pnl.getRange(firstEmptyRow,1+24*month).setValue(firstEmptyRow);

Keep in mind this returns the array index. So the actual row of the cell will be firstEmptyRow+1 because indexes in JavaScript start from 0.

edited Jan 29, 2021 at 9:50

answered Jan 28, 2021 at 20:41

Marios

27.5k9 gold badges40 silver badges59 bronze badges

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

20 Comments

Matteo Marinari Over a year ago

I've read that the method I've implemented is the fastest so I would like to keep it

Marios Over a year ago

@MatteoMarinari A loop that iterates on every element is never faster than the findIndex.

Matteo Marinari Over a year ago

var firstEmptyRow = values.flat().findIndex(c=>c==''); I dont' get what this does and i don't see where in the code i have the formula that gives me the column i want to consider

Matteo Marinari Over a year ago

I've written var firstEmptyRow = values.join().split(",").findIndex(c=>c=='') + 1; and works,I'm not sure why but it seems that in goole sheet, or at least in mine, the method flat() is not provided. Thank you so much, It's the second time you help me today ;)

Marios Over a year ago

@MatteoMarinari could you please post a separate question regarding this issue so other readers can participate in the discussion as well. Stackoverflow does not allow for follow up questions unfortunately. Sorry for the incovenience and your understanding.

|

Collectives™ on Stack Overflow

First Empty Row in Google Script with dynamic column range

1 Answer 1

20 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

20 Comments

Your Answer

Sign up or log in

Post as a guest

Related