[Google Apps] - 以 Google Spreadsheet 當作成績查詢平台 - Demo 2

最近有網友問到，原來的成績查詢只能顯示一筆查到的紀錄，如果要顯示多筆呢?

原先的想法是，在Server端把資料查詢好，透過組成前端HTML字串輸出的方式來達成
結果，超乎意料，他真的被當成文字輸出而不是Bind到原來的HTML Table當中
應該是原有Template在evaluate時，原有的HTML Elements會先輸出到前端，然後再從Server
下載字串輸出，這部分就沒有重建HTML DOM了

查了一下 Google App Script Document 有一個範例可以套用 Calling Apps Script functions from a template，可以直接將 Spreadsheet的內容 Dump出來變成前端的 Data Source.

於是將程式稍微修改一下，原先查詢介面，新增一個 Checkbox 來判斷前端的查詢，以判斷走不同的路徑

在 程式碼.gs 的 doPost 加入一個參數的判斷，如果前端勾選 isShowAll，就改抓 qryResult2.html來顯示

function doPost(e){
  var id=e.parameter.id;
  var authCode=e.parameter.authCode;
  var isShowAll=e.parameter.isShowAll;  
  if (isShowAll){
    var tmpl = HtmlService.createTemplateFromFile('qryResult2');
    return tmpl.evaluate();
  } else {
    return qryScores(id,authCode);
  }
}

qryResult2.html則將原本 qryResult.html 中

這一大段程式碼，改用迴圈的方式來Dump資料

這邊可以看到，使用 data 去承接 Server 端 qryScoresAll() 所回傳的Data (陣列型態) 所以透過 for-loop 將 Data 在前端組成 HTML Code 輸出

function qryScoresAll() {
  var iRow=3;
  var sheetApp = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1lIUUdZximIlLNM6UXjCAeKeqYJTHtIXBFq9iLEuap1c/edit#gid=0');
  var OutputRecord="";
  
  var sheetScore = sheetApp.getSheetByName('學期成績');
  
  return sheetScore.getDataRange().getValues();
}

就可以做成 Data Dump了。

如果是要做條件篩選輸出多筆，該怎麼做?

可以改寫 qryScoresAll() 把篩選的資料，以陣列的方式輸出，這邊就不再說明 :)

測試網址:
https://script.google.com/macros/s/AKfycbz4QFQDkgaLGlEFnF10pvRXxKyp_kP9dpdDQkeKZpM/dev

Read more

[Google Apps] -- 座位表產生器 on mobile device

網友 Tom 針對之前發表的 [Google Apps]--My First Google Apps Script
來信詢問是否可以在蘋果裝置上執行

之前測試在Mobile Browser上無法執行相關的 App Script
試著在Android手機上安裝 Google Spreadsheet App
結果也是一樣，因為圖形按鈕被當成影像下載到Client端
無法指定Script

想說，可能是限制只能在 Desktop 上面的瀏覽器執行吧

可是想一想，script 是在 Cloud上執行的
應該是怎麼呼叫的問題

於是Google了一下，查到了這個解法
http://webapps.stackexchange.com/questions/87346/add-a-script-trigger-to-google-sheet-that-will-work-in-android-mobile-app

透過 onEdit() 這個 event，去 trigger server side function
於是在 B1 這個 cell 上，做了一個下拉選單 (透過資料驗證)

function onEdit(e) {
  if (e.range.getA1Notation() == 'B1') {
    if ('Run' == e.value) {      
      setSeat();
      e.range.clear();
    }
  }
}

當選擇 Run 時，就可以執行座位重排的程式

這樣透過 Mobile 的 Google Spreadsheet App 就可以執行了

建立資料驗證的方式

1.在Cell B1 上按右鍵，選驗證資料

2.按下 條件那一列的表格圖示 (例如 ... 那一格)

3. 在 '參數設定' 加上驗證的文字 Run，並選取作為驗證資料的範圍

4.完成，按儲存

這樣在 B1 就會出現下拉的圖示了

Read more

[Google Apps] - 以 Google Spreadsheet 當作成績查詢平台

本來要趕在跨年前發布這篇文章，當作是2015年的Ending之作
結果因為關帳一忙，就delay到今天
就當作開年的第一篇文章吧

緣起於年前有網友來信詢問，如果要將學生成績輸入在 Google Spreadsheet 讓學生可以透過網頁查詢成績，當下覺得這是個很有趣的Topic也應該是很好的應用

於是一開始先建立了一個成績試算表，想說撰寫一個查詢函數，將資料轉成JSON格式回傳即可，但是將試算表部署成網路應用程式之後，一直告訴我找不到 doGet 函數，後來參考這一篇講解，發現是搞錯方向了

應該是要新增一個Apps Script專案，再從專案中去開啟Spreadsheet

首先，先新增一個Google Apps Script專案

將無標題專案及 myFunction 修改為 學生成績查詢 以及 doGet

同時新增兩個 html，Index & qryResult (檔案 | 新增 | HTML檔) 當作前端介面的 Template

doGet指的就是前端查詢時會觸發的 event (即 http 的 Get)

在這裡我們透過內建的 HtmlService 將設計好的 HTML 範本丟回給查詢的前端

在這裡的設計，是當前端觸發 Apps Script 時，我們會回應一個輸入學號與驗證碼的頁面

在 Index.html 中 (檔案 | 新增 | HTML 檔案)，我們設計輸入學號與驗證碼的欄位，供前端輸入 在 Form Action 的地方，可以看到有一個類似ASP輸出變數值的寫法

< form action="< ?= serviceUrl ? >" class="login-form" method="post" role="qryForm" >
< div class="form-group">
< label class="sr-only" for="id" >學號< /label >
    < input class="form-username form-control" id="form-username" name="id" placeholder="學號..." type="text" / >
  < /div>
< div class="form-group">
< label class="sr-only" for="authCode">驗證碼
< input class="form-password form-control" id="form-password" name="authCode" placeholder="驗證碼..." type="password" / >
  < /div>
< button class="btn" type="submit" >查詢< /button >
< /form>

對應到 doGet 函數中，就可以明瞭這中間的運作方式，我們在Template中指定的變數，可以在doGet中透過 evaluate()的方式將變數值輸出到前端頁面

function doGet(e){
  var tmpl = HtmlService.createTemplateFromFile('Index');
  tmpl.serviceUrl = ScriptApp.getService().getUrl();
  return tmpl.evaluate();
}

同時，在 Index.html中，我們採用了Form submit的方式將資料回傳到後端，所以相對應的也必須要有 doPost 來承接前端的POST

function doPost(e){
  var id=e.parameter.id;
  var authCode=e.parameter.authCode;
  
  return qryScores(id,authCode);
}

前端傳進來的Form data可以透過 e.parameter.xx 的方式來擷取，這樣我們就可以得到前端的輸入值，並交給 qryScores 來處理我們要得到的結果(可以把 qryScores 當作是 Model)

這邊要特別注意的是，Spreadsheet是被拿來當作資料儲存的地方，Apps Script只是一連串的指令碼，我們仍然需要 create 一個 Spreadsheet 的 Instance 來對檔案進行操作，而我們的Spreadsheet 與 Apps Script 都是在雲端，所以必須要以 openByUrl 的方式來開啟 Spreadsheet 檔

設定方式，在 Google Drive 找到該檔案，按右鍵取得連結，同時須做共用的設定 (進入共用設定後選進階)，如果是要公開給別人使用，權限要注意一下，不然會一直被要求取得開啟檔案權限的

function qryScores(id,authCode) {
  var iRow=3;
  var isAuthenticated=false;
  var sheetApp = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1lIUUdZximIlLNM6UXjCAeKeqYJTHtIXBFq9iLEuap1c/edit#gid=0');
  
  var sheetScore = sheetApp.getSheetByName('學期成績');
  var sheetStudent=sheetApp.getSheetByName('學生帳號');
  
  // check id & authCode
  Logger.log('Verify id & authCode...');  
  while (sheetStudent.getRange(iRow-1, 1).getValue()!=''){
    Logger.log(sheetStudent.getRange(iRow-1, 1).getValue());
    if (sheetStudent.getRange(iRow-1, 1).getValue()==id && sheetStudent.getRange(iRow-1, 2).getValue()==authCode){
      isAuthenticated=true;
      break;
    }
    iRow++;
  }
  
  if (!isAuthenticated){
    return HtmlService.createHtmlOutput('認證錯誤!');
  }else{
    var tmpl = HtmlService.createTemplateFromFile('qryResult');
    tmpl.Student_ID = sheetScore.getRange(iRow, 1).getValue();
    tmpl.Student_Name = sheetScore.getRange(iRow, 2).getValue();
    tmpl.Chn_1st = sheetScore.getRange(iRow, 3).getValue();
    tmpl.Eng_1st = sheetScore.getRange(iRow, 4).getValue();
    tmpl.Math_1st = sheetScore.getRange(iRow, 5).getValue();
    tmpl.Chn_2nd = sheetScore.getRange(iRow, 6).getValue();
    tmpl.Eng_2nd = sheetScore.getRange(iRow, 7).getValue();
    tmpl.Math_2nd = sheetScore.getRange(iRow, 8).getValue();
    tmpl.Chn_3rd = sheetScore.getRange(iRow, 9).getValue();
    tmpl.Eng_3rd = sheetScore.getRange(iRow, 10).getValue();
    tmpl.Math_3rd = sheetScore.getRange(iRow, 11).getValue();
    tmpl.Chn_quiz = sheetScore.getRange(iRow, 12).getValue();
    tmpl.Eng_quiz = sheetScore.getRange(iRow, 13).getValue();
    tmpl.Math_quiz = sheetScore.getRange(iRow, 14).getValue();
    tmpl.Chn_sum = sheetScore.getRange(iRow, 15).getValue();
    tmpl.Eng_sum = sheetScore.getRange(iRow, 16).getValue();
    tmpl.Math_sum = sheetScore.getRange(iRow, 17).getValue();
    tmpl.Chn_avg = sheetScore.getRange(iRow, 18).getValue();
    tmpl.Eng_avg = sheetScore.getRange(iRow, 19).getValue();
    tmpl.Math_avg = sheetScore.getRange(iRow, 20).getValue();
    tmpl.total_sum = sheetScore.getRange(iRow, 21).getValue();
    tmpl.total_avg = sheetScore.getRange(iRow, 22).getValue();
    tmpl.rank = sheetScore.getRange(iRow, 23).getValue();
    return tmpl.evaluate();
  }
}

回到程式，在 成績試算表 中，有兩個 Sheet，一個用來登錄成績，一個用來驗證學號的驗證碼，這邊用比較陽春的方式來設計，主要是做一個示範，並不鼓勵採用這種做法

當驗證通過時，將頁面導向 qryResult.html 這個 Template，一樣透過 evaluate() 將對應的變數值輸出到前端，結果如下：

順帶一提，當欄位很多時，如果要從 Spreadsheet 快速產生 html 格式，可以先將 Spreadsheet 用網頁的方式下載下來 (檔案 | 下載格式 | 網頁(.zip) )，將內容整理一下貼到 template 中

最後，也是最重要的部分，就是要將 Google Apps Script 這個專案發佈到網路上成為應用程式

將應用程式執行為的方式，如果設定為  "我" ，網路上的使用者打開連結時，會被要求取得執行權限的(會發送 email 通知你自己)

因為趕時間，所以就沒有往當初設想的方向去測試，除了用繞檔案的方式來做查詢，網路上也有實作用下 SQL來查詢的方式(就像是透過 ODBC 來查詢 Excel 檔)

這一次的實作，倒也讓自己收穫不少，像學到了可以把 Apps Script 當作是 MVC 的平台，.gs 當作是 Controller/Model，doGet/doPost 可以當作 Routing Rule，搭配 View Template 來與前端互動，倒是還蠻方便的 :)

測試網址: https://script.google.com/macros/s/AKfycbwj3VxM_jpQ2514VYk0B47Wkes9hMq_am96LPZik9TK/dev 帳號:10105 密碼:MoGu7b

Read more

Surface Pro 4 安裝 Android Studio 所遇到的問題與排解

最近想在新買的 Surface Pro 4 上面安裝 Android Studio，來練習開發 Android App

參考 Android 6 Tutorial 第一堂（2）建立Android Studio開發環境 中所講解的安裝步驟，安裝完要進行設定就開始遇到問題

首先是找不到 jvm.dll，明明就存在於 Java 安裝路徑中，但開起來就是找不到

JAVA_HOME環境變數也加了，Path也指到 bin 了，就是不行

只好將 Java 全部移除，Android Studio 也移除，全部重來

依照下列步驟

1. 先安裝 Java SDK，將 JAVA_HOME & Path 都設定好

2. 安裝 Android Studio

啟動 Android Studio，就OK了

研判應該是之前安裝過 Java 的其他版本，路徑跟這一次安裝的不同

在 Java 控制面板中，有 Java Runtime Environment 設定，這邊是會影響參考路徑的

雖然有試著在這邊修改路徑，但可能是沒重新開機，所以還是找不到正確的地方

但都重裝了，所以也無法驗證

OK，按照文章的步驟都設定好了，接著照 Android 6 Tutorial 第一堂（3）開始設計Android應用程式 開啟新的專案，很期待的執行的 Run App

結果

Intel HAXM 沒有啟用，所以無法執行 AVD 啟動模擬器

好照著前一篇文章，安裝 Intel HAXM，竟然出現 您的 CPU 不支援 VT-x

什麼!! Surface Pro 4 可是最新的CPU，怎麼可能不支援!!

Google 一下 Surface 跟 VT-x 的文章，說微軟在 Firmware 中把他 Disable 了，要看之後的更新有沒有將這部分 Enable

但文章大多是 2013 年左右的

總算有一篇文章提到了 微軟 的 Hyper-V

那就試著把 Hyper-V 關掉，再安裝一次 Intel HAXM，就可以了!!

所以，結論就是...遇到問題還是要實際去Try，別看到一些文章說不行就放棄了!

Read more

[Excel VBA] 用 VBA 自動產生小計

Excel 本身提供小計的功能，可以讓資料依據某個欄位，做相關的小計，非常方便。

那如果，透過VBA該怎麼做呢?

首先，先準備一個底稿

透過ADO將資料擷取至Recordset中，將資料依據底稿的順序排列好，利用

Range("A2").CopyFromRecordset rs

將資料放到 A2 這個 Cell 展開

再透過 Subtotal

Range("A1:V29").Select

Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22)

將資料依據第一個欄位 (GroupBy:=1)，做小計 (xlSum)，並決定要小計的欄位 (利用 Array() 將小計的欄位置入 TotalList)

大功告成！

顏色的部分可以透過

Cells(iRow, 3).Interior.Color = RGB(218, 238, 243)

來達成

Read more

[Excel VBA] 用 VBA 控制 Group Column 收合/展開

最近有個需求，是要將 Excel 群組的欄位，在打開檔案時，依時間自動展開該季的欄位，並將其他季的欄位位縮合

網路上查到的，多半是用 Outline.ShowLevels

但這個主要是當有群組階層時，可以針對哪一個階層設定展開或收合，並不是我要用的

就寫一個Sub Routine，依季的時間，傳入 Range Column 將該區間的欄位收合或展開

Public Sub GroupColumnCollapse(oSheet As Worksheet, s As String, e As String, collapse As Boolean)
  If oSheet.Range(s & "1:" & e & "1").EntireColumn.Hidden Then 
    oSheet.Range(s & "1:" & e & "1").EntireColumn.Hidden = collapse
  End If
End Sub

用到的是 Range EntireColumn.Hidden 這個屬性來控制

Read more

[Google Apps]--FetchURLs using Google App Script

很久沒有寫 Google Apps Script 了(也只寫過一次好嗎)...

有網友看了之前寫的一篇文章 [Google Apps]--My First Google Apps Script，問我一個是否可以使用 Google Spreadsheet 來作為 server alive & failed notice 的腳本

查了一下 Google Apps Script 文件，發現有現成的API可以使用，就直接套用做成一個可以探詢 Server List 是否存活的App

https://docs.google.com/spreadsheets/d/1uPhxJGx9HyvLDOSEW8NDDWen2I543Lq3RW-p4uTEKsc/edit?usp=sharing

網友提出的想法是這樣的，他希望有兩個Sheet，一個紀錄要查詢的Server，一個紀錄Server 的 Administrator，當網站不存在時，就通知網站管理員

我大致上開了兩個工作表如下：
一個是 Server List

一個是管理員 List，兩者間為1:1關係(如多個管理員，就以逗號區分，理論上應該可以自動判斷，如果不行就要自己 split)

資料準備好了，那我們就來寫程式吧(如何建立的過程，請參考前一篇 [Google Apps]--My First Google Apps Script)

function loadServerList() {

 var sheetApp = SpreadsheetApp.getActiveSpreadsheet();
 var sheet1=sheetApp.getSheets()[0];
 var sheet2=sheetApp.getSheets()[1];
 var serverlist=new Array(), adminlist= new Array();
 var iRow=2,i;
             
  // 先將 Server List 讀進 Array
 // iRow=2 從第二列開始讀，讀到空白行
 while (!sheet1.getRange(iRow, 1).getValue()==""){
  serverlist[iRow-2]=sheet1.getRange(iRow, 1).getValue();
  iRow++;
 }

 iRow=2;

  // 將 Administrator 讀進 Array
 while (!sheet2.getRange(iRow, 1).getValue()==""){
  adminlist[iRow-2]=sheet2.getRange(iRow, 1).getValue();
  iRow++;
 }

  // 透過 try catch 攔截錯誤，發送 email
 try{
  for (i=0; i < serverlist.length; i++){
   var response = UrlFetchApp.fetch(serverlist[i]);
   MailApp.sendEmail(adminlist[i], 'UrlFetchApp OK:'+serverlist[i], response.getContentText());
   Logger.log(response.getContentText());
  }
 } catch (e){
  MailApp.sendEmail(adminlist[i], 'UrlFetchApp Failed:'+serverlist[i], '');
  Logger.log("Url Fetch Failed!")+serverlist[i];
 }
}

Google Apps Script 內建的 Service API 有一個是 URLFetch
讓你可以擷取網站的資料回傳，我們就透過這個 Service，來當作網站存活的查詢，查詢失敗就透過 exception block 傳送失敗通知 

這邊為了測試這個 Service 的運作方式，所以將查詢成功的部份也寄送 email 得到的 mail 結果如下：(第二個網址是我故意寫錯，測試用)

這樣就可以執行了 (App 執行的過程，會要求認證與授權，授權過之後就不會再出現了) 

...好像還少了什麼...

 誰去執行?

當然是要讓系統自己做啊!

 按一下 Script Editor 上面的時鐘，新增觸發程序，完工!

Read more