Code.gs - Seat Booking Form
/*#### Seat Booking Form ####
*
* Used on a multiple choice item in Google Forms, this can update the number of seats
* after each booking.
*
* Requires: A Google Form with a connecting Google Sheet.
*
*/
//#### GLOBALS ####
var SEATS = 100; //Your seating capacity
var FORM_ID = "1nzWf5MG9xKMoC1w4klyu18kKFxkavRkSTcE0gT8WQkc";//Add your form ID
var SESSION_ITEM_ID = 1492288992; //Use findItemId function in Test.gs
var SS_ID = "1E-jlB1uSiGscv-VOeF0HfKCUtF5z9FE2b5YPhi1Fg4Q"; //Add your Spreadsheet ID
var SHEET_NAME = "Count"; //Add your Sheet tab name
var SESSION_DATA_RANGE = "A2:C5"; //Add the range of booking items your selecte sheet tab
/* ###################################################################
* Seat booking function
*
* Requires: Set trigger Edit>Current project's triggers > select onSubmit
*
* After the form is submitted, it check the seating information from the
* sessions and then updates the form with the remaining seats.
* If seats are full for 1 session, that session is removed.
* If all seats are full for all sessions, the form is closed.
*/
function onFormSubmit() {
//Wait a little for the form to submit the results to the Google Sheets
sleep(2000);
var sessionData = SpreadsheetApp
.openById(SS_ID)
.getSheetByName(SHEET_NAME)
.getRange(SESSION_DATA_RANGE)
.getValues();
var form = FormApp.openById(FORM_ID)
var sessionID = form.getItemById(SESSION_ITEM_ID);
//Filter item data by seats remaining < SEATS
var remainingSessionData = sessionData.filter(function(item){
return item[2] > 0;
});
if(remainingSessionData.length == 0){
// Close the form.
form.setAcceptingResponses(false);
form.setCustomClosedFormMessage(
"Registration is now closed."
);
}else{
var itemList = remainingSessionData.map(function(item){
var itemCombined = item[0] + " (" + item[2] + " seats remaining)"
return itemCombined;
});
sessionID.asMultipleChoiceItem().setChoiceValues(itemList)
}
};
/* ###################################################################
* Sleep function
*
* Paused the processing of the form data for an assigned period of time.
*
* @param {number} a number in milliseconds
*/
function sleep(milliseconds) {
var start = new Date().getTime();
for (var i = 0; i < 1e7; i++) {
if ((new Date().getTime() - start) > milliseconds){
break;
}
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
/*#### Seat Booking Form ####
*
* Used on a multiple choice item in Google Forms, this can update the number of seats
* after each booking.
*
* Requires: A Google Form with a connecting Google Sheet.
*
*/
//#### GLOBALS ####
var SEATS = 100; //Your seating capacity
var FORM_ID = "1nzWf5MG9xKMoC1w4klyu18kKFxkavRkSTcE0gT8WQkc";//Add your form ID
var SESSION_ITEM_ID = 1492288992; //Use findItemId function in Test.gs
var SS_ID = "1E-jlB1uSiGscv-VOeF0HfKCUtF5z9FE2b5YPhi1Fg4Q"; //Add your Spreadsheet ID
var SHEET_NAME = "Count"; //Add your Sheet tab name
var SESSION_DATA_RANGE = "A2:C5"; //Add the range of booking items your selecte sheet tab
/* ###################################################################
* Seat booking function
*
* Requires: Set trigger Edit>Current project's triggers > select onSubmit
*
* After the form is submitted, it check the seating information from the
* sessions and then updates the form with the remaining seats.
* If seats are full for 1 session, that session is removed.
* If all seats are full for all sessions, the form is closed.
*/
function onFormSubmit() {
//Wait a little for the form to submit the results to the Google Sheets
sleep(2000);
var sessionData = SpreadsheetApp
.openById(SS_ID)
.getSheetByName(SHEET_NAME)
.getRange(SESSION_DATA_RANGE)
.getValues();
var form = FormApp.openById(FORM_ID)
var sessionID = form.getItemById(SESSION_ITEM_ID);
//Filter item data by seats remaining < SEATS
var remainingSessionData = sessionData.filter(function(item){
return item[2] > 0;
});
if(remainingSessionData.length == 0){
// Close the form.
form.setAcceptingResponses(false);
form.setCustomClosedFormMessage(
"Registration is now closed."
);
}else{
var itemList = remainingSessionData.map(function(item){
var itemCombined = item[0] + " (" + item[2] + " seats remaining)"
return itemCombined;
});
sessionID.asMultipleChoiceItem().setChoiceValues(itemList)
}
};
/* ###################################################################
* Sleep function
*
* Paused the processing of the form data for an assigned period of time.
*
* @param {number} a number in milliseconds
*/
function sleep(milliseconds) {
var start = new Date().getTime();
for (var i = 0; i < 1e7; i++) {
if ((new Date().getTime() - start) > milliseconds){
break;
}
}
}
