How to extract individual array value from filtered row in app script for google sheets

Question

I am trying to extract individual array values from a Google Sheets Row which was filtered in app script. I have successfully filtered data/rows based on conditions of a empty cells in a column. But now, I keep getting the following error: TypeError: Cannot read property '0' of undefined at selectRecords(SendNotifications:21:22)

Please note that I want to access all filtered rows all at the same time. For example, if I have Row 1 and 2, and if I want all 5 columns within these two rows, my result should be:

[row: 3][col 1][col 2][col 3][col 4][col 5]

[row: 7][col 1][col 2][col 3][col 4][col 5]

[row: 8][col 1][col 2][col 3][col 4][col 5]

Thank you.

/* Global Prameters */
var ssss = SpreadsheetApp.getActiveSheet();
var rows = ssss.getDataRange();
var numRows = rows.getNumRows();
var values = rows.getValues();  
var columnToCheck = ssss.getRange("J:J").getValues();
var columnToCheck2 = ssss.getDataRange().getValues();
var lastData = getLastRowSpecial(columnToCheck2);
var lastRow = getLastRowSpecial(columnToCheck);

function selectRecords() {    
var dataRange = ssss.getRange(3,1, lastRow, ssss.getLastColumn()); // This 
 // range is to select column 9
var headerRowNumber = 3;
var dataValues = dataRange.getValues();

for (var i=2; i < lastData; i++){
  var row =dataValues[i];
  var headerRowNumber = 2;  
  var mydata1 = dataValues.filter(row => row[9] == ""); // This returns 
   // all row contents where column 9 is empty.
  var getData = row[0] + "\n" + row[1] + "\n" + row[2] + "\n" + row[3] + 
                "\n" + row[4] + "\n" + row[5];  // <<<< This is where 
                                                        error generates.
var email = row[13];
var test = row[9];
var message = getData; // Second column
var subject = 'Sending emails from a Spreadsheet';
MailApp.sendEmail(emailAddress, subject, message);
} Logger.log(getData);
}   

function getLastRowSpecial(range){ // Function to limit getDataRange() or 
                   // get data without looping through the entire sheet
var rowNum = 0;
var blank = false;
for(var row = 0; row < range.length; row++){
  if(range[row][0] === "" && !blank){
    rowNum = row;
    blank = true;
  }else if(range[row][0] !== ""){
    blank = false;
  };
};
return rowNum;
};

Answer 1

Issues/Solution:

• Counter i starts at 2, say last row and hence, lastData is 5. dataValues is a 2d array starting from 3 and ending at 5 => 3 rows of data with indexes 0, 1 and 2. But i starts at 2 and ends at 5. As soon as i hits 3, var row =dataValues[i];> dataValues[3] will be undefined. row is undefined and undefined is not a array. So, you

Cannot read property '0' of undefined

• Array.filter doesn't filter in place. Instead it returns the filtered array. Use the filtered array.

• getDataRange() only returns the data upto the lastRow and not the whole sheet. So usually, it is not needed to getLastRowSpecial

Sample script:

function selectRecords() {
  const ss = SpreadsheetApp.getActiveSheet();
  const dataRange = ss.getDataRange();
  const headers = 2;
  const dataValues = dataRange
    .offset(headers, 0, dataRange.getNumRows() - headers)//offsetting the headers from the whole range
    .getValues();

  dataValues
    .filter(row => row[9] == '') //filtered data where row[9] is empty
    .forEach(row => {
      //forEach filtered row do>
      let message =
        row[0] +
        '\n' +
        row[1] +
        '\n' +
        row[2] +
        '\n' +
        row[3] +
        '\n' +
        row[4] +
        '\n' +
        row[5]; 
      let email = row[13];
      let subject = 'Sending emails from a Spreadsheet';
      MailApp.sendEmail(email, subject, message);
      Logger.log(`${subject}: ${message} sent to ${email}`);
    });
}