-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathtrigger_OnCommit_func.txt
93 lines (70 loc) · 3.17 KB
/
trigger_OnCommit_func.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
##file name: app_send_mail.gs
function onCommit(){
var SpreadSheet = SpreadsheetApp.openById("**********************");
var Sheet = SpreadSheet.getSheetByName("raw1");
var Lastrow = Sheet.getLastRow();
var query_data = Sheet.getRange( Lastrow, 1, 1, 6).getValues(); //colum: A~H ; getRange(row, column, numRows, numColumns)
var EMAIL_SENT = "EMAIL_SENT"; //email sent flag, Prevents sending duplicates.
//欄位順序: 時間戳記, User e-mail, 1.問題一?, 2.問題二?, 3.問題三?, email_sent
var timestamp = query_data[0][0];
var to_email = query_data[0][1];
var q1 = query_data[0][2];
var q2 = query_data[0][3];
var q3 = query_data[0][4];
var emailSent= query_data[0][5];
var htmlBody = getHtmlBody(timestamp,to_email,q1,q2,q3);
//generate HTML
var temp = HtmlService.createTemplateFromFile("email_template");
temp.htmlBody = htmlBody;
var htmlcontext = temp.evaluate().getContent();
if (emailSent != EMAIL_SENT) { // Prevents sending duplicates
var subject = "Sending emails from a Spreadsheet";
var message = "google form last result record.";
MailApp.sendEmail(to_email, subject, message,{from: 'stuser.tc@gmail.com', htmlBody:htmlcontext});
Sheet.getRange(Lastrow, 6).setValue(EMAIL_SENT);
// Make sure the cell is updated right away in case the script is interrupted
SpreadsheetApp.flush();
}
}
function include(filename) {
return HtmlService.createHtmlOutputFromFile(filename).getContent();
}
function getHtmlBody(timestamp,to_email,q1,q2,q3){
var htmlBody = "sending email test: <br/>";
htmlBody += "<table class='datalist'>";
htmlBody += "<thead>";
htmlBody += "<th>時間戳記</th><th>e-mail</th><th>問題一</th><th>問題二</th><th>問題三</th>";
htmlBody += "</thead>";
htmlBody += "<tbody>";
htmlBody += "<tr><td>"+timestamp+"</td><td>"+to_email+"</td><td>"+q1+"</td><td>"+q2+"</td><td>"+q3+"</td></tr>";
htmlBody += "</tbody>";
htmlBody += "</table>";
return htmlBody;
}
##filename: email_template.htmlBody
<!DOCTYPE HTML PUBLIC '-//w3c//dtd HTML 4.01 Transitional//EN' 'http://www.w3.org/TR/html4/loose.dtd'>
<html>
<head>
<base target="_top">
<?!= include('CSS'); ?>
<?!= include('JavaScript'); ?>
</head>
<body>
<?!= htmlBody ?>
</body>
</html>
##filename: CSS.html
<style type="text/css">
.datalist{align: center;border:1px solid #0058a3;font-family:Arial;border-collapse:collapse;background-color:#eaf5ff;font-size:12px;}
.datalist caption{padding-bottom:5px;font:bold 1.4em;text-align:left;}
.datalist th{border:1px solid #0058a3;background-color:#97cbff;color:#000000;font-weight:bold;padding-top:4px; padding-bottom:4px;padding-left:12px; padding-right:12px;text-align:center;}
.datalist td{border:1px solid #0058a3;text-align:left;padding-top:4px; padding-bottom:4px;padding-left:10px; padding-right:10px;}
.datalist tr:hover, .datalist tr.altrow{background-color:#c4e4ff;}
</style>
##filename: JavaScript.html
<script type="text/javascript">
//include page javascript.
//window.addEventListener('load', function() {
// console.log('Page is loaded');
//});
</script>