使用Google Apps Script 及SQL在試算表中提取指定義工的所有時數記錄

最近我加入了一間NGO工作，該NGO主要服務對象是四肢嚴重殘疾的人士。本文簡記如何實作一個提取目標記錄的程式。

Published in

Curtis’s Digital Life

6 min readDec 22, 2019

A) 背景
B) 函數 QUERY() 與 SQL
C) 巨集 與 Google Apps Script
 C1) 界面準備
 C2) 搜尋按鈕
D) 結語

English readers may check out the alternative version here.

Retrieving volunteer records by SQL with Google Apps Script in Google Spreadsheet

Recently, I am working for an NGO supporting those severe handicapped. This article documents how I retrieve target…

medium.com

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()函數的條件中。最後，整段程式碼會搜出該義工的時數記錄，並複製去一個新的工作表中。