空中的向日葵

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

原先的想法是，在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 中

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

網友 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 就可以執行了

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

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

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

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

在 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>

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

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

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();
  }
}

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

最近想在新買的 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，別看到一些文章說不行就放棄了!

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)

來達成

網路上查到的，多半是用 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

很久沒有寫 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


資料準備好了，那我們就來寫程式吧(如何建立的過程，請參考前一篇 [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 上面的時鐘，新增觸發程序，完工!