Created
January 24, 2017 12:20
-
-
Save mangar/51e172aac9ff4d72edffe8b83e4db761 to your computer and use it in GitHub Desktop.
[Google Drive - Spreadsheet] Read spreadsheet (+ tabs) compose and send email...
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** | |
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); | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment