[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

[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 就會出現下拉的圖示了


[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).getValue()!=''){
    Logger.log(sheetStudent.getRange(iRow, 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

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 設定,這邊是會影響參考路徑的

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

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

image

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,別看到一些文章說不行就放棄了!

[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)
來達成

[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 這個屬性來控制

[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 上面的時鐘,新增觸發程序,完工!