Search code examples
javascriptarraysgoogle-apps-scripttypeerrorundefined

TypeError: Cannot read property "9" from undefined


I'm looking to delete rows of a given spreadsheet using the code below, but am receiving the following error: "TypeError: Cannot read property "9" from undefined." When testing the For loop and setting it as i = 20, the code works, but as i = data.length, it results in an error. Any ideas what the problem is? Thanks

function cleanUp() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var data = sheet.getDataRange().getValues();

  for ( var i = data.length ; i > 0; i-- ) {
    if (data[i][9] === 'ERROR' || data[i][9] === 'BOUNCED' || data[i][9] === 'NO_RECIPIENT') {
      sheet.deleteRow(i+1);
    }
  }
}

Solution

  • Thanks to @ASDFGerte in the above comments, I found the issue was data[i] in the if statement nested inside the for loop. This essentially returned data[data.length] which was undefined. Instead, this works perfectly:

    for ( var i = data.length-1 ; i > 0; i-- ) {
     if (data[i][9] === 'ERROR' || data[i][9] === 'BOUNCED' || data[i][9] === 'NO_RECIPIENT' || data[i][9] === 'UNSUBSCRIBED') {
      sheet.deleteRow(i+1);
     }
    }