How to send several rows of google sheet table via email if cell match today's date

daily I have to send different qty of google sheet table rows (it depends on how many trucks were departed) via e-mail to a few recipients.

Could you help me, please, with an example of how to collect several rows of google table if cell match today's date and then send it via email?

For example, I need to grab all rows with today's date and send data from columns A, B, C, E via e-mail.

Thanks for any help in advance :)

Solution

Let's go step by step. I'm supposing the column A contains the dates. Change it if it's not correct:

Declare the variables we will use for this

function main() {  
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = sheet.getActiveSheet();
  var row_count = sheet.getLastRow() - 1;  
  var today = Utilities.formatDate(new Date(), "UTC", "dd.MM.yyyy"); //You can use the date format you prefer
  var data = sheet.getRange("A2:E"+ (row_count + 1)).getValues(); //We get all the values from A to E and from row 2 to the last one.

  countToday(sheet, today, row_count, data);
  sendEmails(data);


}

Since we have each row in each position of data, we will compare each date with "today", and we will remove (splice) the rows from other days. The rows with valid dates will remain in the variable:

function countToday(sheet,today, row, data){

  var lastrow = "A" + (row + 1);
  var col_A = sheet.getRange('A2:'+lastrow).getValues();


  for (var i = row; i >= 0; i--){
    if (col_A[i] != today){
      data.splice(i, 1); //
    }

  }

}

Now that we got each row of elements, we can send the email. We will create an html table so it will be easier to understand. The Mailapp function makes it very simple:


function sendEmails(data){

 MailApp.sendEmail({
    to: "[email protected]" + "[email protected]", //You can put as many emails you want
    subject: "Example",
    htmlBody:"<html><body>" + createTable(data)+ "</body></html>"});

}

To create the html table, we just make a string coded in html with the names of the columns from the Sheet. Then we make a table array, where we will split each element from data separated by coma (that's each sheet cell) and just add it to the end of the variable. < /td> will create the rows for us.

function createTable(data){
  var cells = [];

  var table = "<html><body><br><table border=1><tr><th>Date</th><th>Column B</th><th>Column C</th><th>Column D</th><th>Column E</tr></br>";

  for (var i = 0; i < data.length; i++){
      cells = data[i].toString().split(",");
      table = table + "<tr></tr>";

      for (var u = 0; u < cells.length; u++){
          table = table + "<td>"+ cells[u] +"</td>";
      }
  }

  table=table+"</table></body></html>";
  return table;
}