Another idea you can try on your current setup is that you could use an onFormSubmit trigger. And once the response is submitted, it is compared to the previous rows that were added to the sheet to check if there have been any conflict with your data.
Upon confirming there is, just remove the last response. If no conflict, then let it be and end the script there.
Sheet Script:
function checkConflict(e) {
var sheet = e.range.getSheet();
var lastRow = sheet.getLastRow();
// exclude the last submitted response
var timeSlots = sheet.getRange(2, 3, lastRow - 2, 2).getValues();
var lastResponse = e.values;
var lastStart = new Date(lastResponse[2]).getTime();
var lastEnd = new Date(lastResponse[3]).getTime();
// convert timeslots to comparable data type
timeSlots = timeSlots.map(timeSlot => [timeSlot[0].getTime(), timeSlot[1].getTime()]);
// get conflicts
var conflict = timeSlots.filter(timeSlot => (timeSlot[0] <= lastStart && timeSlot[1] >= lastStart) ||
(timeSlot[0] <= lastEnd && timeSlot[1] >= lastEnd));
// remove lastRow if conflict is 1
if(conflict.length) {
sheet.deleteRow(lastRow);
MailApp.sendEmail(lastResponse[4],
"Time Slot conflict",
"Your recent response was removed due to a conflict. Your submitted time slot is the following:\n" +
"From: " + new Date(lastStart) + "\n" +
"To: " + new Date(lastEnd));
}
else {
MailApp.sendEmail(lastResponse[4],
"Time Slot confirmed",
"Your time slot is now confirmed. Confirmed time slot is the following:\n " +
"From: " + new Date(lastStart) + "\n" +
"To: " + new Date(lastEnd));
}
}
Sample Data:
Trigger:
Emails confirm:
Emails conflict:
Output:
Note:
- This approach doesn't check your input before the submission. It just removes the last response afterwards IF there have been conflicts with the previous slot timings to that one.
- Also, the one who sent the response wouldn't know that his slot was confirmed or not, not unless you ask for his email in the form and send confirmation afterwards the checking (whether his submitted response was removed due to conflict, or approved due to it having no conflict)
- If you have a time slot of 9:00 - 10:00, it won't accept another response with 9:00 in it since (>=, <=) is inclusive. What you can do is to pick timeslots where starting has 1/31 minute (e.g.
9:01-10:00, 8:31-9:30) or the ending slots has 59/29 minutes (e.g. 9:00-9:59, 8:30-9:29).
- If that is a hassle, then you need to adjust the conditional on checking conflicts. You should be able to figure it out.
