[Google sheet] Get last stock price

readme.md

Create custom function google sheet

https://developers.google.com/apps-script/guides/sheets/functions

How to use

=price_depth("TCB", $A$1)

=price_stock(stock="TCB", $A$1)

=index_board($A$1)

stock_price.gs

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();
}