[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

[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

[Google Apps]--My First Google Apps Script

最近看到 Google Apps 推出 Google Apps Script 可以用來整合 Google Drive 資源，腦海中頓時有了出現救世主了的想法。

Google Apps 有多強大，這就不用我在此贅述，Google 一下大概就知道了。

其實要說 Google Apps 可以完全取代 微軟 的 Office，還言之過早。畢竟到目前為止，除了 Kingsoft Office Suit 相容性比較高之外，其他版本的 Office 可以說用「慘」一個字來形容。更何況是雲端的 Office。不過，話說回來，雲端 Office 本來就不是要來取代桌面 Office 的，他只是提供一種便利的服務，讓你隨時隨地可以取用文件，並進行分享與協同運作。

但未來，當這樣的形態成了大家共同的作業模式，你就不能輕忽他會帶來的影響性了。

OK，進入正題，我為什麼要寫 Google Apps Script？

主要是我想了解 Google Apps 的整合運用，當我們在雲端建構好呈現的介面，透過 JSON 資料傳輸技術，我們就可以很容易的將企業內的資料（或者雲端上的資料）呈現到外部環境（當然，假設安全條件無虞的情況下），而不需要花大錢去建構一整套系統來達成這個運用，更不用說還要考慮後續 Maintain 的所有事情（理想狀況）。

怎麼開始呢？

連到這個網址 https://developers.google.com/apps-script/ 按一下 Get Started 這個按鈕。
所有說明文件與範例都在上面，照著步驟一個一個做，其實不難。

問題是要做什麼運用？

Office 最強的地方在哪裡？其實不在編輯文字或做出很漂亮的簡報、文件，而是企業內部強大的應用，Excel + VBA。

Google Apps 中加入了 Google Apps Script，那就等於整個 Google Apps 平台，都可以讓你使用，用多少付多少，這就是重點了。

所以我的想法很簡單，我怎麼樣在 Googls Sheets 中重現我在 Excel 中所做的事，請往下看。

我之前幫老婆寫了一支排座位的程式，可以用來排班級座位，每次段考/小考，就可以要學生依座位表的內容就座，就是要防止作弊啦！

按一下講桌，就可以重新產生一張座位表，很方便。

所以，起手式，就用這一支來實作看看吧！

首先進入 Google Drive，建立一張試算表，將格子拉一拉.....還真難拉 T_T

而且還沒有框線！（還是我找不到？）所以就用顏色標示區隔。其實用熟了，拉表格也還蠻快的，但就是有點彆扭。（Google加油蛤！）

接下來，在工具下拉，選指令碼編輯器

第一次產生，他會要你選擇要產生哪一類型的 Script，選試算表那一個。

接著就會產生一個 程式碼.gs 的頁面以及範例程式。

接下來，就開始寫程式吧！（不用想太多，就跟寫 Macro 一樣）

檢視原始檔複製到剪貼簿列印關於

1. function setSeat(){  
2.   var iCol;  
3.   var iRow;  
4.   var sheetApp = SpreadsheetApp.getActiveSpreadsheet();  
5.   Logger.log('讀取座位表');  
6.   var sheet1=sheetApp.getSheetByName('座位表');  
7.   Logger.log('參數設定');  
8.   var sheet2=sheetApp.getSheetByName('參數設定');  
9.     
10.   iCol = sheet2.getRange(1,2).getValue();  
11.   iRow = sheet2.getRange(2,2).getValue();  
12.       
13.   var a=[];  
14.   var i, j, k;  
15.   var seed;  
16.   var iFix, iCount, iStart  
17.   var i1  
18.       
19.   iFix = sheet2.getRange(3,2).getValue();  
20.   iStart = 0;  
21.     
22.   var startrow = 8;  
23.   var startcol = 2;  
24.   
25.     
26.   //Randomize    ' 對亂數產生器做初始化的動作。  
27.   
28.   for (iCount = 1;iCount<=iFix + 1;iCount++){  
29.     i1 = sheet2.getRange(3 + iCount,2).getValue();  
30.     for (i=iStart+1;i<=i1;i++){             
31.       a[i] = getSeed(a,i1);  
32.     } //i  
33.     iStart = i - 1;  
34.   } //iCount  
35.     
36.   k = 1;  
37.   for (i = 1;i<=iRow;i++){  
38.     for (j = iCol;j>=1;j--){  
39.       sheet1.getRange(startrow + (i - 1) * 2, startcol + (j - 1) * 2).setValue(a[k]);  
40.       k++;  
41.     } //j  
42.   } //i  
43. }  
44.   
45. function getSeed(a,i){  
46.   var seed = Math.floor(Math.random()*i)+1;    // 產生 1 到 36 之間的亂數值。  
47.   for (var j = 1;j<=i;j++){  
48.     if (seed == a[j]){  
49.       seed = getSeed(a,i);  
50.     }  
51.   }  
52.   return seed;  
53. }  

我把主要要產生座位編排的程式，寫在 setSeat() 這個 function 中，SpreadsheetApp 是內建的物件，透過它可以取得試算表的控制權。

我將一些參數寫在 參數設定 這個 Sheet 中，如果要改變規則（例如：人數增減，座位增加等），就讓老婆自己修改，改一下座位表的版面即可。（要知道，當程式寫得不夠彈性時，會有奪命連環Call）

程式很簡單，就是藉由亂數產生不重複的編號，丟進去 Array 中，再 Dump 到工作表的相關位置上。程式碼從 VBA 移植過來，沒有花太多時間，主要是在 Excel Object Model Mapping 到 Google Sheets Object Model 的熟悉程度，還好 Google Apps Script 的編輯器，已經有 Code Insight 的功能，要了解並不困難。

OK，程式寫完了.....啊...怎麼執行啊？

Google Apps Script 的 IDE 有執行 & Debug 的功能

選取後，執行就可以馬上知道結果

瞧，跟 Excel 執行的結果沒兩樣呢！那麼就把 講桌 的 Button 加上去吧....噫？竟然沒有！

這麼不 Friendly，User 怎麼知道要去選工具，選指令碼管理員，選要執行的程式，這樣使用經驗會很糟糕吧！

研究了一下，看來只能用圖形的方式來做了，用插入 | 繪圖 的方式，自己做一個 Button 的圖形擺上去，其實這裡很方便的就提供您指定指令碼的功能

把它指派好 setSeat 這樣這個圖形就有執行程式的功能了

此程式已設定為共用，請參考以下連結

https://docs.google.com/spreadsheet/ccc?key=0AiEO0LGS-8xNdGpMVnVDejNMdWJJbzFzbEgxc3Fta2c

附記1：原本以為在 iPad 上也可以使用，結果 Script 無法在 iOS 上執行，可惜

附記2：要能夠執行 Script，還不是那麼簡單勒...要一大堆設定...還在 Try 當中，不過可以看到協同運作的功能了，一個開啟的使用者改變了 Sheets 內容，其他開啟的使用者的內容也會跟著更新

Read more