Google apps script can't read the 1001'th row from google spreadsheets

Question

The function bellow returns the first empty row on column A. The sheet became full, I extended it with another 9000 rows, I ran main manually and I got an error, "TypeError: Cannot read property "0" from undefined".

The problem it seems to be that the 1001'th row cannot be read, values[1001] returns nothing, undefined. Am I missing something or am I limited to 1000 rows of data ?

Thank you for reading, here is the code:

function getLastRowNumber(sheetName){
// sheetName: string; returns intege (the last row number based on column A)
  var sheet = getSheet(sheetName);
  var column = sheet.getRange('A1:A'); // THIS IS THE PROBLEM
  var values = column.getValues(); // get all data in one call

  // DEBUG
  Logger.log(values[1001][0])

  var ct = 0;
  while (values[ct][0] != "") {
  ct++;
}
return (ct);
}

EDIT: Solution: use .getRange('A:A'); instead of the 'A1:A' notation. Thank you @tehhowch for the solution.

The 1001th row is actually accessed with array index 1000, since JavaScript arrays are 0-based. — tehhowch
That's right, I can access row 1001, but not 1002, I'm thinking maybe because getValues only returns the first 1000 rows, but i'm not sure what's going on. For now i'm trying to implement a way of deleting rows to make room for more data when getting to this limit. — Feraru Silviu Marian
What happens if you use an actual request for a range, rather than use the implicit A1 notation string "A:A"? e.g. const values = sheet.getSheetValues(1, 1, sheet.getLastRow(), 1); or const values = sheet.getRange("A1:A" + sheet.getLastRow()).getValues();? — tehhowch
@tehhowch By using "A:A" as you mentioned as the argument of getRange it has fixed the problem. I don't know the limit, 30k rows is as far as I tested. — Feraru Silviu Marian
@FeraruSilviuMarian The solution should be posted as an answer. Ref. Can I answer my own question? — Rubén

Feraru Silviu Marian Feraru Silviu Marian · Accepted Answer · 2018-07-20T22:39:37

Posting this answer so people can see it, the solution was provided by @tehhowch.

By using "A:A" as the argument of getRange fixes the problem.

Google apps script can't read the 1001'th row from google spreadsheets

1 Answers