Google Sheets 抓上櫃股票股價

在 Google Sheets 試算表中，上市股票股價可以使用 GOOGLEFINANCE() 取得股價。 

上櫃OTC股票則需另外用指令碼(Apps Script)處理。

1. 新增一個工作表，假設名稱為「上櫃股票」，並填入股票代碼。
   如下圖：
   
   
   
   
   
2. 「工具」 -> 「指令碼編輯器」
   2021-12-12：新版位置已改至「擴充功能」 -> 「Apps Script」
   
   
   
   
   
3. 填入以下程式碼，選擇執行「mainOTC()」

   function getDataOTC(sheet_name, col_code, col_stock_name, col_price, col_updtime, col_err, row_start, row_end) {
     var spreadsheet = SpreadsheetApp.getActive();
     var rows = spreadsheet.getRange(sheet_name + "!" + col_code + row_start + ":" + col_code + row_end);
     var numRows = rows.getNumRows();
     var data = rows.getValues();
     var options = {
       //若來源網址SSL不安全(或過期)出現Exception: SSL Error，且確定來源資料安全時，可關閉validateHttpsCertificates
       //'validateHttpsCertificates': false
     };
     for (var i = 0; i < numRows; i++) {
       var code = data[i][0];
       //上市
       //https://mis.twse.com.tw/stock/api/getStockInfo.jsp?ex_ch=tse_2353.tw&json=1&delay=0
       //上櫃
       //https://mis.twse.com.tw/stock/api/getStockInfo.jsp?ex_ch=otc_6488.tw&json=1&delay=0
       var url = "https://mis.twse.com.tw/stock/api/getStockInfo.jsp?ex_ch=otc_" + code + ".tw&json=1&delay=0"
       //舊的
       //var url = "https://mis.tse.com.tw/stock/api/getStock.jsp?ch=" + code + ".tw&json=1&_=";
       try {
         var response = UrlFetchApp.fetch(url);
         var json = response.getContentText("UTF-8");
         var data_arr = JSON.parse(json);
         spreadsheet.getRange((sheet_name + "!" + col_stock_name) + (row_start + i)).setValue(data_arr.msgArray[0].n);//股票名稱
         // data_arr.msgArray[0].y 股價(昨天成交價)
         // data_arr.msgArray[0].z 股價(最近成交價)
         var stock_price = data_arr.msgArray[0].z.trim();
         if (isNaN(parseFloat(stock_price))) {
           throw ("股價數值異常:" + stock_price);//盤中可能抓不到股價，抓到"-"
         }
         spreadsheet.getRange((sheet_name + "!" + col_price) + (row_start + i)).setValue(stock_price);
         spreadsheet.getRange((sheet_name + "!" + col_updtime) + (row_start + i)).setValue("股價更新:" + Utilities.formatDate(new Date(), "GMT+8", "yyyy-MM-dd HH:mm:ss"));
         spreadsheet.getRange((sheet_name + "!" + col_err) + (row_start + i)).setValue("");
       } catch (e) {
         spreadsheet.getRange((sheet_name + "!" + col_err) + (row_start + i)).setValue("Err:" + Utilities.formatDate(new Date(), "GMT+8", "yyyy-MM-dd HH:mm:ss") + " " + e);
       }
       Utilities.sleep(1000);//等1秒再繼續下一個
     }
   }
   
   function mainOTC() {
     getDataOTC(
       "上櫃股票", //工作表名稱
       "A",//股票代碼在工作表的欄位
       "B",//股票名稱在工作表的欄位
       "C",//股價在工作表的欄位
       "D",//更新時間在工作表的欄位
       "E",//錯誤訊息在工作表的欄位
       2,  //資料從第幾列開始
       21  //資料在第幾列結束
     );
   }

   
   
   
   
   
   
4. 因為要在工作表自動填入資料，所以須授權程式可存取 Google Docs
   若之後要取消授權，可於 https://myaccount.google.com/permissions?pli=1 取消。
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
5. 抓取結果
   
   
   
   
   
6. 可設定「觸發條件」，讓程式每隔一段時間自動執行，避免每次都要手動執行。
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   

參考：

• https://stackoverflow.com/questions/24867386/ssl-error-when-making-a-request-via-urlfetchapp
  SSL Error when making a request via UrlFetchApp
• https://zys-notes.blogspot.com/2020/01/api.html
  證交所即時資訊API