[教學] 用 ChromeOS/Linux 開發 Google Apps Script WebApp (API Content Service)

本帖最後由 javacomhk 於 2022-7-4 04:07 編輯

之前一篇是關於 Google Apps Script WebApp 嘅 backend 功能
這篇就討論一下，點樣利用Google Apps Script Webscrape 返來嘅 stock data 提供 JSON API 嘅 ContentService。

（F1）首先好似上一篇 Webscrape 那樣在 main.gs 增加一個 function hkej_scrape() 去 stock360.hkej.com 獲取香港所有股票的更多 data 。如下

function hkej_scrape() {
var webAppSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("HKEJ");
webAppSheet.getRange("A1:E3000").clearContent();
const datestr = (new Date().toLocaleString('sv',{timeZone: 'Asia/Hong_Kong'})).slice(0, 19).replace(/-/g, "").replace(/:/g, "").replace("T", " ");
const url = ["https://stock360.hkej.com/stockList/all/99999999?&p=1","https://stock360.hkej.com/stockList/all/99999999?&p=2","https://stock360.hkej.com/stockList/all/99999999?&p=3","https://stock360.hkej.com/stockList/all/99999999?&p=4","https://stock360.hkej.com/stockList/all/99999999?&p=5","https://stock360.hkej.com/stockList/all/99999999?&p=6","https://stock360.hkej.com/stockList/all/99999999?&p=7","https://stock360.hkej.com/stockList/all/99999999?&p=8","https://stock360.hkej.com/stockList/all/99999999?&p=9","https://stock360.hkej.com/stockList/all/99999999?&p=10"]
for (i = 0 ; i < url.length; i++) {
var res=UrlFetchApp.fetch(url[i]);
var content = res.getContentText();
const $ = Cheerio.load(content);
var valdatestr = $('#datepicker').attr('value')
const a = $("tbody > tr").each((index,element) =>
{
const tds = $(element).find("td");
if ($(tds[0]).text() >= '00001' && $(tds[0]).text() <= '99999' ) {
webAppSheet.appendRow([$(tds[0]).text()+".HK", $(tds[1]).text().replace("s",""), $(tds[5]).text(), $(tds[6]).text(), valdatestr]);
}
}
);
console.log('HKEJ scraped page ' + (i+1) + " " + valdatestr + ' !!')
}
}

複製代碼

（F2）然後在 React Test Project 的 Google Sheet 內加上一個新的 worksheet 叫 HKEJ

（F3）新增 Triggers 設定每日定時上午6時執行 function hkej_scrape() 去獲取股票價值。

（F4）測試 function hkej_scrape() 成功後，worksheet HKEJ 就如下圖

（F5）在 main.gs 修改 doGet 及新增 doPost 如下：

function doGet(e) {
if (typeof e.parameters.request === 'object' || typeof e.parameters.stock === 'object') {
return doPost(e);
}
else
return HtmlService.createTemplateFromFile("index")
.evaluate()
.addMetaTag("viewport","width=device-width, initial-scale=1.0")
}
function doPost(e) {
if (e.parameters.request.includes("getStock") ) {
return ContentService.createTextOutput(JSON.stringify(getStock(e.parameters.stock))).setMimeType(ContentService.MimeType.JSON)
}
else if (e.parameters.request.includes("getData") ) {
return ContentService.createTextOutput(JSON.stringify(getData())).setMimeType(ContentService.MimeType.JSON)
}
else if (e.parameters.request.includes("hello")) {
return ContentService.createTextOutput('Hello, world '+JSON.stringify(e))
}
else {
return ContentService.createTextOutput(JSON.stringify(e))
}
}

複製代碼

（F6）在 main.gs 新增 function getStock(codes) 如下：

function getStock(codes) {
var hkejSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("HKEJ");
const hkejVals = hkejSheet.getDataRange().getValues();
const uppercodes = codes.map(code => code.toUpperCase());
var filterStock = hkejVals.filter(function (row) {
if (uppercodes.includes(row[0])) { // 0-based
return row
}
})
if (filterStock.length > 0) {
Logger.log(filterStock)
var jsn = []
filterStock.forEach(function (r, i) {
jsn[i] = {}
jsn[i]['code'] = r[0]
jsn[i]['name'] = r[1]
jsn[i]['price'] = r[2]
jsn[i]['change'] = r[3]
jsn[i]['date'] = r[4].slice(0,-3).replace('年','').replace('月','').replace('日','')
})
// Logger.log(JSON.stringify(jsn))
return jsn
}
else {
var jsn = {}
return jsn
}
}

複製代碼

（F7）使用 Test Deployments WebApp URL 地址例如是 https://script.google.com/macros/s/AAbbccddeeyyzz/dev

使用 Chrome Browser 測試 API 及返回的 JSON 如下

（F7a）測試 hello https://script.google.com/macros/s/AAbbccddeeyyzz/dev?request=hello

（F7b）測試 getData https://script.google.com/macros/s/AAbbccddeeyyzz/dev?request=getData

（F7c）測試 getStock https://script.google.com/macros/s/AAbbccddeeyyzz/dev?request=getStock&stock=00001.HK&stock=00005.HK

(F8) 最後將個 WebApp deploy to production 然後將個 WebApp URL 可以去登記的 URL shortener 例如 tinyurl.com 去進行測試
https://tinyurl.com/react9?request=getStock&stock=00001.HK&stock=00005.HK&stock=00700.HK

使用 curl 去測試 doPost

$ curl -L --data "request=getStock&stock=00001.HK&stock=00006.HK" https://tinyurl.com/react9
[{"code":"00001.HK","name":"長江和記實業","price":56.4,"change":-0.15,"date":"20220523"},{"code":"00006.HK","name":"電能實業","price":52.3,"change":-2.3,"date":"20220523"}]
$ curl -L --data "request=getData" https://tinyurl.com/react9
[{"key":"apple","value":"green"},{"key":"banana","value":"yellow"}]

複製代碼

使用 wget 去測試 doPost

$ wget -qO - --post-data='request=hello' https://tinyurl.com/react9
Hello, world {"postData":{"contents":"request=hello","length":13,"name":"postData","type":"application/x-www-form-urlencoded"},"contentLength":13,"queryString":"","contextPath":"","parameter":{"request":"hello"},"parameters":{"request":["hello"]}}

複製代碼

在 Google Apps Script Editor 修改完代碼，是需要更新 Linux 的檔案，方法是在 Terminal 輸入