Google Apps Script - TypeError: Cannot read property "0" from undefined

Question

I keep getting an "undefined error" on this line of code:

      while (agentSheetValues[j][0] != "") {

... even though if I use Logger.log(agentSheetValues[j][0]) it outputs the variable, so I know it IS defined. I've also tried replacing the while loop with a for loop and have the same problem; what's going on?

Full code below, thank you.

  var resultSS = SpreadsheetApp.openById('someKey');
  var agentSheet = resultSS.getSheetByName('Agents');
  var agentSheetRows = agentSheet.getDataRange();
  var agentSheetValues = agentSheetRows.getValues();

  var agentSheets = [];

  var j = 1;

  while (agentSheetValues[j][0] != "") {
    var sheetKey = agentSheetValues[j][1];
    agentSheets.push(sheetKey);
    j++
  }

After var j = 1 add the line: console.log(agentSheetValues) and make sure that array is not empty or null. — Jeremy J Starcher

Henrique G. Abreu Henrique G. Abreu · Accepted Answer · 2014-05-04T00:38:18

You should consider in your loop that your array of data may end before you reach an empty value. Try this:

var resultSS = SpreadsheetApp.openById('someKey');
var agentSheet = resultSS.getSheetByName('Agents');
//getDataRange returns from A1 to the last column and row WITH DATA.
//you'll not get the whole sheet. Blank rows and columns after the end are ignored
var agentSheetDataRange = agentSheet.getDataRange();
var data = agentSheetDataRange.getValues();

var agentSheets = [];
//I'm assuming you started 'j = 1' on purpose, to skip the header row
//Also, if you're sure you do not have another column with more data than A
// you could drop the::  && data[j][0] != ''
for( var j = 1; j < data.length && data[j][0] != ''; ++j )
  agentSheets.push(data[j][1]);

Google Apps Script - TypeError: Cannot read property "0" from undefined

1 Answers