-
Notifications
You must be signed in to change notification settings - Fork 0
/
Code.gs
84 lines (57 loc) · 2.64 KB
/
Code.gs
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
function sendEmail(){
var emailAddress = 'XXXXXXXXXXXXXXXXXXXXXXXXXX'; // Input email address of recipient
var timeZoneOffset = '+ 10' // Input timezone for of recipient
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Watchlist');
var tickerNumberRng = ss.getRange('TickerNumber');
var totalTickers = ss.getRange('TotalTickers').getValue();
var date = new Date();
var day = date.getDay();
var dateString = Utilities.formatDate(date, 'GMT ' + timeZoneOffset, 'dd/MM/YY EEEE');
var subjectString = '[Stock Watchlist Alert] ' + dateString; // appends string to end of subject title which provides handle to filter alerts in Gmail
var messageString = '';
var sortRange = ss.getRange('B3:D100');
// Sends email alerts only on weekdays
if (day >= 1 && day <= 5){
// Top 5 falls
messageString += 'Top 5 Daily Losers:\n';
sortRange.sort({column: 4, ascending: true});
for (var i = 1; i <= totalTickers; i++){
// Set scenario to current ticker value
tickerNumberRng.setValue(i);
var dayChange = ss.getRange('DayChange').getValue();
if (dayChange < 0) {
var tickerDescription = ss.getRange('TickerDescription').getValue();
var dayChangeString = Utilities.formatString('%2.2f%', dayChange * 100);
var emailLine = tickerDescription + ': ' + dayChangeString + '\n';
messageString += emailLine;
}
}
// Top 5 rises
messageString += '\nTop 5 Daily Risers:\n';
sortRange.sort({column: 4, ascending: false});
for (var i = 1; i <= totalTickers; i++){
// Set scenario to current ticker value
tickerNumberRng.setValue(i);
var dayChange = ss.getRange('DayChange').getValue();
if (dayChange > 0){
var tickerDescription = ss.getRange('TickerDescription').getValue();
var dayChangeString = Utilities.formatString('%2.2f%', dayChange * 100);
var emailLine = tickerDescription + ': ' + dayChangeString + '\n';
messageString += emailLine;
}
}
// Tickers % from 52-week low
messageString += '\n% From 52-Week Low:\n'
for (var i = 1; i <= totalTickers; i++){
// Set scenario to current ticker value
tickerNumberRng.setValue(i);
var pctLow = ss.getRange('PctLow').getValue();
var tickerDescription = ss.getRange('TickerDescription').getValue();
var pctLowString = Utilities.formatString('%2.2f%', pctLow * 100);
var emailLine = tickerDescription + ': ' + pctLowString + '\n';
messageString += emailLine;
}
// Email alert
MailApp.sendEmail(emailAddress, subjectString, messageString);
}
}