CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsspreadsheetgoogle-sheets-api
Ezequiel Kossoy
Ezequiel Kossoy

Reputation: 3

Count the number of times a value appears in a Spreadsheet column based on criteria on Google Apps Script

I'm trying to create a Spreadsheet App Script that sends emails to different users to notify them to do something.

Of course, I made it send the email but I can't make it send a count of the number of cells the same mail is mentioned in, so that the user will be notified of the amount of tasks they have to work at.

On the other hand, I'm struggling to make the script just send one email to the Users.

This is the code I'm trying to write:

var enviado = 'ok';
function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;
  var numRows = 1000;
  // Rango J2:J
  var dataRange = sheet.getRange(startRow, 9, numRows, 4);
  var data = dataRange.getValues();
  var nextEmail = '';
  for (var i = 0; i < data.length; ++i) {
    if (i < data.length - 1) {
     nextEmail = data[i+1][11] //busca la siguiente fila, en la columna 11
    } 
    else {  //si i es la fila máxima
     nextEmail = ''
    }
    var row = data[i]; 
    var registrado = row[1];
    var name = row[0]
    var emailAddress = row[2];
    var contarMails = 0;
     for (var mails in emailAddress) {
       if (emailAddress[mails][0] == emailAddress) { //Here's where I'm stuck
       contarMails++;
       };
      };
    var mensaje = 
        '<p>¡Hola, '+name+'!</p>'+ 
        '<br>Tenés '+contarMails+' <strong>partida(s) no conciliada(s)</strong> asignada(s) en la Planilla Conciliaciones bancarias.</br><br></br>'+
        '<br>Ingresá al archivo para revisar: </br>'+'<a href="https://docs.google.com/spreadsheets/d/1kzaDQSMDwi_SFKaB4SDVYraeS8wqMjIVl_Tt20wfX1o/">Planilla Conciliaciones bancarias</a>'
    var mailEnviado = row[3];
    if (mailEnviado !== enviado) { // Previene el envío de duplicadios
      var asunto = 'Notificación PNC - Conciliaciones bancarias';
      if (emailAddress !== "" && mailEnviado !== enviado && nextEmail !== emailAddress){ //esto deberia evitar el envio de multiples mails a un mismo destinatario, pero no funciona
        if (registrado == false) {
          MailApp.sendEmail({
             to: emailAddress, 
             subject: asunto, 
             htmlBody: mensaje, 
             noReply: true
            });
          sheet.getRange(startRow + i, 12).setValue(enviado);
         // Asegura que la celda se complete automaticamente por si el script se detiene
        SpreadsheetApp.flush();
        }
      }
     }
   }
}

I appreciate any help

Thanks!

Upvotes: 0

Views: 188

Answers (1)

Cooper
Cooper

Reputation: 64062

Just noticed a problem

var dataRange = sheet.getRange(startRow, 9, sheet.getLastRow()-startRow+1, 4);

The line above creates a range from column I to column L 

  var data = dataRange.getValues();

so data in the line above is only 4 columns wide indices 0,1,2,3

  var nextEmail = '';
  for (var i=0;i<data.length;++i) {
    if (i < data.length - 1) {

So there is no data[i+1][11]

     nextEmail = data[i+1][11] 
    } else { 
      nextEmail = ''
    }

Upvotes: 2

Related Questions