Cannot read property from undefined

Question

I have the following code, which works fine and does what it's supposed to do.

However my table has 2 rows at the top which doesn't interest me (they wouldn't match the if clause anyway so it doesn't affect me, just trying to figure this out) so I was trying to tailor my range to simply exclude them.

All I did was change the A1 to A3 inside the getRange and it's throwing the Cannot read property "1" from undefined (referring to the hardcoded 1 in the if statement).

The reason I don't understand why it's undefined is because changing the range like this shouldn't affect it at all, since I'm reducing my range from A1:B6 (6 rows in my sheet with the first 2 being either empty or not needed) to A3:B6, but the first value in the set (A3) should still end up at [0][0] inside 'data'.. unless getValues() messes something up that I don't know about..

var lastRow = sheet.getLastRow();
var myRange = sheet.getRange("A1:B" + lastRow);
var options = new Array();  
var data = myRange.getValues();
for(var i = 0; i < lastRow; i++) {
    if(data[i][1] == region)
    {
     options.push(data[i][0]);
    }
}

Thanks!

Maybe you should take the getRange(row, column, numRows, numColumns) method which is more easy to use and avoid the use of string and int. — Pierre-Marie Richard
– Pierre-Marie Richard, Commented Jun 8, 2017 at 14:08

TheAddonDepot · Accepted Answer · 2017-06-08 14:38:13Z

2

Your bug stems from your for loop; it expects to iterate over 6 rows (i starts at 0 and increments by 1 up to 5), when what you really want is to iterate over 4 rows matching the range A3:B6. So the array only has 4 elements but your for loop tries to reference a 5th element that does not exist.

Simplest solution is to use the Array object's built in array methods (I'm using forEach in this case) to iterate over the items specifically in the range you defined as follows:

var range = sheet.getRange("A3:B"), // you automatically reference the last row with A3:B, no need for A3:B6
    options = [],
    region = '[some-region-value]';

range.getValues().forEach(function(row){
    row[1] == region && options.push(row[0]); // exploit short-circuit mechanism in logical AND operator (in this case the operand on the right, the push, only gets executed if the operand on the left is true)
});

edited Jun 8, 2017 at 14:38

answered Jun 8, 2017 at 14:22

TheAddonDepot

9,0653 gold badges25 silver badges36 bronze badges

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

3 Comments

IForgotEverything Over a year ago

Right on! I totally missed that.. Was too focused on what's going inside the array rather than how many... :))

IForgotEverything Over a year ago

Also, thanks for the simplified version, much less room for error that way! Cheers!

TheAddonDepot Over a year ago

Cool. Make sure to tag this as the "preferred" answer ;).

Anton Dementiev · Accepted Answer · 2017-06-08 14:46:46Z

1

Array indices start with zero. The index of the last element is length of an array (total number of elements) - 1. Modify your 'for' loop like this and it will work

for(var i = 0; i < data.length; i++)

As the previous poster pointed out, you iterate from 0 to what the function getLastRow() returns. Remember, it returns the last row with data in a sheet. It has nothing to do with your array.

answered Jun 8, 2017 at 14:46

Anton Dementiev

5,7344 gold badges23 silver badges36 bronze badges

Collectives™ on Stack Overflow

Cannot read property from undefined

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related