[教學] 用 ChromeOS/Linux 開發 Google Apps Script WebApp (WebScrape backend)

javacomhk

前二篇講關於開發 Google Apps Script WebApp，今篇主要是講 Google Apps Script 的 backend timer 功能即是用 javascript 定時去獲取 scrape web data 然後儲存在 Google Spreadsheet 用作 WebApp 的內容。

如果用 node.js 去做 Web Scrape, 通常會用 cheerio.js 或者用 document.querySelectorAll 的方法。Google Apps Script 也可以用 cheerio (見 https://github.com/tani/cheeriogs)

cheerio.js 既原理就係用 JQuery 的 syntax 去個 webpage 度根據 CSS selector 去攞相關嘅 text content。如果用 Chrome Browser 試的 JQuery code, 首先要在 Chrome Browser 裝個 JQuery Inject 嘅 extension, 然後打開個網頁後，打開個 Developer tools 嘅 console 就可以打入段 code 去試。
例如 Chrome Browser 已安裝好 JQuery Inject Extension 後
（1）去到 http://www.aastocks.com/tc/stock ... -con.aspx?index=HSI
（2）Browser 右上角的 Extensions 內開啟 JQuery Inject
（3）在 console 內打入 code 如下

1. $("table#tblTS2 > tbody > tr").each((index,element) =>
   
2.    { console.log($(element).text());})

複製代碼

就會見到這內容， 代表 "table#tblTS2 > tbody > tr" 的 CSS selector 是可以獲取相關的內容


以下就示範如何使用 Google Apps Script 去獲取 AASTOCKS 的 webpage table 內的每日股票價值
（E1） 如果要用 Google Apps Script 使用 JQuery Syntax 的 Webscrape 就要在 Google Apps Script Project 內使用 Cheerio.gs https://github.com/tani/cheeriogs, 安裝方法是在 Google Apps Script Project 內 左邊菜單選擇 "Libraries" 右側的 + 號去 "Add a Library" 輸入 Project Key (1ReeQ6WO8kKNxoaA_O0XEQ589cIrRvEBA9qcWpNqdOP17i47u6N9M5Xh0) 及最新版本 14 後便可使用。
（E2）然後在  main.js 內加入這 function 代碼內容

1. function aastocks_scrape() {
   
2.   var webAppSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("AASTOCKS");
   
3.   webAppSheet.getRange("A1:E30").clearContent();
   
4.   const datestr = (new Date().toLocaleString('sv',{timeZone: 'Asia/Hong_Kong'})).slice(0, 19).replace(/-/g, "").replace(/:/g, "").replace("T", " ");
   
5.   var res=UrlFetchApp.fetch("http://www.aastocks.com/tc/stocks/market/index/hk-index-con.aspx?index=HSI");
   
6.   var content = res.getContentText();
   
7.   const $ = Cheerio.load(content);
   
8.   const a = $("table#tblTS2 > tbody > tr").each((index,element) =>
   
9.    {
   
10.      const tds = $(element).find("td");
    
11.      const namecell = $(tds[0]).find("span");
    
12.      const codecell = $(tds[0]).find("a");
    
13.      console.log("stockcode is "+$(codecell).text());
    
14.      console.log("stockname is "+$(namecell).text());
    
15.      console.log("price is "+$(tds[1]).text());
    
16.      console.log("up/down "+$(tds[2]).text());
    
17.      webAppSheet.appendRow([$(codecell).text(), $(namecell).text(), $(tds[1]).text(), $(tds[2]).text(), datestr]);
    
18.    }
    
19.   );
    
20.   SpreadsheetApp.flush();
    
21. }

複製代碼

（E3）然後在 React Test Project 的 Google Sheet 內加上一個新的 worksheet 叫 AASTOCKS 如下圖。


（E4）要令到 Google Apps Script WebApp 每日定時去獲取股票價值，就要在左邊菜單新增 Triggers

  Trigger 的設定如下，頻率通常不要過密，否則很容易比人block。例如每日下午收市時便可以。


（E5）測試成功後


留位

javacomhk

Webscrape 完就可以將個 stock portfolio 輸入 Google spreadsheet 及計算盈利

（E5）在 Google Spreadsheet 新增 worksheet 叫 Portfolio 如下內容，其中 Column F 就是取自每天 Webscrape 的股票價格，其 F2 formula 是 =vlookup($A2,AASTOCKS!$A$1:$E$30,3)



（E6）新增一個 google doc 例如下圖 其中{{ variable }}，就會在 mail merge 時被取替



（E7） 新增 function sendPortfolioEmail() 在 main.gs 內

1. function sendPortfolioEmail(){
   
2.   // fill in google doc ID
   
3.   var id = 'FILL IN DOC ID HERE' ;
   
4.   var forDriveScope = DriveApp.getStorageUsed(); //needed to get Drive Scope requested
   
5.   var url = "https://docs.google.com/feeds/download/documents/export/Export?id="+id+"&exportFormat=html";
   
6.   var param = {
   
7.     method      : "get",
   
8.     headers     : {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
   
9.     muteHttpExceptions:true,
   
10.   };
    
11.   var html = UrlFetchApp.fetch(url,param).getContentText();
    
12.   var htmlout1 = html;
    
13. // email send out with replaced fields
    
14.   var portfolioSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Portfolio");
    
15. 
    
16.   const result = portfolioSheet.getDataRange().getValues();
    
17.   const numRows = result.length
    
18.   
    
19.   var TODAYPL = 0;
    
20.   var TOTALPL = 0;
    
21.   for (var i = 1; i < numRows; i++) {
    
22.      htmlout1 = htmlout1.replace("{{R"+i+"C1}}",result[i][0].toString().substring(0,5));
    
23.      htmlout1 = htmlout1.replace("{{R"+i+"C2}}",result[i][1]);
    
24.      htmlout1 = htmlout1.replace("{{R"+i+"C3}}",Utilities.formatString( '%.2f',result[i][4]));
    
25.      htmlout1 = htmlout1.replace("{{R"+i+"C4}}",Utilities.formatString( '%.2f',result[i][5]));
    
26.      htmlout1 = htmlout1.replace("{{R"+i+"C5}}", result[i][6].toLocaleString('en-US', { style: 'currency', currency: 'USD' }));
    
27.      htmlout1 = htmlout1.replace("{{R"+i+"C6}}", result[i][7].toLocaleString('en-US', { style: 'currency', currency: 'USD' }));
    
28.      htmlout1 = htmlout1.replace("{{R"+i+"C7}}", result[i][8]);
    
29.      TODAYPL += parseFloat(result[i][6]);
    
30.      TOTALPL += parseFloat(result[i][7]);
    
31.   }
    
32. 
    
33.   console.log("TODAYPL "+ TODAYPL.toLocaleString('en-US', { style: 'currency', currency: 'USD' }))
    
34.   console.log("TOTALPL "+ TOTALPL.toLocaleString('en-US', { style: 'currency', currency: 'USD' }))
    
35. 
    
36.   var nameSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
    
37.   htmlout1 = htmlout1.replace("{{TODAYPL}}",TODAYPL.toLocaleString('en-US', { style: 'currency', currency: 'USD' }));
    
38.   htmlout1 = htmlout1.replace("{{TOTALPL}}", TOTALPL.toLocaleString('en-US', { style: 'currency', currency: 'USD' }));
    
39. 
    
40.   var userSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
    
41.   const users = userSheet.getDataRange().getValues();
    
42.   
    
43.   for (var i = 0; i < users.length; i++) {
    
44.     var firstname = users[i][0];
    
45.     var lastname = users[i][1];
    
46.     var email  = users[i][3];
    
47.     var htmlout2 = htmlout1;
    
48.     htmlout2 = htmlout2.replace("{{title_1}}","Dear "+ firstname +" "+lastname);
    
49.     htmlout2 = htmlout2.replace("{{heading_1}}","Portfolio Summary");
    
50. 
    
51.     MailApp.sendEmail({
    
52.       to: email,
    
53.       subject: "Your portfolio Summary",
    
54.       htmlBody: htmlout2
    
55.       });
    
56.     console.log('email sent to '+ email + ' ' + firstname + ' !!')
    
57.   }
    
58. }

複製代碼

（E8） 新增 trigger 令 function sendPortfolioEmail() 定時在收市後執行 send email.  成功後收到電郵如下：

javacomhk

留位

bongbong3481

留位學野，高汁啊