[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

3 意見

您好~想請教一下 , 因為剛好也在幫學校找一個簡易的成績登入(老師可自己更新資料)+查詢系統 , 因為不是學程式,這樣看下來還有點吃力 , 若需要 [以 Google Spreadsheet 當作成績查詢平台IN GOOGLE APPS ] , 是可以完全只靠Google Apps Script + Google Spreadsheet就可以辦到嗎(含查詢頁面)? .....thx

Reply

您好,
首先非常感謝您提供教學,
對我來說非常實用,
想麻煩您詳細說明 qryResult.html 的內容,
若不方便也沒關係,謝謝您!

Reply

你好,請問我已經做好此查詢,可將網頁分出給他人做查詢時,他們都無法開啟,請問是否可以教導要在哪邊做設定,謝謝!

Reply

張貼留言