[Google Drive - Spreadsheet] Read spreadsheet (+ tabs) compose and send email...

codigo.gs

/**
function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 2;   // Number of rows to process
  
  // Fetch the range of cells A2:B3
  var dataRange = sheet.getRange(startRow, 1, numRows, 2)
  
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  
  for (i in data) {
    var row = data[i];
    var emailAddress = row[0];  // First column
    var message = row[1];       // Second column
    var subject = "Sending emails from a Spreadsheet";
    MailApp.sendEmail(emailAddress, subject, message);
  }
  
}
**/


function getHTMLHeader() {
  var content =  "" + 
"<style type=\"text/css\"> " +
".tg  {border-collapse:collapse;border-spacing:0;border-color:#ccc;} " +
".tg td{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#ccc;color:#333;background-color:#fff;} " +
".tg th{font-family:Arial, sans-serif;font-size:14px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#ccc;color:#333;background-color:#f0f0f0;} " +
".tg .tg-amwm{font-weight:bold;text-align:center;vertical-align:top} " +
".tg .tg-9hbo{font-weight:bold;vertical-align:top} " +
".tg .tg-yw4l{vertical-align:top} " +
"</style> " +
"<table class=\"tg\" style=\"table-layout:fixed;width:935px;border-collapse:collapse;border-spacing:0;border-color:#ccc;\" > " +
"<colgroup> " +
"<col style=\"width:117px;\" > " +
"<col style=\"width:491px;\" > " +
"<col style=\"width:42px;\" > " +
"<col style=\"width:62px;\" > " +
"<col style=\"width:68px;\" > " +
"<col style=\"width:155px;\" > " +
"</colgroup> " +
"  <tr> " +
"    <th class=\"tg-amwm\" style=\"font-family:Arial, sans-serif;font-size:14px;padding-top:10px;padding-bottom:10px;padding-right:5px;padding-left:5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#ccc;color:#333;background-color:#f0f0f0;font-weight:bold;text-align:center;vertical-align:top;\" >ALM</th> " +
"    <th class=\"tg-9hbo\" style=\"font-family:Arial, sans-serif;font-size:14px;padding-top:10px;padding-bottom:10px;padding-right:5px;padding-left:5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#ccc;color:#333;background-color:#f0f0f0;font-weight:bold;vertical-align:top;\" >Descrição</th> " +
"    <th class=\"tg-9hbo\" style=\"font-family:Arial, sans-serif;font-size:14px;padding-top:10px;padding-bottom:10px;padding-right:5px;padding-left:5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#ccc;color:#333;background-color:#f0f0f0;font-weight:bold;vertical-align:top;\" >Env.</th> " +
"    <th class=\"tg-9hbo\" style=\"font-family:Arial, sans-serif;font-size:14px;padding-top:10px;padding-bottom:10px;padding-right:5px;padding-left:5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#ccc;color:#333;background-color:#f0f0f0;font-weight:bold;vertical-align:top;\" >Version</th> " +
"    <th class=\"tg-9hbo\" style=\"font-family:Arial, sans-serif;font-size:14px;padding-top:10px;padding-bottom:10px;padding-right:5px;padding-left:5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#ccc;color:#333;background-color:#f0f0f0;font-weight:bold;vertical-align:top;\" >Owner</th> " +
"    <th class=\"tg-9hbo\" style=\"font-family:Arial, sans-serif;font-size:14px;padding-top:10px;padding-bottom:10px;padding-right:5px;padding-left:5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#ccc;color:#333;background-color:#f0f0f0;font-weight:bold;vertical-align:top;\" >Data Correção</th> " +
"  </tr> " +
    "";
  return content;
}


function getHTMLTDContent(code, description, env, version, owner, fixedDate) {
  return "" + 
    
"  <tr> " +
"    <td class=\"tg-yw4l\" style=\"font-family:Arial, sans-serif;font-size:14px;padding-top:10px;padding-bottom:10px;padding-right:5px;padding-left:5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#ccc;color:#333;background-color:#fff;vertical-align:top;\" >" + code + "</td> " +
"    <td class=\"tg-yw4l\" style=\"font-family:Arial, sans-serif;font-size:14px;padding-top:10px;padding-bottom:10px;padding-right:5px;padding-left:5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#ccc;color:#333;background-color:#fff;vertical-align:top;\" >" + description + "</td> " +
"    <td class=\"tg-yw4l\" style=\"font-family:Arial, sans-serif;font-size:14px;padding-top:10px;padding-bottom:10px;padding-right:5px;padding-left:5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#ccc;color:#333;background-color:#fff;vertical-align:top;\" >" + env + "</td> " +
"    <td class=\"tg-yw4l\" style=\"font-family:Arial, sans-serif;font-size:14px;padding-top:10px;padding-bottom:10px;padding-right:5px;padding-left:5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#ccc;color:#333;background-color:#fff;vertical-align:top;\" >" + version + "</td> " +
"    <td class=\"tg-yw4l\" style=\"font-family:Arial, sans-serif;font-size:14px;padding-top:10px;padding-bottom:10px;padding-right:5px;padding-left:5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#ccc;color:#333;background-color:#fff;vertical-align:top;\" >" + owner + "</td> " +
"    <td class=\"tg-yw4l\" style=\"font-family:Arial, sans-serif;font-size:14px;padding-top:10px;padding-bottom:10px;padding-right:5px;padding-left:5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#ccc;color:#333;background-color:#fff;vertical-align:top;\" >" + fixedDate + "</td> " +
"  </tr>" +    
    
    "";
}



//
//
//
function getMessage() {
  var content = 
      "Olá! <br>" + 
      " <br>" + 
      "Você está recebendo a atualização com os tickets (ALM) resolvidos no App <b>Next</b>. <br>" + 
      " <br>" + 
      " <br>";
  
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.setActiveSheet(sheet.getSheetByName('alms'))    
  var dataRange = sheet.getRange(2, 1, 50, 6)
  var data = dataRange.getValues();

  
  content += getHTMLHeader();
  
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    
    if (almCode != "") {
      var almCode = row[0];
      var almDescription = row[1];
      var almEnv = row[2];      
      var almVersion = row[3];            
      var almResponsible = row[4];
      var almFixedDate = row[5];
      
      content += getHTMLTDContent(almCode, almDescription, almEnv, almVersion, almResponsible, almFixedDate);
      
//function getHTMLTDContent(code, description, env, version, owner, fixedDate) {
      
      
    }
    
  }  
  
  content += "</table>";
  
  return content;
}


//
//
//
function markAsSent(sheetTo, rowIndex) {
  sheetTo.getRange(START_ROW + rowIndex, 2).setValue(EMAIL_SENT);
  SpreadsheetApp.flush();
}


var EMAIL_SENT = Utilities.formatDate(new Date(), "GMT-2", "yyyy-MM-dd HH:mm:ss");
var START_ROW = 2;
var NUM_ROWS = 50;

function sendEmails2() {
  var sheetTo = SpreadsheetApp.getActiveSpreadsheet();
  var sheetTo = SpreadsheetApp.setActiveSheet(sheetTo.getSheetByName('to'))    
  var dataRangeTo = sheetTo.getRange(START_ROW, 1, NUM_ROWS, 3)
  
  // Fetch values for each row in the Range.
  var data = dataRangeTo.getValues();
  
  
  var message = getMessage();
  
  
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var emailAddress = row[0];  // First column
    if (emailAddress != '') {
      var subject = "Next App | ALM Status Report - App - " + EMAIL_SENT;
      MailApp.sendEmail({to:emailAddress, subject:subject, htmlBody:message});    
      markAsSent(sheetTo, i);
    }
  }
}