function sendMail() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Pending Tasks");
  var lr = sheet.getLastRow();
  var range = sheet.getRange(2,1,lr-1,10).getValues();
  for(var i = 0; i<range.filter(String).length; i  ){
    for(var j = 0; j<range[i].filter(String).length; j  ){
      var timestamp = range[i][0];
      
       
      Logger.log(range[i],[j]);
    
    }
  }
  
}

In below code it print same row 10 time because i have selected 10 column in range if i select 2 column it print row 2 time.

CodePudding user response：

It seems that you are attempting to iterate all non-blank values in a sheet. The for loops will not work correctly because the condition expressions will get the wrong columns in all rows.

Another issue is with Logger.log(range[i],[j]); that will log two values: the full row followed by an array that contains a running integer.

Use forEach(), like this:

function sendMail() {
  const ss = SpreadsheetApp.getActive();
  const values = ss.getRange('Pending Tasks!A2:J').getValues();
  values.forEach((row, rowIndex) => {
    const timestamp = row[0];
    row.forEach((value, columnIndex) => {
      if (value !== '') {
        console.log({ 'timestamp': timestamp, 'value': value, 'row': rowIndex   1, 'column': columnIndex   1 });
      }
    });
  });
}

CodePudding user response：

function sendMail() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Pending Tasks");
  var lr = sheet.getLastRow();
  var range = sheet.getRange(2,1,lr-1,10).getValues();
  var range2 = sheet.getRange(2,1,lr-1,1).getValues();
  for(var i = 0; i<range2.filter(String).length; i  ){
    for(var j = 0; j<range2[i].filter(String).length; j  ){ 
      var name = range[i][9];
      if(name == "Nitish"){
      Logger.log(range[i],[j]);
      }
    }
  }
  
}

it is done by using another range.