今天的目標：

幫自己用 Google Sheet 和 GAS 做一個數值到了就用 mail 提醒的程式！身為一個不喜歡被通知綁住的工程師，我自己除了 Email 之外的通知都是關閉的。換句話說，如果今天我的股票突然到一個很慘的價格，我是要看到才知道挫賽；或是到我了理想價了，我往往都飲恨錯過。所以就突發奇想，能不能結合 Google Apps Script 做個通知呢？就試著做做看了。

今天的關鍵問題是——

1. 要怎麼用 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)
		),
"找不到資料")

1. 最外層有個 iferror，簡單來說，就是有錯誤時的通知。
2. ifs 是連續幾層的 if 的意思，這邊就是分別檢查，是美股、上市與上櫃的情況要怎麼處理。Google Sheet 的邏輯是會先檢查第一個 IF，才會往後檢查。這點在設計上可以注意一下。
3. GOOGLEFINANCE 仔細看會知道是 Google Finance，是 Google 給 Google Sheet 中拉出財務資料的 API，我們拉出來介紹——
4. vlook 的意思是找另外一張表單的資料，我在名叫「上櫃資訊」的另一張試算表（Sheet）中，使用了 importHTML 來抓出資料。

那我們分別來看看 Google Finacne 和 importHTML。

Google Finance

這個 function 有五個參數可以調整——

1. Ticker——基本上就是股票代碼，可以透過 Google Finance 查到
2. （選填）屬性（Attribute）—— 預設是查即時價格（Price），要從 Google 財經服務擷取的代號相關屬性。不斷在新增中，功能越來越強大，可以參考此表
3. 開始時間、結束時間與間隔，也是參考同一張表最後的範例，上面的清晰度覺得夠了，就不再多做介紹。

那我這邊用到的屬性有預設的價格（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 天，盤點一下今天學的

1. Google Sheet 公式
   1. googlefinance
   2. vlook
   3. 從另外一張表單讀數據的方式
2. 回顧 D4 的寄信

那今天就到這邊，鐵人賽也接近了尾聲。也進入了最關鍵的 Sheet 的部分，希望內容對大家有所幫助。如果還有問題，透過留言之外，也可以到 Facebook Group，想開很久這次鐵人賽才真的開起來，歡迎來當 Founding Member。如果不想錯過可以訂閱按讚小鈴鐺（？），也歡迎留言跟我說你還想知道什麼做法/主題。我們明天見。