运用 Google Scripts 实现Google Form 报名人数限制

想必大家都知道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要进入浏览器的开发人员选项来做观察，如下图：

步骤六
再来我们就是要让Google Script做测试
我把Google Script改成MyFirstApp，gs档名改成UpDateMyDateItem [这里都建议不要用中文名称]
可以先按下执行看看有没有Bug
若成功的话，应该会如下图：

报名日期直接显示未来3-10天的日期

步骤六
到最后一步了，加油...
最后要把专案发布出去，让Google自动执行程式
请执行选单[现有专案的执行程序]

然后我建议新增两种触发条件，一种是按小时驱动，一种是提交表单后驱动

终于大功告成啦

如果某日的报名人数超过15人，是否下拉选单就不显示了，大家可以试试看