I have a scheduling system using Google Form, Sheet, Calendar using API Script what I want is to have limit per day on the calendar. For Example, I want 5 volunteers on Monday and I want to make sure no one can book on Monday since I already have 5 volunteers. Please help me
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var lastRow = sheet.getLastRow();
var lastColumn = sheet.getLastColumn();
// Calendar to output requests
var calendar = CalendarApp.getCalendarById(‘e6i9tfp012mt4m5ic81v9lisp0@group.calendar.google.com’);
// Creates an object from the last form submission
function getSubmission(){
this.timestamp = sheet.getRange(lastRow, 1).getValue();
this.name = sheet.getRange(lastRow, 2).getValue();
this.reason = sheet.getRange(lastRow, 3).getValue();
this.date = new Date(sheet.getRange(lastRow, 4).getValue());
this.time = sheet.getRange(lastRow, 5).getValue();
this.email = sheet.getRange(lastRow, 6).getValue();
// Adjust time and make end time
this.date.setHours(this.time.getHours());
this.date.setMinutes(this.time.getMinutes());
this.endTime = new Date(this.date);
this.endTime.setHours(this.time.getHours() + 1);
return this;
}
// Check for date conflicts
function getConflicts(request){
var conflicts = calendar.getEvents(request.date, request.endTime);
if (conflicts.length < 1){
request.header = "Confirmation";
request.message = "Your appointment has been scheduled.";
} else {
request.header = "Conflict";
request.message = "There was a scheduling conflict. Please reschedule.";
}
return conflicts.length;
}
// Creates a calendar event using the submitted data
function updateCalendar(request){
var event = calendar.createEvent(
request.name,
request.date,
request.endTime
)
}
function sendEmail(request){
MailApp.sendEmail({
to: request.email,
subject: request.header,
htmlBody: makeEmail(request)
})
}
// ————–Main————–
function main(){
var request = getSubmission();
if (getConflicts(request) < 1){
updateCalendar(request);
}
sendEmail(request);
}