GSheets Script email for each row containing repeated data How to limit it to one row only?

Question

How to send an email once, if the row is repeated in the sheet where this is bringing it from?

If I put the MailApp.sendEmail part outside the loop and within a certain period of time the user changes two products' statuses, it only sends one email, containing one of the product's updated status. If I put MailApp.sendEmail within each condition met, it sends one email for each row.

Below is an image of what the sheet looks like:

There are usually multiple rows of data for one product and there should be only one email sent.

var EMAIL_SENT = "Sim";

function sendEmails() {
  var file = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("");
  var startRow = 2;  // First row of data to process
  var numRows = sheet.getLastRow();   // Number of rows to process
  var dataRange = sheet.getRange(startRow, 1, numRows, 48)
  var data = dataRange.getValues();

  for (var i = 0; i < data.length; ++i) {
    var rowData = data[i];
    if (rowData[18] === "Estudo" || rowData[18] === "Desenvolvimento" || rowData[18] === "Aprovação" || rowData[18] === "Ativo" || rowData[18] === "Cancelado" || rowData[14] === "Descontinuado") {
    //var updateAsDate = new Date(rowData[13]);
    //var update = Utilities.formatDate(updateAsDate, "GMT" , "dd/MM/yyyy" );
    var produto = rowData[1];
    var emailTo = file.getOwner().getEmail();
    //var emailCC = file.getEditors().map(function(e){return [e.getEmail()]}).join(",");
    var versao = rowData[2];
    var status = rowData[18];
    var lastUpdated = rowData[19];
    var lastUpdatedAsDate = Utilities.formatDate(lastUpdated, SpreadsheetApp.getActive().getSpreadsheetTimeZone(), "dd/MM/yyyy HH:mm") + "hs";
    var usuario = rowData[20];
    var message =  "<HTML><BODY>"
        + "<P>Olá!"
        //+ "<br><br />"
        + "<P>O status do produto " + produto + ", versão " + versao + ", foi atualizado para " + "<b>" +status +"</b>" + "."
        //+ "<brr /><br />"
        + "<br>Data da última atualização:  </b>" + lastUpdatedAsDate + "<br />"
        + "<br>Usuário: </b>" + usuario + "<br />"
        + "<br /><br />"    
        //+ "<br>Clique para explorar detalhes, ou para atualizar o status: </b>" + "https://docs.google.com/spreadsheets/d/15pL_AMKVtH4dGk1U7VWMeg590MxtNe7VY4gRqm_GhrM/edit?usp=sharing" + "<br />" 
        + "<br /><br />"  
        + "</HTML></BODY>";   
    var emailEstudo = rowData[42];    
    var emailDesenvolv = rowData[43];
    var emailAprov = rowData[44];
    var emailAtivo = rowData[45];
    var emailCancelado = rowData[46];
    var emailDescont = rowData[47];
    var subject = "O produto " + produto + ", versão " + versao + " " +", mudou de status.";
    Logger.log(rowData[18]);
    if (emailDesenvolv != EMAIL_SENT && rowData[18] === "Desenvolvimento") {  // Prevents sending duplicates 
      sheet.getRange(startRow + i, 44).setValue(EMAIL_SENT);
      SpreadsheetApp.flush();
     } else if(emailEstudo != EMAIL_SENT && rowData[18] === "Estudo") {  // Prevents sending duplicates 
      sheet.getRange(startRow + i, 43).setValue(EMAIL_SENT);
      SpreadsheetApp.flush();
     }else if(emailAprov != EMAIL_SENT && rowData[18] === "Aprovação") {  // Prevents sending duplicates 
      sheet.getRange(startRow + i, 45).setValue(EMAIL_SENT);
      SpreadsheetApp.flush();
     }else if(emailAtivo != EMAIL_SENT && rowData[18] === "Ativo") {  // Prevents sending duplicates 
      sheet.getRange(startRow + i, 46).setValue(EMAIL_SENT);
      SpreadsheetApp.flush();
     }else if(emailCancelado != EMAIL_SENT && rowData[18] === "Cancelado") {  // Prevents sending duplicates 
      sheet.getRange(startRow + i, 47).setValue(EMAIL_SENT);
      SpreadsheetApp.flush();
     }else if(emailDescont != EMAIL_SENT && rowData[18] === "Descontinuado") {  // Prevents sending duplicates 
      sheet.getRange(startRow + i, 48).setValue(EMAIL_SENT);
      SpreadsheetApp.flush();
     }
     }
  }
  MailApp.sendEmail({
    name: "P&D - PB",
    to: emailTo,
    //cc: emailCC,
    subject: subject, 
    htmlBody: message
  });
    }

The intent is to send one email containing the product name, version and its current status. Therefore, although I have multiple rows with repeated data, the email would only need one row of data, but mark these repeated rows with EMAIL_SENT to avoid another email being sent later.

function SendEmail() {
  var mgrcol=4;
  var file = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ArquivoItens");
  var rg=sheet.getDataRange();
  var values=rg.getValues();
  var productList=[];
  var html='';
  for(var i=1;i<values.length;i++) {
    if(productList.indexOf(values[i][0])==-1 && productList.indexOf(values[i][2])==-1) {
      //mgrA.push(vA[i][3]);
      productList.push(values[i]); // Joga toda a linha que será usada pra dar os dados para o email.
    }
  }

  var productObj={}
  for(var i=0;i<productList.length;i++) {
    for(var j=0;j<values.length;j++) {
      if(productList[i]==values[j][0] && productList[i]==values[j][2]){
        if(productObj.hasOwnProperty(productList[i])) {
          productObj[productList[i]]+=Utilities.formatString('~~~%s<br />%s<br />%s',values[j][0],values[j][2],values[j][18]);
        }else{
          productObj[productList[i]]=Utilities.formatString('%s<br />%s<br />%s',values[j][0],values[j][2],values[j][18]);
        }                                               
      } 
    }
  }
  Logger.log(productList);
  for(var i=0;i<productList.length;i++) {
    var tA=productObj[productList[i]].split('~~~'); //This is presenting the error
    var s='Status atualizado<br /></br />';
    for(var j=0;j<tA.length;j++) {
      s+=tA[j].toString() + '<br />'; 
    }
    s+='<hr widht="100" />';
    GmailApp.sendEmail(productList[i], 'User Names and Emails', null, {htmlBody:s})
    html+=Utilities.formatString('Email Recipient: <strong>%s</strong><br />',productList[i]) + s;//debug 
  }
  var ui=HtmlService.createHtmlOutput(html);//debug
  SpreadsheetApp.getUi().showModelessDialog(ui, 'Emails');//debug
}

Short answer: You filter the data before the mails are sent or you keep a lists of email sent and use a condition prevent sending another email to the same email address. Please add a brief description of your search/research efforts as is suggested in How to Ask. — Rubén
Probably the easiest thing to do in to run the emails in two passes. First Pass just collects all of the data into an object of arrays the key of which is the email address. The second pass you get the key from Object.keys() and then you loop through them collecting all of the data from the arrays in each key. — Cooper
This is an example that is somewhat similar: stackoverflow.com/a/54741922/7215091 — Cooper
Hi@Cooper, Read the content of the link more carefully and it really looks like my difficulty. I'll try that solution as soon as I can. Sorry I basically repeated my question, thinking you had makred it as duplicate a couple of hours ago. — Antonio Santos
If you have two rows with exactly the same content - maybe you want to remove duplicates from the sheet before proceeding wiht sending emails? Otherwise, please specify more in detail: Do you want to send one email contianing all rows that are not exact duplicates and are not marked as EMAIL_SENT? Do you run your funciton on time-driven trigger or onEdit? — ziganotschka

Cooper Cooper · Accepted Answer · 2020-06-09T20:48:35

This is just an example to show you how to build arrays of rows that have common properties. I used the values of ColumnA and ColumnC to create the property because they match in the areas that I want to combine together.

Here's the code:

function makingObjects() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getActiveSheet();
  const rg=sh.getDataRange();
  const vs=rg.getValues();
  const hA=vs.shift();
  let col={};
  let idx={};
  hA.forEach(function(h,i){col[h]=i+1;idx[h]=i;});
  let obj={pA:[]};
  vs.forEach(function(r,i){
    let prop=r[idx["COL1"]]+ "-" + r[idx["COL3"]];
    if(!obj.hasOwnProperty(prop)) {
      obj[prop]=[];
      obj[prop].push({"COL2":r[idx["COL2"]],"COL4":r[idx["COL4"]],"COL5":r[idx["COL5"]],"COL6":r[idx["COL6"]]});
      obj.pA.push(prop);//I find easier just keep the properties here
    }else{
      obj[prop].push({"COL2":r[idx["COL2"]],"COL4":r[idx["COL4"]],"COL5":r[idx["COL5"]],"COL6":r[idx["COL6"]]});
    }
  });
  //SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(JSON.stringify(obj)), "Rows Combined");//This will display a dialog showing how the rows are combined in separated arrays for each property.
  obj.pA.forEach(function(r,i){
    //Here you have to decide and how you want to combine them in each row.
  });
}

Here's the data from my spreadsheet:

COL1,COL2,COL3,COL4,COL5,COL6
1,1,a,1,1,1
1,2,a,2,2,2
2,3,b,3,3,3
2,4,b,4,4,4
3,5,c,5,5,5
3,6,c,6,6,6
4,7,d,7,7,7
4,8,d,8,8,8
5,9,e,9,9,9
5,10,e,10,10,10
6,11,f,11,11,11
6,12,f,12,12,12
7,13,g,13,13,13
7,14,g,14,14,14
8,15,h,15,15,15
8,16,h,16,16,16
9,17,i,17,17,17
9,18,i,18,18,18

GSheets Script email for each row containing repeated data How to limit it to one row only?

2 Answers