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!

Is your script in Form or Response Sheet? You can use the Form Response sheet to get the latest entry as it will be updated as soon as the form is submitted. — Akshin Jalilov, Aug 03 '15 at 05:30
@AkshinJalilov Thanks for your reply. No, it is not a Google Form. It is a new Google app script project that has many functions implemented, which treats the Google Spreadsheet as a database. Moreover, Google Form does not support returning a dynamic value to client-side after submitting the form. — user3545752, Aug 03 '15 at 05:38
Oh, When you said "Form" I assumed you meant google forms. One way you can solve your problem is by recording the latest ID as a document property using [Properties Service](https://developers.google.com/apps-script/guides/properties). This will be much faster than appending row and will be correctly pulled when another users runs it at the same time. — Akshin Jalilov, Aug 03 '15 at 05:43
@AkshinJalilov Thanks for the reply! However if I delete some rows inside the Spreadsheet, the value recorded won't be reset. I need to stick strictly to the **last row** inside the spreadsheet. Is there some way to achieve this? — user3545752, Aug 03 '15 at 06:07

Zig Mandel · Accepted Answer · 2015-08-04T19:16:04.520

7

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()

edited Aug 04 '15 at 19:16

answered Aug 03 '15 at 13:23

Zig Mandel

19,571
5
26
36

Thanks very much! That's EXACTLY what I needed. The `flush()` makes sure the `setValue()` is running before releasing the lock. – user3545752 Aug 04 '15 at 07:25

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

1 Answers1

Linked