Last active
March 22, 2020 09:34
-
-
Save avence12/5483fd9d38637bf4c3ea4f9a50e88d71 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** | |
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; | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment