I wrote a web app to make reservations for a concert. People can select from certain dates and can choose with how many people they come by selecting an amount of seats.
I use a spreadsheet to gather all the reservations. I wrote down the last 2 functions of the process. If I press the button to order the seats, 2 functions activate: validate() on the frontend and getLastCheck() on the backend. This is a last check whether the asked amounts of seats are still available. If so, the data is written to the spreadsheet.
I tested the script a few times with 4 other colleagues and we simultaneously tried to book 3 seats on the same date. Since there were only 10 seats left, 2 of us should get the message that the "seats are not booked". Sometimes it worked fine, other times only 1 of us received the message "seats are not booked" and the other 4 people (1 too many!) could book their seats. In that case we exceeded the maximum capacity.
I presume that the belated updating from the spreadsheet (which results in a wrong evaluation) is caused by the time of traffic from and to the spreadsheet. Is there a way to solve this wrong evaluation when simultaneously submitting the data?
Frontend function:
function validate() {
var info = {};
info.firstName = document.getElementById("first-name").value;
info.lastName = document.getElementById("last-name").value;
info.mail = document.getElementById("email").value.trim();
info.date = document.getElementById("select-date").value;
info.seats = document.getElementById("select-seats").value;
google.script.run.withSuccessHandler(function(result){
console.log(result);
}).getLastCheck(info);
}
backend function:
function getLastCheck(info) {
var listAll = wsRsrv.getRange(2, 5, lastRowRsrv, 2).getValues();
var dates = listAll.map(function(element){ return element[0]; });
var seats = listAll.map(function(element){ return element[1]; });
var sum = 0;
var diff = maxPerDate - info.seats;
for (var i = 0; i<listAll.length; i++) {
if (info.date == dates[i]) { sum += Number(seats[i]); }
}
if (sum <= diff) {
wsRsrv.appendRow([new Date(), info.lastName, info.firstName, info.mail, info.date, info.seats]);
return "seats are booked";
} else {
return "seats are not booked";
}
}
I tested it out and it seems to work right.
function lockedFunction() {
var active_spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
// BEGIN - start lock here
const lock = LockService.getScriptLock();
try {
lock.waitLock(5000); // wait 5 seconds for others' use of the code section and lock to stop and then proceed
} catch (e) {
console.log('Could not obtain lock after 5 seconds.');
return "Error: Server busy try again later... Sorry :("
}
// note: if return is run in the catch block above the following will not run as the function will be exited
const active_sheet = active_spreadsheet.getActiveSheet()
const new_start_row = active_sheet.getLastRow() + 1;
// Run the function
const data = {
firstName: 'David',
lastName: 'Salomon',
mail: 'mail@example.com',
date: new Date(),
seats: 10
}
getLastCheck(data)
SpreadsheetApp.flush(); // applies all pending spreadsheet changes
lock.releaseLock();
// END - end lock here
return;
}
You can check this other thread to have more ideas on how to implement it but as mentioned in the comments, you can check the Google documentation