運用 Google Scripts 實現Google Form 報名人數限制

google form google app script

Lebron 2020-01-29 14:24:14 ‧ 10823 瀏覽

分享至

想必大家都知道Google Form用來做系統報名是非常方便的工具，但...

如果報名人數有限制的話，Google Form並沒有一套檢查機制可以執行

但我們知道Google Form的資料會儲存在Google試算表內，所以可以通過Google試算表及內建的Google Script，

來顯示Google表單的某下拉選單 [如日期或是梯次] 來判定Google Form報名人數是否已滿

而且我們有時候希望報名的人能夠提前預約活動

本範例就在展示如何預約未來3-10天的Google報名...

使用技術
-1. Google Form 的下拉清單
-2. Google Sheet 新增一個Sheet檢核是否人數額滿
-3. Google 指令碼編輯器

步驟一
先建立一個Google 報名表單，需含有一個參加日期的下拉選單

步驟二
建立此Google Form的Google Sheet

步驟三
建立第二個Sheet活動梯次表，並建立如下欄位：
活動梯次/日期二/年度/月份/日期一/星期/額滿否/剩餘數量/容納數量/報名數量
並從選單->格式->數值->改成純文字

步驟四
建立活動梯次表Sheet內的資料與欄位相關語法
-1. 先填入[年度]、[月份]、[日期二]、[星期]的資料，這裡範例是先填寫2020/1 ~ 3 月
-2. [日期一]第一列的欄位公式 = C2&D2&E2，其餘欄位公式直接拖拉後依序產生
-3. [活動梯次]第一列公式 =C2&"/"&D2&"/"&E2&"("&F2&")" 其餘欄位公式直接拖拉後依序產生
-4. [容納數量]我們先填每梯次都是15人
-5. [報名數量]第一列公式 = sumifs('表單回應 1'!D:D,'表單回應 1'!B:B,A2)
這裡稍微要說明一下，我要加總的是表單回應1的D欄，也就是[報名人數]這一欄，但是必須符合表單回應1的B欄 = 活動梯次表的A2欄，也就是表單回應1的報名日期 = 活動梯次表[活動梯次]
-6. [剩餘數量] = [容納數量] - [報名數量]
-7. [額滿否]第一列 = if(H2<=0,"已額滿","尚未額滿")，其餘類推H3,H4,...

最後修改的結果大致如下圖：

步驟五
來寫Google Script
-1. 在Google Sheet 上面選單選擇[工具]->[指令碼編輯器]
-2. 填入以下程式代碼：

    // call your form and connect to the drop-down item
  var form = FormApp.openById("1cKjOCtj-h7u-S1XHDG5UW72JuK69YTEvb1_9aNjZyCQ"); 
  var namesList = form.getItemById("309397191").asListItem();
  var d = new Date();
  var date2 = new Date(d.getTime() + (3 * 24 * 60 * 60 * 1000))  //抓取後三天的日期
  var datestring = Utilities.formatDate(date2, "UTC+8", "yyyyMMdd"); //改成string  
  var ss = SpreadsheetApp.getActive();
  var names = ss.getSheetByName("活動梯次表");

 //從第二列開始抓到getMaxRows() - 1列，抓取第一個欄位值，也就是[活動梯次]這個欄位
  var namesValues = names.getRange(2, 1, names.getMaxRows() - 1).getValues(); 
 
 //從第二列開始抓到getMaxRows() - 1列，抓取第二個欄位值，也就是[日期二]這個欄位 
  var dateValues = names.getRange(2, 2, names.getMaxRows() - 1).getValues();
  
 //從第二列開始抓到getMaxRows() - 1列，抓取第七個欄位值，也就是[額滿否]這個欄位 
  var fullflag = names.getRange(2, 7, names.getMaxRows() - 1).getValues(); //是否額滿的註記
  
  var DateOptions = [];  //Google Form 關於日期的下拉選單值

  j = 0;
 
  // convert the array ignoring empty cells
  for(var i = 0; i < namesValues.length; i++)   
  {
    if(dateValues[i][0] >= datestring)
    {
      if(fullflag[i][0] == '尚未額滿')
      {
        DateOptions[j] = namesValues[i][0];
        j = j + 1;
      }
    }    
    if(j >= 10)  //只抓後面3-10天
    {
      break;
    }
  }  
    // 產生你的報名日期下拉清單
  namesList.setChoiceValues(DateOptions);

[Hint]
這邊有兩行，可能大家不知道去哪抓取
var form = FormApp.openById("這是你的GoogleForm的代碼編號");
var namesList = form.getItemById("這是你的報名日期控制項的ID").asListItem();

GoogleForm的代碼編號就是當你開啟這個GoogleForm編輯狀態時，去看他顯示在瀏覽器的超連結：
https://docs.google.com/forms/d/1cKjOCtj-h7u-S1XHDG5UW72JuK69YTEvb1_9aNjZyCQ/edit#responses
也就是1cKjOCtj-h7u-S1XHDG5UW72JuK69YTEvb1_9aNjZyCQ這一段

至於報名日期控制項的ID要進入瀏覽器的開發人員選項來做觀察，如下圖：