https://ithelp.ithome.com.tw/m/articles/10272118
登入
問答
文章
徵才
聊天
2021 iThome 鐵人賽 Modern Web DAY 25
D25 - 如何用 Apps Script 自動化地創造與客製 Google Sheet？（二）結合股票價格通知與信件
整合 Google 服務的燃料——透過 Google Apps Script (GAS) 加速你的工作速度 系列 第 25 篇
森林裡的園藝眼鏡
3 年前 ‧ 9846 瀏覽
1
今天的目標：
幫自己用 Google Sheet 和 GAS 做一個數值到了就用 mail 提醒的程式！身為一個不喜歡被通知綁住的工程師，我自己除了 Email 之外的通知都是關閉的。換句話說，如果今天我的股票突然到一個很慘的價格，我是要看到才知道挫賽；或是到我了理想價了，我往往都飲恨錯過。所以就突發奇想，能不能結合 Google Apps Script 做個通知呢？就試著做做看了。
今天的關鍵問題是——
要怎麼用 GAS 做一個股票價格提醒器？
這邊主要會今天主要會帶到 Google Sheet 中 Google Finance 的使用。那就讓我們開始吧！
要怎麼用 GAS 做一個股票價格提醒器？
Input
希望觀察的清單（Google Finance 代碼）
可能是美股或台股
台股可能是上市或上櫃
希望通知的頻率（Google Trigger）
我就先隨便抓個幾支股票，從美股、上市台股、上櫃台股都有。
Output
一封「你的期望價格已到」的信
且發過後就不要重發
搞懂 Input 和 Output 後，讓我們來看看中間的 Process 要怎麼寫吧。
Step 1 從 Google Sheet 進入 GAS
今天我們用 Google Sheet 作為連結 GAS 的管道，讓我們借用 D14 的影片。
一樣第一次按下 GAS 中的「執行」會有「存取驗證」需要大家按一下。這邊仍是借用一下 D2 的影片。
接著，我們要先設定這張 Google Sheet 上面的參數們。
Step 2 從 Google Sheet 中讀取股票名稱與現在股價
這邊我們主要會用到 googlefinance 來讀美股和上市台股，用 importHTML 來讀上櫃台股。讓我們開始吧！
先給大家看最後的 Google Sheet 公式，我們先一層層寫，比較看得懂有幾層——
=iferror(
ifs(A2="美股",GOOGLEFINANCE(C2,"price"),
B2="上市",GOOGLEFINANCE("TPE:"&C2,"price"),
B2="上櫃",vlookup(value(C2),'上櫃資訊'!A:C,3,0)
),
"找不到資料")
最外層有個 iferror，簡單來說，就是有錯誤時的通知。
ifs 是連續幾層的 if 的意思，這邊就是分別檢查，是美股、上市與上櫃的情況要怎麼處理。Google Sheet 的邏輯是會先檢查第一個 IF，才會往後檢查。這點在設計上可以注意一下。
GOOGLEFINANCE 仔細看會知道是 Google Finance，是 Google 給 Google Sheet 中拉出財務資料的 API，我們拉出來介紹——
vlook 的意思是找另外一張表單的資料，我在名叫「上櫃資訊」的另一張試算表（Sheet）中，使用了 importHTML 來抓出資料。
那我們分別來看看 Google Finacne 和 importHTML。
Google Finance
這個 function 有五個參數可以調整——
Ticker——基本上就是股票代碼，可以透過 Google Finance 查到
（選填）屬性（Attribute）—— 預設是查即時價格（Price），要從 Google 財經服務擷取的代號相關屬性。不斷在新增中，功能越來越強大，可以參考此表
開始時間、結束時間與間隔，也是參考同一張表最後的範例，上面的清晰度覺得夠了，就不再多做介紹。
那我這邊用到的屬性有預設的價格（Price）和股票名稱（Name），設定起來的公式長這樣——
ImportHTML
這筆資料是抓 台灣證交所 的 API，透過這個網址我們可以讀取到上櫃股票的資料。我們來看看直接用瀏覽器讀會怎麼樣——
"https://www.tpex.org.tw/web/stock/aftertrading/otc_quotes_no1430/stk_wn1430_print.php?l=zh-tw&se=EW&s=0,asc,0&d="
跑起來長這樣，就基本上是一張大表單（Table）輸出有交易的上櫃資料們。
而我們透過 ImportHTML 設定的話，要弄成以下這樣，指定我要最大的這張表格（Table）
=IMPORTHTML("https://www.tpex.org.tw/web/stock/aftertrading/otc_quotes_no1430/stk_wn1430_print.php?l=zh-tw&se=EW&s=0,asc,0&d=","table",1)
跑起來長這樣——
而接著就是透過 vlookup 去取得另外一張表單的內容。這邊先說明一下在 Google Sheet 中叫另外一張表單的資訊，使用的程式碼是——
='試算表名稱'!位置
// e.g.
='上櫃資訊'!B4
跑起來長這樣——
所以搭配起來的 vlookup 程式碼是這樣—— vlookup(value(C9),'上櫃資訊'!A:C,2,0)
跑起來長這樣——
好，最後則是會回到我們 Step 2 最一開始的程式碼，將上面這些整合起來，依照不同的資料用不同的公式。
=iferror(ifs(A2="美股",GOOGLEFINANCE(C2,"price"),B2="上市",GOOGLEFINANCE("TPE:"&C2,"price"),B2="上櫃",vlookup(value(C2),'上櫃資訊'!A:C,3,0)),"找不到資料")
跑起來長這樣——
那基本上設定好後，這個數值會不斷更新，預設頻率是每二十分鐘一次。但我們可以設定讓它每分鐘/每小時有自動更新，方式如下——
那當這些都設定完後，接著就是寫上「預計通知數值」並且設定 Gmail 寄信了。
Step 3 依照出場與停損價格，寄出通知 Email
我們先設定一系列數據如下——
以下因為前面有用過數次，第一次進來的朋友不清楚怎麼用的話，可以參考 D4 - 如何透過 Google Apps Script 來整合 Google Form / Google Sheet 並自動寄出客製的 Email？ 1. 取得 Google Sheet 的資料架構 ，裡頭有完整介紹 GAS 取得 Google Sheet 的架構。
那，對於知道的朋友，我們就先直接上讀數據的程式碼——
function readData(){
let sheet = SpreadsheetApp.getActiveSheet()
let start_row = 2;
let start_col = 5;
let num_row = sheet.getLastRow()-1;
let num_col = 3;
let data = sheet.getRange(start_row, start_col, num_row, num_col).getValues();
Logger.log(data);
return data;
}
看一下對照是否正確——
看來數值沒錯，那接著我們就繼續玩寄信。
function stockPriceAlert(){
let data = readData();
let emailAddress = "your_mail_here";
for (row_data of data){
let curr_price = row_data[0];
let higher_target = row_data[1];
let lower_target = row_data[2]
if((curr_price > higher_target) || (curr_price < lower_target)){
MailApp.sendEmail(emailAddress, "Your stock alert!");
}
}
}
寄信也很簡單，就單純寫個程式碼，在數值超過這個範圍時進行通知。這邊比較特殊的是，要來設定「Trigger」，我自己是習慣設定每十分鐘一次。操作影片如下——
好，那這樣就設定完了。但，我們總不會希望它每十分鐘就寄一次通知，所以這邊設定一天只要寄送一次。方式是當今天有寄出信時，就在 GAS 上面寫上最後提醒日期，如果這日期跟今天一樣，那就不再寄送，完整的程式碼如下——
function readData(){
let sheet = SpreadsheetApp.getActiveSheet()
let start_row = 2;
let start_col = 5;
let num_row = sheet.getLastRow()-1;
let num_col = 4;
let data = sheet.getRange(start_row, start_col, num_row, num_col).getValues();
return data;
}
function stockPriceAlert(){
let data = readData();
let emailAddress = "your_mail_here";
for (let i=0; i < data.length; i++){
let row_data= data[i]
let curr_price = row_data[0];
let higher_target = row_data[1];
let lower_target = row_data[2];
let last_sent_date =row_data[3];
let todat_date = new Date().getDate();
if((curr_price > higher_target) || (curr_price < lower_target)){
if(todat_date != last_sent_date){
// MailApp.sendEmail(emailAddress, "Your stock alert!");
SpreadsheetApp.getActiveSheet().getRange(i, 6).setValue(todat_date)
}
}
}
}
有朋友問說今天的試算表能不能給一份，給拉哪次不給的，是谷歌的連結，基本上谷歌沒事就是無毒保證。
好，那今天就到這邊！今天比較是應用題，更多是 Google Sheet 的操作。 Google Sheet 本身就是很強大的工具，搭配 GAS 更是會讓大家如虎添翼。
今天是我們的第 25 天，盤點一下今天學的
Google Sheet 公式
googlefinance
vlook
從另外一張表單讀數據的方式
回顧 D4 的寄信
那今天就到這邊，鐵人賽也接近了尾聲。也進入了最關鍵的 Sheet 的部分，希望內容對大家有所幫助。如果還有問題，透過留言之外，也可以到 Facebook Group，想開很久這次鐵人賽才真的開起來，歡迎來當 Founding Member。如果不想錯過可以訂閱按讚小鈴鐺（？），也歡迎留言跟我說你還想知道什麼做法/主題。我們明天見。
留言
追蹤
分享
訂閱
此系列
上一篇
此系列
下一篇
0 則留言
iThome 服務 iThome online iThome Learning
電週文化事業版權所有、轉載必究 | Copyright © iThome 刊登廣告 服務信箱 隱私權聲明與會員使用條款