Mismatch between simultanously submitting data from WebApp to spreadsheet and retrieving data from this updated spreadsheet

Question

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";
  }

}

Answer 1

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