https://developers.google.com/apps-script/guides/sheets/functions
=price_depth("TCB", $A$1)
=price_stock(stock="TCB", $A$1)
=index_board($A$1)
https://developers.google.com/apps-script/guides/sheets/functions
=price_depth("TCB", $A$1)
=price_stock(stock="TCB", $A$1)
=index_board($A$1)
function onOpen() { | |
var sheet = SpreadsheetApp.getActiveSpreadsheet(); | |
var entries = [{ | |
name : "Refresh", | |
functionName : "refreshLastUpdate" | |
}]; | |
sheet.addMenu("Refresh", entries); | |
}; | |
function refreshLastUpdate() { | |
SpreadsheetApp.getActiveSpreadsheet().getRange('A1').setValue(new Date().toTimeString()); | |
} | |
function price_depth(stock="TCB") { | |
var url = "https://bgapidatafeed.vps.com.vn/getliststockdata/" + stock; | |
// Make an HTTP GET request | |
var response = UrlFetchApp.fetch(url); | |
var responseData = JSON.parse(response.getContentText()); | |
return parseFloat(responseData[0]['lastPrice']) | |
SpreadsheetApp.flush(); | |
} | |
function price_stock(stock="TCB") { | |
var url = "https://bgapidatafeed.vps.com.vn/getliststockdata/" + stock; | |
// Make an HTTP GET request | |
var response = UrlFetchApp.fetch(url, {method : 'get'}); | |
var responseData = JSON.parse(response.getContentText()); | |
if (!Array.isArray(responseData)) { | |
throw new Error( "Call api error" ); | |
} | |
var lastPrice = parseFloat(responseData[0]['lastPrice']); // Giá hiện tại | |
var rPrice = parseFloat(responseData[0]['r']); // Giá tham chiếu | |
var cPercent = parseFloat(responseData[0]['changePc']); // % thay đổi | |
var cPrice = lastPrice - rPrice; // Thay đổi | |
if (lastPrice < rPrice) { | |
cPercent = -cPercent | |
} | |
var ohlcData = []; | |
ohlcData.push([lastPrice, cPrice, cPercent + "%"]) | |
return ohlcData | |
SpreadsheetApp.flush(); | |
} | |
function index_board() { | |
var indexDict = { | |
"10": "VNINDEX", | |
"11": "VN30", | |
"02": "HNX", | |
"03": "UPCOM" | |
}; | |
var indexes = []; | |
for (var key in indexDict) { | |
if (indexDict.hasOwnProperty(key)) { | |
indexes.push(key); | |
} | |
} | |
var url = "https://bgapidatafeed.vps.com.vn/getlistindexdetail/" + indexes.join(","); | |
// Make an HTTP GET request | |
var response = UrlFetchApp.fetch(url, {method : 'get'}); | |
var responseData = JSON.parse(response.getContentText()); | |
if (!Array.isArray(responseData)) { | |
throw new Error( "Call api error" ); | |
} | |
var ohlcData = []; | |
ohlcData.push(["Index", "Thời gian", "Giá tham chiếu", "Giá hiện tại", "Thông tin", "Tổng giá trị", "Khối lượng"]) | |
for (let i = 0; i < responseData.length; i++) { | |
let dataRow = []; | |
dataRow.push(indexDict[responseData[i]["mc"]]); // Tên index | |
dataRow.push(responseData[i]["time"]); // Thời gian | |
dataRow.push(responseData[i]["oIndex"]); // Giá tham chiếu | |
dataRow.push(responseData[i]["cIndex"]); // Giá hiện tại | |
let extra = responseData[i]["ot"].split("|"); | |
let cPrice = parseFloat(extra[0]); | |
let cPercent = parseFloat(extra[1]); | |
if (responseData[i]["cIndex"] < responseData[i]["oIndex"]) { | |
cPrice = -cPrice | |
cPercent = -cPercent | |
} | |
dataRow.push(cPrice + "/" + cPercent + "%"); | |
dataRow.push(responseData[i]["value"]); // Tổng giá trị | |
dataRow.push(responseData[i]["vol"]); // Khối lượng | |
ohlcData.push(dataRow) | |
} | |
// Logger.log(ohlcData) | |
return ohlcData | |
SpreadsheetApp.flush(); | |
} |