일단 Google Sheets, 스프레드시트를 이용한 메일링이 가능 한 것을 알게 되었고,
즉시 방법을 찾았다.
근데 정확히 내 엑셀에 있는 데이터를 표 형식으로 해서 메일링 하는 방법은 찾기 어려웠다.
그래도 힘겹게 이 사이트를 찾아서 다행이였다.
https://spreadsheet.dev/send-html-email-from-google-sheets
Send HTML email from Google Sheets
In this tutorial, I'll show you how to send HTML email from Google Sheets using Apps Script. I'll also show you how to create the HTML email template using Gmail and Google Docs. In a previous tutorial, I described how to build a Stock Watcher application
spreadsheet.dev
https://spreadsheet.dev/send-email-from-google-sheets-based-on-a-schedule
Send email from Google Sheets based on a schedule
If you've worked in any organization of any size, you've probably had to put together and send reports on a regular basis to your team. In this post, I'll show you how to automate sending emails from Google Sheets based on a schedule. We'll build a stock p
spreadsheet.dev
여기 방법을 그대로 따라하면 된다.
총 위에 두개가 나오는데
위에는 google sheets에서 Google Script를 통해 데이터를 읽어오는법
아래는 google sheets에서 html에다 데이터를 넣어주는 방법이다.
위에방법으로만 하면 그냥 일렬로 데이터가 쭉 나와서 메일 전송이 되기때문.
처음엔 방법이 어려웠으나 해보니 쉽다.
나의 스프레드시트의 데이터는 아래와 같다.
이걸 자동 메일링으로 보낼거다.
과정 1 - 데이터 에 들어간다(파일 수정 보기 등등 칸에 있다) -> 이름으로 지정된 범위 클릭
들어가서 범위를 지정한 후 이름을 정해준다.
과정 2 - 시트의 이름을 변경한다. (나같은 경우엔 참고 사이트와 똑같이 Data로 변경, 위의 Stocks도 마찬가지)
과정 3 - 도구 - 스크립트 편집기 들어간다
Code.gs에 아래와 같은 코드를 넣는다.
sendEmail은 메일 보내는 함수
getEmailText는 필요없다. body로 변수 받는데 메일링 할때 body 변수 지워버렸거든
getData는 아까 지정해준 Data, Stocks에서 각각의 값을 가져온다
getColor는 getData에서 값 -> 색상 변경이라고 보면 된다.
getEmailHtml은 html로 데이터를 넣어주는 함수
|
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
|
function myFunction() {
sendEmail()
}
function sendEmail() {
var stockData = getData();
var stockColor = getColor();
var body = getEmailText(stockData,stockColor);
var htmlBody = getEmailHtml(stockData,stockColor);
MailApp.sendEmail({
to: "메일@naver.com",
subject: "하루일과",
htmlBody : htmlBody
});
}
function getEmailText(stockData) {
var text = "";
stockData.forEach(function(stock) {
text = text + stock.name + "\n" + stock.ticker + "\n" + stock.price + "\n-----------------------\n\n";
});
return text;
}
/**
* @OnlyCurrentDoc
*/
function getData() {
var values = SpreadsheetApp.getActive().getSheetByName("Data").getRange("Stocks").getValues();
values.shift(); //remove headers
var stocks = [];
values.forEach(function(value) {
var stock = {};
stock.market = value[0];
stock.ticker = value[1];
stock.company = value[2];
stock.volumn = value[3].toFixed(2);
stock.change = value[4].toFixed(2);
stock.price = value[5].toFixed(2);
stock.year_high = value[6].toFixed(2);
stock.year_high_over = value[7].toFixed(2);
stock.year_low = value[8].toFixed(2);
stock.year_low_over = value[9].toFixed(2);
stock.per = value[10].toFixed(2);
stock.eps = value[11].toFixed(2);
stocks.push(stock);
})
//Logger.log(JSON.stringify(stocks));
return stocks;
}
function getColor() {
var values = SpreadsheetApp.getActive().getSheetByName("Data").getRange("Stocks").getBackgrounds();
values.shift(); //remove headers
var colors = [];
values.forEach(function(value) {
var color = {};
color.market = value[0];
color.ticker = value[1];
color.company = value[2];
color.volumn = value[3];
color.change = value[4];
color.price = value[5];
color.year_high = value[6];
color.year_high_over = value[7];
color.year_low = value[8];
color.year_low_over = value[9];
color.per = value[10];
color.eps = value[11];
colors.push(color);
})
return colors;
}
function getEmailHtml(stockData,stockColor) {
var htmlTemplate = HtmlService.createTemplateFromFile("Template.html");
htmlTemplate.stocks = stockData;
htmlTemplate.colors = stockColor;
var htmlBody = htmlTemplate.evaluate().getContent();
console.log(htmlBody)
return htmlBody;
}
|
cs |
과정4 - HTML 코드 만들기 (사실 이게 제일 귀찮다)
일단 코드 투척.
|
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
|
<div dir="ltr">
<table cellspacing="0" cellpadding="0" dir="ltr" border="1" style="table-layout:fixed;font-size:10pt;font-family:Arial;border-collapse:collapse;border:none">
<colgroup>
<col width="95">
<col width="57">
<col width="139">
<col width="74">
<col width="55">
<col width="75">
<col width="93">
<col width="144">
<col width="93">
<col width="144">
<col width="56">
<col width="74">
</colgroup>
<tbody>
<tr style="height:21px">
<td style="border-width:1px;border-style:solid;overflow:hidden;vertical-align:bottom;background-color:rgb(207,226,243);font-size:11pt;font-weight:bold;color:rgb(0,0,0);text-align:center"
rowspan="1" colspan="12">관심주식</td>
<tr style="height:21px">
<td style="border:1px solid rgb(0,0,0);overflow:hidden;vertical-align:bottom;background-color:rgb(207,226,243);font-size:12pt;font-weight:bold;text-align:center">거래소</td>
<td style="border-width:1px;border-style:solid;overflow:hidden;vertical-align:bottom;background-color:rgb(207,226,243);font-size:12pt;font-weight:bold;text-align:center">티커</td>
<td style="border-width:1px;border-style:solid;overflow:hidden;vertical-align:bottom;background-color:rgb(207,226,243);font-size:12pt;font-weight:bold;text-align:center">기업명</td>
<td style="border-width:1px;border-style:solid;overflow:hidden;vertical-align:bottom;background-color:rgb(207,226,243);font-size:12pt;font-weight:bold;text-align:center">시총</td>
<td style="border-width:1px;border-style:solid;overflow:hidden;vertical-align:bottom;background-color:rgb(207,226,243);font-size:12pt;font-weight:bold;text-align:center">상승률</td>
<td style="border-width:1px;border-style:solid;overflow:hidden;vertical-align:bottom;background-color:rgb(207,226,243);font-size:12pt;font-weight:bold;text-align:center">현재 주가</td>
<td style="border-width:1px;border-style:solid;overflow:hidden;vertical-align:bottom;background-color:rgb(207,226,243);font-size:12pt;font-weight:bold;text-align:center">52주 신고가</td>
<td style="border-width:1px;border-style:solid;overflow:hidden;vertical-align:bottom;background-color:rgb(207,226,243);font-size:12pt;font-weight:bold;text-align:center">신고가 대비 하락률</td>
<td style="border-width:1px;border-style:solid;overflow:hidden;vertical-align:bottom;background-color:rgb(207,226,243);font-size:12pt;font-weight:bold;text-align:center">52주 신저가</td>
<td style="border-width:1px;border-style:solid;overflow:hidden;vertical-align:bottom;background-color:rgb(207,226,243);font-size:12pt;font-weight:bold;text-align:center">신저가 대비 상승률</td>
<td style="border-width:1px;border-style:solid;overflow:hidden;vertical-align:bottom;background-color:rgb(207,226,243);font-size:12pt;font-weight:bold;text-align:center">PER</td>
<td style="border-width:1px;border-style:solid;overflow:hidden;vertical-align:bottom;background-color:rgb(207,226,243);font-size:12pt;font-weight:bold;text-align:center">EPS</td>
</tr>
<? for(var i = 0; i < stocks.length; i++) { ?>
<tr style="height:21px">
<td style="border-width:1px;border-style:solid;overflow:hidden;vertical-align:bottom;font-size:12pt;text-align:center; background-color : <?= colors[i].market?>"><?= stocks[i].market?></td>
<td style="border-width:1px;border-style:solid;overflow:hidden;vertical-align:bottom;font-size:12pt;text-align:center; background-color : <?= colors[i].ticker?>"><?= stocks[i].ticker?></td>
<td style="border-width:1px;border-style:solid;overflow:hidden;vertical-align:bottom;font-size:12pt;text-align:center; background-color : <?= colors[i].company?>"><?= stocks[i].company?></td>
<td style="border-width:1px;border-style:solid;overflow:hidden;vertical-align:bottom;font-size:12pt;text-align:center; background-color : <?= colors[i].volumn?>"><?= stocks[i].volumn?></td>
<td style="border-width:1px;border-style:solid;overflow:hidden;vertical-align:bottom;font-size:12pt;text-align:center; background-color : <?= colors[i].change?>"><?= stocks[i].change?></td>
<td style="border-width:1px;border-style:solid;overflow:hidden;vertical-align:bottom;font-size:12pt;text-align:center; background-color : <?= colors[i].price?>"><?= stocks[i].price?></td>
<td style="border-width:1px;border-style:solid;overflow:hidden;vertical-align:bottom;font-size:12pt;text-align:center; background-color : <?= colors[i].year_high?>"><?= stocks[i].year_high?></td>
<td style="border-width:1px;border-style:solid;overflow:hidden;vertical-align:bottom;font-size:12pt;text-align:center; background-color : <?= colors[i].year_high_over?>"><?= stocks[i].year_high_over?></td>
<td style="border-width:1px;border-style:solid;overflow:hidden;vertical-align:bottom;font-size:12pt;text-align:center; background-color : <?= colors[i].year_low?>"><?= stocks[i].year_low?></td>
<td style="border-width:1px;border-style:solid;overflow:hidden;vertical-align:bottom;font-size:12pt;text-align:center; background-color : <?= colors[i].year_low_over?>"><?= stocks[i].year_low_over?></td>
<td style="border-width:1px;border-style:solid;overflow:hidden;vertical-align:bottom;font-size:12pt;text-align:center; background-color : <?= colors[i].per?>"><?= stocks[i].per?></td>
<td style="border-width:1px;border-style:solid;overflow:hidden;vertical-align:bottom;font-size:12pt;text-align:center; background-color : <?= colors[i].eps?>"><?= stocks[i].eps?></td>
</tr>
<? } ?>
</tbody>
</table>
</div>
|
cs |
아래의 큰 틀을 만들기 위해서는
과정 4-1 엑셀을 복사해서 gmail이용하여 나 자신에게 보낸다.
과정 4-2 gmail에서 메일 들어간 뒤 더보기(오른쪽 3개 점) 들어가서 메일 원본보기를 클릭
과정 4-3 원본메일 - 클립보드 복사
과정 4-4 클립보드에서 <div> ~</div> 만 가져오면된다.
여기서 잠깐! Content-Transfer-Encoding: quoted-printable 이거 다음 div가 시작이며
매애애애앤 아래 --000000000000478d2f05cb539a8a-- 이런거 위에 div가 마지막이다.
과정 4-5 Decode에 복붙하고 Encode 해준 후 Encoding 된 텍스트 가져온다.
https://www.webatic.com/quoted-printable-convertor
Encode/Decode Quoted Printable - Webatic
Related utilities HTML Entities convertor, URL convertor, Base64 convertor, Encoding Explorer Charset The current character set/encoding is Unicode (utf-8) Afrikaans (iso-8859-1) Albanian (iso-8859-1) Arabic (win1256) Azerbaijani (iso-8859-9) B
www.webatic.com
과정 4-6 Encoding 된 텍스트 예쁘게 정렬시켜준다.
https://www.browserling.com/tools/html-prettify
HTML Prettifier - HTML Beautifier - Online - Browserling Web Developer Tools
www.browserling.com
그 뒤에 Template.html에 복붙해준다. 그게 아까 그 위에 코드이다
과정 5 - HTML에 변수를 넣어준다.
위에 코드를 잠시보자면 <?= stocks[i].eps?> 이런 부분이 있는데 데이터 가져와서 동적으로 넣어준것이다.
아 반복문도 있으니 참고 부탁.
아무튼 이렇게 순서대로 하면
이렇게 쨘하고 보내진다.
그 뒤에 트리거는 알아서 잘들 조절 하시고
그럼 여기까지.
'투자 > 개발' 카테고리의 다른 글
| 위불과 같은 RSI 계산하기 (엑셀, 파이썬) (0) | 2021.09.25 |
|---|---|
| 네이버 증권을 이용한 주식 테마 정리하기 (4) | 2021.08.22 |