(終極版)用Google Sheet做出報名、連署、抽獎完整解決方案 ~ Practical DBM

這篇文章(尚未完成)大部分介紹程式開發過程中循序漸進寫出來的九個版本，然後在課堂上逐一解說程式設計重點。

在開始介紹此程式開發歷程之前，應有認知: Google Apps Script: 3.3B weekly executions; 8.7M weekly end users; and 270K weekly developers.、Container-bound Scripts v.s. Standalone Scripts

首先，參考第一版Google Sheet「報名 (v.1 GAS 執行 display Sidebar for Announce Checklist)」建立副本，由於原來的Google Sheet是已經有一個連動的Google Form(如下圖的[報到]工作表名稱前有紫色圖示)，所以建立副本也應該有產生出兩個檔案(至雲端硬碟查看)。

這個版本重點是參考在Sidebar置入表單範例，在Sidebar置入HTML語法，呈現表單讓使用者決定挑選哪些欄位要複製到[名單公開(部分個資)]工作表，表單填好送出就會依sendForm設定去執行confirming2Clone函式，而其callback設定會回到ready執行google.script.host.close()將側邊欄關閉。(程式語法和邏輯會在課堂上說明，而且在程式碼裡面也有語法範例參考網址。)

這個showSidebar4announce函式的執行是綁定在Spreadsheet自訂選單(如下圖的[自訂功能]選單就是用Script程式產生出來的，有關用addMenu自訂選單並將其寫在onOpen函式的介紹就不再贅述。)

第一次會跳出對話框要求授權，當然後續每一步都要同意允許(就省略擷取畫面囉)。

出現側邊欄讓使用者決定挑選哪些欄位要複製到[名單公開(部分個資)]工作表。

等到函式執行完畢，查看 > 記錄，就可看到傳入confirming2Clone函式的陣列資料，確認是符合選擇的結果。

接著，用第二版Google Sheet「報名 (v.2 GAS 加上 Announce Checklist Confirming2Clone)」建立副本，這個版本重點放在confirming2Clone函式，準備利用儲存格B1，"特定記錄"後面加上記錄有哪些欄位被設定至名單公開。

再來要處理觸發條件設定，新資料存進[表單回應]工作表，程式就會自動將指定欄位複製到[名單公開(部分個資)]工作表，這部分放在第三版Google Sheet「報名 (v.3 GAS 加上 TriggerBuilder for Confirming2Clone)」，用它來建立副本吧。Script程式重點是在addFormSubmissionListener函式，以newTrigger對於forSpreadsheet設定onFormSubmit表單資料進來時就觸發submissionCloning函式執行複製程序，也就是把[表單回應]工作表要複製的那些欄位放進二維陣列，再將二維陣列資料寫到[名單公開(部分個資)]工作表。

下個步驟，讓使用者設定自動回覆確認信啟用/停用，這部分放在第四版Google Sheet「報名 (v.4 GAS 加上 display Sidebar for Auto-Reply setting)」，用它來建立副本吧。Script程式重點是在Sidebar置入HTML語法，呈現表單讓使用者決定是否啟動自動回覆確認信，並指定哪個是要求填寫信箱之欄位。而為了有更充分的訊息讓使用者知道，在showSidebar4autoreply函式兜出sheet發佈後的網址，以getRemainingDailyQuota得知當日發送郵件餘額(以Script程式發送郵件數量是有限制的，每日額度一般Gmail是100人次、G Suite教育版則是1500人次)，並以getConfirmationMessage取得目前設定的表單提交後的訊息。

為了產生自動回覆確認信，就必須再寫個寄發郵件的函式，這部分放在第五版Google Sheet「報名 (v.5 GAS 加上 after confirming for Auto-Reply setting)」，用它來建立副本吧。Script程式重點是在confirming2Reply函式利用儲存格B1，"特定記錄"後面加上最後兩格記錄@自動回覆信箱欄位。至於自動回覆確認信，是在sendmail2user函式組成MailApp.sendEmail的htmlBody，就能寄發郵件了。

不過，兜出sheet發佈後的網址，並非是由Script自動設定發佈喔，因為Script裡面沒有很容易的方式可以讓程式去執行這樣的設定，所以還是要由使用者手動設定，請執行: 檔案 > 發佈到網路 > 從整份文件下拉選單中選擇 [名單公開(部分個資)] 再按下發佈。

如果要讓自動回覆確認信裡面包含填答者在提交後可以編輯的網址，有不少細節需要處理，這部分放在第六版Google Sheet「報名 (v.6 GAS 加上 getEditResponseUrl for Auto-Reply Mail)」，用它來建立副本吧。首先，在showSidebar4autoreply函式的側邊欄內容，可勾選確認信內容是否加入可編輯該筆回應的表單網址。

然後，在confirming2Reply函式判讀儲存格B1，"特定記錄"後面加上最後兩格記錄@自動回覆信箱欄位，大小寫字母分別代表設定信件內容是否加入可進行編輯的網址。

最後，在submissionCloning函式增加參數e，這個參數非常關鍵，如果從e取得的datetime與工作表1最後一筆的timestamp相差無幾(1秒內，即1000毫秒)，應是新填的表單回應，而對於新填或再編輯的表單回應會有不同的處理細節。

既然已經將[名單公開(部分個資)]工作表發佈到網路了，那也可以考慮寫在表單說明讓填表者注意到這項資訊，這部分放在第七版Google Sheet「報名 (v.7 GAS 加上 set Description to the linked Form)」，用它來建立副本吧。Script程式重點是在csetDescription2form函式，以setDescription將文字寫入表單說明中。

暫時來到目前最後一個版本，要處理的設定表單接受回應截止日期時間，這部分放在第八版Google Sheet「報名 (v.8 GAS 加上 TriggerBuilder for Responses Accept)」，用它來建立副本。Script程式重點是以newTrigger對於forSpreadsheet設定timeBased時間觸發stopAcceptingResponses函式執行setAcceptingResponses關閉表單接受回應。另外也做了檢查，若重複執行就要刪除先前設定的觸發條件。

這部分的後續發展(尚未完成)將會是:

哪些欄位要複製到[名單公開(部分個資)]工作表之後要進行 "去識別化" 處理
基於個資保護考量，每日觸發寄信給管理者告知這些資料還在雲端上的存取權限狀態，尤其是停止公告後還在雲端上卻未將存取權限關閉要特別警示。

完成前述版本之後，我們又再加了一個彩蛋功能，提供一個很炫的抽獎程式，這部分放在第X版Google Sheet「報名 (v.X GAS 加上 deploy Lucky Draw Script as Web App)」，用它來建立副本。原本程式雖然是Container-bound Script，但也可以發佈 > 部署為網路應用程式成為Web APP，也就多了一個抽獎網頁程式，這個程式是從這個原始碼(在线演示)改寫而成的，執行抽出得獎者的過程中隨機動態跳出姓名並等待按鈕決定停跳而抽出得獎者。

前述抽獎網頁程式改寫成Google Apps Script，讓它可執行前必須有幾個程序要先做。第一步驟，在[得獎公告]工作表G1儲存格的公式 =JOIN(";",ARRAYFORMULA(CONCAT(FILTER('表單回應'!D2:D, NOT(ISBLANK('表單回應'!D2:D))), "("&FILTER('表單回應'!C2:C, NOT(ISBLANK('表單回應'!C2:C)))&")"))) ，在此標示藍底的D應視[表單回應]工作表中的哪一題是姓名欄位而定，第一題就是D、是第二題就要改成E、是第三題就要改成F、......。

其實G1儲存格的公式並不算太複雜，原則上就是用CONCAT連接姓名、序號兩個欄位值，但必須先用FILTER檢查兩個欄位值都不可為空(也就只到有資料的最後一列)，然後用ARRAYFORMULA將多列的資料都一次處理完，最後讓JOIN使用指定分隔符號(;)將陣列資料串聯在一起而成為完整名單字串。

第二步驟，在[得獎公告]工作表B、C欄自行輸入獎項名稱及數量，抽獎網頁程式的執行則是每抽完一個獎項就依序寫入第2,3,4...列的D,E,F欄，也就能與你自行輸入的獎項名稱對應上。