How did getValues() in Google Apps Script return a 'undefined' value?

Question

I was trying to count the number of filled cells in a column and once it is empty, the count is supposed to stop. As such I imposed this condition:

sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1')
numberOfRows = 0
while(sheet.getRange(2,1,sheet.getLastRow()-1,1).getValues()[numberOfRows] != '') {
  numberOfRows ++
}

I turned on the debugger on Google Apps Script and found out that the above while loop did not stop. Upon further testing, I found that the cells that were empty were 'undefined', instead of the conventional empty string that they were supposed to return. Any possible reason that the cells could return an 'undefined' instead of ''?

The debugger has problems. Is there any thing else to suggest that the *loop did not stop*? — TheMaster, Jan 22 '20 at 14:32
That's a terrible loop. You should **never** be calling getValues in a loop. Call it once and loop the contents. https://stackoverflow.com/questions/49020131/how-much-faster-are-arrays-than-accessing-google-sheets-cells-within-google-scri/49020786 — tehhowch, Jan 22 '20 at 14:56
When I paused the debugger, the value for numberOfRows shot up to ~600000. This shows the while loop did not stop — Lai Yao Hao, Jan 22 '20 at 15:06
@tehhowch in reality, even I separate the getValues by putting it in another variable at the top, the result is still the same: the while loop did not stop and the empty cells are still 'undefined' instead of the conventional empty string. — Lai Yao Hao, Jan 22 '20 at 15:10

score 2 · Answer 1 · answered Jan 22 '20 at 14:59

The undefined values are not the issue, the loop is not finishing because each row is an Array, and an Array is never equal to ''.

When the code calls sheet.getRange(2,1,sheet.getLastRow()-1,1).getValues()[numberOfRows] the return value is an Array of columns. If you selected 3 columns in your range, a row would look something like: ['A','B','C'].

To access the value of a column in the row, you need to specify it's index. In this case, you are only selecting a single columnm (['A']), so the index is 0.

Optionally, you might find the code is cleaner if you put the values in a variable first, this is a style choice.

  var values = sheet.getRange(2,1,sheet.getLastRow()-1,1).getValues();
  var numberOfRows = 0;

  while(values[numberOfRows][0] != '' ){
     numberOfRows++;
  }

Rubén · Answer 2 · 2020-01-22T18:17:13.073

The code has some problems:

as mentioned in a question's comment, it's not a good idea to use getValues() in a loop
the following expression can't be visualized in the Google Apps Script editor debugger
```
sheet.getRange(2,1,sheet.getLastRow()-1,1).getValues()[numberOfRows]
```
the above expression returns an Array, not a single value.
the loop doesn't stops when the last row has been reached.

The assumption that getValues() returned undefined is wrong. That's is caused by numberOfRows having a value equal or greater than the number of elements returned by getValues() as JavaScript arrays indexes are zero based.

In order to able to use the Google Apps Script debugger you should add more code lines

var values = sheet.getRange(2,1,sheet.getLastRow()-1,1).getValues();

Regarding the

I was trying to count the number of filled cells in a column and once it is empty, the count is supposed to stop.

I think that a better wording is "find the first empty cell". Anyway, it should stop also when the last row of the the column is reached.

var numberOfRows = 0;
while(numberOfRows < values.length && values[numberOfRows][0] !== ''){
  numberOfRowss++;
}

How did getValues() in Google Apps Script return a 'undefined' value?

2 Answers2