CODEWITHSUNDEEP
javascriptemailgoogle-apps-scriptgoogle-sheetsfiltering
onit
onit

Reputation: 2372

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

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: enter image description here

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
}

Upvotes: 1

Views: 95

Answers (2)

onit
onit

Reputation: 2372

Here's the solution I've come up with:

  var produtoEmail = new Array();
  var versaoEmail = new Array();
  var statusEmail = new Array();

and then, within the if statements that qualify the row's data to be sent, it pushes the columns' data I want to the arrays above.

if (emailDesenvolv != EMAIL_SENT && status === "Desenvolvimento") {  // Prevents sending duplicates 
        sheet.getRange(startRow + i, 44).setValue(EMAIL_SENT);      
        produtoEmail.push(data[i][1]);
        versaoEmail.push(data[i][2]); 
        statusEmail.push(data[i][18]);

Then, when writing the email, it gets only the first element of each array. I'm sure this isn't the most efficient way, given my poor technical understanding, but its solved my problem.

Appreciate the ones who helped me.

Cheers,

Upvotes: 0

Cooper
Cooper

Reputation: 64072

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

Upvotes: 0

Related Questions