TWStockPrice.js

/**  
    The example code is used to illustrate the method of updating stock price on Google Spreadsheet
    See the post for details - https://www.lightblue.asia/realtime-tw-stockprice-in-google-spreadsheet
**/

function STOCK_() {
    this.ticker = "";
    this.name = "";
    this.curPrice = 0.0;
    this.high = 0.0;
    this.low = 0.0;
    this.volume = 0;
}

function main() {
  /* Get ActiveSheet */
  var sheet = SpreadsheetApp.getActiveSheet();
  var aryData = sheet.getDataRange().getValues();

  /* Use IP directly to avoid DNS failure */
  var host = "163.29.17.179";  

  /* The TAIEX API is not stable so I need failover */
  var max_retry = 3
  
  /* Row0 can be used for title of table, so we start read stock from Row1 */  
  var timestamp = Date.now()+60000;
  var stockInfoUrl = "http://"+host+"/stock/api/getStockInfo.jsp?json=1&delay=0&_="+timestamp+"&ex_ch="
  for (var i = 1; i < aryData.length; i++) {    
    stockInfoUrl += "tse_"+aryData[i][0]+".tw"
    if (i+1 < aryData.length) {
      stockInfoUrl += "%7C"
    }
  }
  /* Poke TWSE homepage to get session id, e.g.JSESSIONID=5B050F7AF3A3CD64091F772D7D589A82; Path=/stock */
  var respForSession = UrlFetchApp.fetch("http://"+host+"/stock/index.jsp?lang=zh-tw");
  var server_cookie = respForSession.getHeaders()["Set-Cookie"]
  var cookie = server_cookie.substring(0, server_cookie.indexOf(";")+1)
  var headers = {
    "Cookie" : cookie
  }    
  var options = {
    "method" : "get",
    "headers" : headers
  };  
  
  for (var retry = 0; retry < max_retry; retry++) {
    var stocks = getStock(stockInfoUrl, options);
    var updateTime = Utilities.formatDate(new Date(), "GMT+8", "yyyy-MM-dd HH:mm");
    if (stocks == null || stocks.length == 0) {    
      sheet.getRange("H1").setValue("Fail to sync "+updateTime+" CST");
      //sheet.getRange("H2").setValue(stockInfoUrl);
      continue;
    } else {      
      for (var i = 0; i < stocks.length; i++) {
        var stock = stocks[i]      
        /* getRange() starts from 1 */
        sheet.getRange(i+2, 2).setValue(stock.name);
        sheet.getRange(i+2, 3).setValue(stock.curPrice);
        sheet.getRange(i+2, 4).setValue(stock.volume);
        sheet.getRange(i+2, 5).setValue(stock.high);
        sheet.getRange(i+2, 6).setValue(stock.low);
      }
      sheet.getRange("H1").setValue("Last Update "+updateTime+" CST");
    }    
    return;
  }
}



function getStock(stockInfoUrl, options) {
  var stocks = [];
  try {        
    //stockInfoUrl = "http://163.29.17.179/stock/api/getStockInfo.jsp?json=1&delay=0&_=1487753506584&ex_ch=tse_0050.tw"
    var response = UrlFetchApp.fetch(stockInfoUrl, options);
    Logger.log(response.getContentText());

    var jsonData = JSON.parse(response.getContentText());
    if (jsonData.rtmessage === undefined || jsonData.rtmessage !== "OK") {
      Logger.log("Fail to fetch response");
      return null;
    }
    for (var i = 0; i < jsonData.msgArray.length; i++) {
      var stock = new STOCK_()
      var respStock = jsonData.msgArray[i];
      
      stock.ticker   = respStock.c;
      stock.curPrice = respStock.z;
      stock.name     = respStock.n;
      stock.high     = respStock.h;
      stock.low      = respStock.l;
      stock.volume   = respStock.v;
      stocks[i] = stock;
    }
    return stocks;
    
  } catch (error) {    
    return null;    
  }
}