Ensuring appendRow is executed and return data from last row while dealing with concurrency with google apps scripts

Question

I have an html form that takes data (a student observation) and saves that data to a spreadsheet, the function it's supposed to a. generate an id b. append the row to the spreadsheet (the student observation) including the id c. checks that the observation was saved (row was appended) by retrieving the last id saved on the sheet d. returns the id saved if the generated id matches the id retrieved from the last row, otherwise returns a 0

The issue:

the code works most of the time, the problem is that sometimes the user gets 0 (instead of the id that it's supposed to be returned) and I'm not sure why is that if the append operation doesn't seem to fail.

My code:

//save observacion submitted
function saveObservation(studentICID, studentOSISICID, studentName, studentClass, dateObserv, catObsev, textObserv, textCommit){
  //return variable initialized
  var success = 0;
  //lock the app to deal with concurrency for 3 seconds
  var lock = LockService.getPublicLock();
  lock.waitLock(3000);  // wait 3 seconds before conceding defeat.
  try{
    // Log the email address of the person running the script.
    var submittedByEmail = Session.getActiveUser().getEmail().slice(0, Session.getActiveUser().getEmail().indexOf("@"));
    var now = new Date();
    //get current date/time
    var dateTime = Utilities.formatDate(now, "GMT-5", "yyyy-MM-dd' 'HH:mm:ss");
    //get the sheet to log observations
    var sODA = ssODA.getSheetByName("Observaciones");
    var rowsBeforeObservation = sODA.getLastRow();
    //get observation id
    var observID = getObservationID(studentICID, studentOSISICID);
    //log observation 
    sODA.appendRow([observID, dateTime,submittedByEmail,studentICID,studentOSISICID,studentName,studentClass,dateObserv,catObsev,textObserv,textCommit]);
    //log observation to Logger for backup
    Logger.log([
      observID, 
      dateTime,
      submittedByEmail,
      studentICID,
      studentOSISICID,
      studentName,
      studentClass,
      dateObserv,
      catObsev,
      textObserv,
      textCommit
    ]);
    //update spreadsheet
    SpreadsheetApp.flush();
    //check if observation was logged
    var lastobservIDOnSheet = sODA.getRange(sODA.getLastRow(),1,1,1).getValue();
    if(lastobservIDOnSheet == observID){
      success = observID;
    }
    Logger.log('success: ' + success);
    return success;
  }catch(e){
    //Logger.log("error in saveObservation: " + e);
    var eventcode = "saveObsFailed";
    var event = "failure";
    log_event_text(eventcode,event,e)
  }finally { //release lock
    lock.releaseLock();
  }
}

What I have tried:

1. at first I thought i had to lock the save operation becuase I thought it was an issue with concurrency, so I added the locking part

2. then one teacher was saving a student observation and reported that it was getting a 0 instead of an observation id so I added spreadsheet flushing as indicated in this similar Question

but again, one teacher recently reported getting a 0 and the logs confirmed it (he was the only one using the html form). I'm not sure where this one comes from as I even tested it myself and with other users as working prior, having also only one person saving, so at this point I'm not sure what else I should try or maybe my lock / flush implementation is wrong?

Thank you in advance.

Answer 1

Try using SpreadsheetApp.flush() between the write and the read from the Spreadsheet.

var lastobservIDOnSheet = sODA.getRange(sODA.getLastRow(),1).getValue();

The problem could be that sometime you are not getting the activeUsers email

Try it this way:

function saveObservation(studentICID, studentOSISICID, studentName, studentClass, dateObserv, catObsev, textObserv, textCommit) {
  var success = 0;
  var lock = LockService.getPublicLock();
  lock.waitLock(3000);  // wait 3 seconds before conceding defeat.
  try {
    var submittedByEmail = Session.getActiveUser().getEmail().slice(0, Session.getActiveUser().getEmail().indexOf("@"));
    if (submittedByEmail) {
      var now = new Date();
      var dateTime = Utilities.formatDate(now, "GMT-5", "yyyy-MM-dd' 'HH:mm:ss");
      var sODA = ssODA.getSheetByName("Observaciones");
      var rowsBeforeObservation = sODA.getLastRow();
      var observID = getObservationID(studentICID, studentOSISICID);
      sODA.appendRow([observID, dateTime, submittedByEmail, studentICID, studentOSISICID, studentName, studentClass, dateObserv, catObsev, textObserv, textCommit]);
    } else {
      throw "Error: Getting Active User Email";
      return 0;
    }
    SpreadsheetApp.flush();
    var lastobservIDOnSheet = sODA.getRange(sODA.getLastRow(), 1).getValue();
    if (lastobservIDOnSheet == observID) {
      success = observID;
    }
    throw "Success"
    return success;
  } catch (e) {
    throw e;
    var eventcode = "saveObsFailed";
    var event = "failure";
    log_event_text(eventcode, event, e)
  } finally { //release lock
    lock.releaseLock();
  }
}