Search code examples
javascriptgoogle-apps-scriptgoogle-sheetshtml-email

Fetch rows with column matching a criteria and send them by email (Google Sheets)

The need here is to have a minimum qty set in a column and once the inventory has items' qtys equal to or below that, have an email sent once a day with the rows fetched. I have the below script working for another purpose, but this sends an email with one row at a time, while we now need to have the rows fetched and sent in one email (HTML format is ideal).

var EMAIL_SENT = "EMAIL_SENT";

function sendEmails2() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Pedidos");
  var startRow = 7;  // First row of data to process
  var numRows = sheet.getLastRow();   // Number of rows to process
  // Fetch the range of cells A7:s3
  var dataRange = sheet.getRange(startRow, 1, numRows, 25)
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var rowData = data[i];
    if (rowData[13] === "Ativo") {
    var emailAddress = "";  // First column
    var update = rowData[12];
    var NumPedido = rowData[0];
    var Cliente = rowData[1];
    var UF = rowData[3];
    var Produto = rowData[4];
    var TotaldoPedido = rowData[7];
    var NumNF = rowData[8];
    var Status = rowData[13];
    var Obs = rowData[16];
    var DataAgenda = rowData[11];
    var senha = rowData[15];
    var respons = rowData[17];
    var message =  "<HTML><BODY>"
    //+ "<P>Dear " + firstname + ","
    //+ "<br /><br />"
    + "<P>Status do Pedido " + NumPedido + ", do cliente " + Cliente + " foi atualizado! Veja abaixo:"
    + "<brr /><br />"
    + "<br>Data da Atualização:  </b>" + update + "<br />" 
    + "<br>Nº do Pedido:  </b>" + NumPedido + "<br />" 
    + "<br>Cliente:  </b>" + Cliente + UF + "<br />" 
    + "<br>Produto:  </b>" + Produto + "<br />" 
    + "<br>Nº da NF:  </b>" + NumNF + "<br />"  
    + "<br>Status: </b>" + Status + "<br />"  
    + "<br>Senha:  </b>" + senha + "<br />"  
    + "<br>Observações: </b>" + Obs + "<br />" 
    + "<br>Responsável: </b>" + respons + "<br />" 
    + "<br /><br />"    
    + "<br>Clique para explorar detalhes, ou para atualizar o status: </b>" + "" + "<br />" 
    + "<br /><br />"  
    + "</HTML></BODY>";      
    var emailAtivo = rowData[18];    
    if (emailAtivo != EMAIL_SENT && rowData[13] === "Ativo") {  // Prevents sending duplicates
      var subject = "Status do Pedido " + NumPedido + " do " + Cliente + " " + UF + " atualizado";
  MailApp.sendEmail({
    to: emailAddress, 
    subject: subject, 
    htmlBody: message
  });

  sheet.getRange(startRow + i, 19).setValue(EMAIL_SENT);

  // Make sure the cell is updated right away in case the script is interrupted
  SpreadsheetApp.flush();
//}

} }

Here is the updated script: function sendTableEmail() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Estoque-Almox - ADB");

  // Get your table data
  var startRow = 7;  // First row of data to process
  var numRows = sheet.getLastRow(); // Number of rows to process
  var dataRange = sheet.getRange(startRow, 1, numRows, 14); // Fetch the range of cells A7:D
  var data = dataRange.getValues(); // Fetch values for each row in the Range.
  var minimum = sheet.getRange("n7:n32").getValue(); // Minimum quantity to check against

  // Loop through the data to build your table
  var message = "<html><body><table style=\"text-align:center\"><tr><th>Código</th><th>Descrição</th><th>Qtd(Cx)</th><th>Estoque Mín. (Cx)</th></tr>";
  for (var i = 0; i < data.length; ++i) {
    var rowData = data[i];
    var Code = rowData[4];
    var Description = rowData[5];
    var InventoryUnit = rowData[10];
    var InventoryBox = rowData[11];
    var InventoryPallet = rowData[12];
    //var MinQty = rowData[13];
    //if (minimum >= 31) {
      if (InventoryBox <= minimum) {
        //var valueA = rowData[0];
        //var valueB = rowData[1];
        message += "<tr><td>"+Code+"</td><td>"+Description+"</td><td>"+InventoryBox+"</td><td>"+minimum+"</td></tr>";
      //} 
    }
  }
  message += "</table></body></html>";
  var subject = "Alerta de Estoque Mín. na Expedição";
  MailApp.sendEmail({
    to: "EMAIL", 
    subject: subject, 
    htmlBody: message
  });
}

...and this is the the result received in my email: enter image description here It's bringing decimal digits (4th Column) which it shouldn't and the 5th column displays 30, whilst on the spreadsheet the formula's result is "" where there are no products listed. Again, thanks a million for your help.

Sorry, here it goes: enter image description here

So, if Column L <= Column N, then fetch the rows meeting this condition and send them in an email.

Solution

  • I understand more clearly what you're doing now. Since the "minimums" are unique to each row, you can pull it when you initiate dataRange. To prevent the list from including blank values, check that the Code isn't blank before proceeding. To fix the decimal number, use toFixed() (see docs). Lastly, just in case no email needs to be sent, I added a sendEmail boolean. If all your quantities are okay, no email will be sent.

    function sendTableEmail() { 
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Estoque-Almox - ADB");

  // Get your table data
  var startRow = 7;  // First row of data to process
  var numRows = sheet.getLastRow(); // Number of rows to process
  var dataRange = sheet.getRange(startRow, 1, numRows, 15); // Fetch the range, including the minimums
  var data = dataRange.getValues(); // Fetch values for each row in the Range.

  // Loop through the data to build your table
  var message = "<html><body><table style=\"text-align:center\"><tr><th>Código</th><th>Descrição</th><th>Qtd(Cx)</th><th>Estoque Mín. (Cx)</th></tr>";
  var sendEmail = false; // If there is content to send, will be set to true
  for (var i = 0; i < data.length; ++i) {
    var rowData = data[i];
    var Code = rowData[4];
    if (Code != "") { // Check for blank Code. If blank, then skip and don't add to the table.
      var Description = rowData[5];
      var InventoryUnit = rowData[10];
      var InventoryBox = rowData[11];
      var InventoryPallet = rowData[12];
      var minimum = rowData[13];
      if (InventoryBox <= minimum) {
        message += "<tr><td>"+Code+"</td><td>"+Description+"</td><td>"+Number(InventoryBox).toFixed(0)+"</td><td>"+minimum+"</td></tr>";
        sendEmail = true; // There is content to send!
      }
    }
  }
  message += "</table></body></html>";
  if (sendEmail) {
    var subject = "Alerta de Estoque Mín. na Expedição";
    MailApp.sendEmail({
      to: "EMAIL", 
      subject: subject, 
      htmlBody: message
    });
  }
}

    You haven't provided enough information for me to fix your attempt, so instead I'm providing you with an example based off what you have provided. The general principles are:

    I hope this helps.

    function sendTableEmail() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Pedidos");

  // Get your table data
  var startRow = 7;  // First row of data to process
  var numRows = sheet.getLastRow(); // Number of rows to process
  var dataRange = sheet.getRange(startRow, 1, numRows, 4); // Fetch the range of cells A7:D
  var data = dataRange.getValues(); // Fetch values for each row in the Range.
  var minimum = sheet.getRange("B4").getValue(); // Minimum quantity to check against

  // Loop through the data to build your table
  var message = "<html><body><table style=\"text-align:center\"><tr><th>A</th><th>B</th><th>Quantity</th><th>Status</th></tr>";
  for (var i = 0; i < data.length; ++i) {
    var rowData = data[i];
    var status = rowData[3];
    var quantity = rowData[2];
    if (status == "Ativo") {
      if (quantity <= minimum) {
        var valueA = rowData[0];
        var valueB = rowData[1];
        message += "<tr><td>"+valueA+"</td><td>"+valueB+"</td><td>"+quantity+"</td><td>"+status+"</td></tr>";
      } 
    }
  }
  message += "</table></body></html>";
  var subject = "Email Subject";
  MailApp.sendEmail({
    to: "[email protected]", 
    subject: subject, 
    htmlBody: message
  });
}

    This is what the spreadsheet looks like.

    enter image description here