使用Google Apps Script 及SQL在試算表中提取指定義工的所有時數記錄
最近我加入了一間NGO工作,該NGO主要服務對象是四肢嚴重殘疾的人士。本文簡記如何實作一個提取目標記錄的程式。
A) 背景
B) 函數 QUERY() 與 SQL
C) 巨集 與 Google Apps Script
C1) 界面準備
C2) 搜尋按鈕
D) 結語
English readers may check out the alternative version here.
A) 背景
協會可參考義工記錄進行更好的規劃。協會劃分作幾個部門,不同部門都會安排適合其目標會員的項目。每一次活動後,職員都會上載參與過的義工時數去各自的工作表中。而在每一年,義工都會收到一本精心設計的義工時數記錄簿去手寫記下他們該年的貢獻。
類似以下簡例,所有部門都依循以下格式保存記錄:
Date, StartTime, EndTime, Hours, Event, Staff, Volunteer
臨到年尾,機構會製作一本年刊及準備一個周年典禮去致謝協會中最熱心的義工。通常義工都會與我們核對時數記錄是否一致。分開不同部門記錄雖然能方便職員更快去處理,但當要逆向查出個別義工各散不同部門的記錄就會比較麻煩。
透過Google試算表可以做到以上的資料提取工作,而不用大幅改動現行數據系統。
B) 函數 QUERY() 與 SQL
=QUERY(數據, 查詢敍述)
Google 試算表中有一個其專有的函數QUERY()
,我們可以用SQL來調取所需資料。SQL是一種數據庫管理系統常用的結構化查詢語言(Wikipedia)。像普通試算表函數,我們可以用一個預定條件來提取符合的數據出來。
散於不同工作表的記錄(即AAA與BBB表),我們可以用一個分號;
來將它們垂直合併。而第二部分查詢敍述,以欲找之名為Tom作例:SELECT * WHERE COL6=‘Tom’ ORDER BY Col1
會告訴程式去找出有Tom參與過的整行記錄,並按日期順序列出。
=QUERY({AAA!1:500;BBB!1:500},”SELECT * WHERE Col7 =’Tom’ ORDER BY Col1")
請小心 SQL 語言用單引號而 Google 試算表公式用雙引號。
C) 巨集 與 Google Apps Script
公式可以應付上述需求,但這段函數或者對同事來說太嚇人。不怕!我們用巨集(Macros)來隱藏整個過程。
C1) 界面準備
先創建一個新工作表,當中至少要有一個橫向拉延儲存格來用來作搜尋欄位。在該欄位附近添加一個代表放大鏡的搜尋按鈕及一些指示字句。
接著,前往 Tools > Script Editor 存入以下程式碼。
function CheckTargetRecords() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange(‘A1’).activate();
var nameToSearch = SpreadsheetApp.getActiveSheet().getActiveCell().getValue();
var formulaToUse = ‘=QUERY({\’AAA\’!1:500;\’BBB\’!1:500},”select * where Col7 =\’’+nameToSearch+ ‘\’order by Col1")’
spreadsheet.insertSheet(nameToSearch);
spreadsheet.getActiveRange().setFormula(formulaToUse);
}
簡單來說,以上程式碼用來抽取A1
儲存格,再寫入變數nameToSearch
。然後這個變數會作為剛提及的QUERY()函數的條件中。最後,整段程式碼會搜出該義工的時數記錄,並複製去一個新的工作表中。
C2) 搜尋按鈕
當在指定儲存格輸入好要找的義工後,去 Tools > Macros > checkTargetRecords 就可以執行程式碼。或者再簡單一些,用放大鏡圖案來代替這堆菜單。右點圖案,按第三個選項就可以讓該圖案作為執行程式checkTargetRecords
的捷徑。
配置完成後,用Mary這個名來測試一下。
叮叮!左點一下放大鏡圖案,所有來自不同部門的記錄著Mary的義工記錄都調出來了。
D) 結語
以Google試算表為本的記錄方式保持不變之餘,以上的小程式也可以節省NGO對員工額外的訓練時間及金錢成本。
查看更多
還有Google 試算表的其他小貼士可以看一看。
參考來源
喜歡這篇文章可以拍手支持!