Google Apps Script: append row to spreadsheet and get last row

Question

I have a new Google App Script project, which includes a form (not Google Form) which allows user to enter data. The data will be submitted and inserted to a Google Spreadsheet.

I want to give each user an unique ID which is last row + 1. I tried some code like the following:

function submitData(data){
  var user_id = sheet.getLastRow();
  sheet.appendRow([user_id, data.name, data.email]);
  return user_id;
}

The problem is that when two user submit the form at the sametime, they will get the same user_id. I believe the possible reason is that they both call getLastRow() before one of them calls appendRow().

I tried to use LockService however the new row overwrites the old row:

var COLUMN_USER_ID = 1;
var COLUMN_NAME = 2;
var COLUMN_EMAIL = 3;

function submitData(data){
  var lock = LockService.getPublicLock();
  if (lock.tryLock(10000))  {

    var newRow = sheet.getLastRow() + 1;
    var user_id = newRow - 1;

    sheet.getRange(newRow, COLUMN_USER_ID).setValue(user_id);
    sheet.getRange(newRow, COLUMN_NAME).setValue(data.name);
    sheet.getRange(newRow, COLUMN_EMAIL).setValue(data.email);

    lock.releaseLock();
    return user_id;

  } else {
    return "Lock Timeout";
  }

}

It seems that even after setValue() is called, getLastRow() still returns the old value before the value is inserted. I am guessing it is because this is an asynchronize call?

Thank you very much!

Answer 1

your actual issue is that you are not calling SpreadsheetAp.flush() before releasing the lock. thats why your lock isnt working.

this is a actually mentioned in the docs for lock.releaseLock() but in my opinion it should be automatically done by the lock (it is not currently). https://developers.google.com/apps-script/reference/lock/lock#releaseLock()