CODEWITHSUNDEEP
concurrencygoogle-apps-scriptgoogle-sheets-api
Chemik
Chemik

Reputation: 1479

Locking script when accessing spreadsheet doesnot work

My problem is how to ensure that no data will be lost while concurrent access.

I have script published as web-app. I want to add new row to DATA_SHEET. The function that handles submit button looks like this:

function onButtonSubmit(e) {
  var app = UiApp.getActiveApplication();
  var lock = LockService.getPublicLock();
  while (! lock.tryLock(1000))
    ;
  var ssheet = SpreadsheetApp.openById(SHEET_ID);
  var sheet = ssheet.getSheetByName(DATA_SHEET);
  var lastRow = sheet.getLastRow();
  var lastCol = sheet.getLastColumn();
  var rangeToInsert = sheet.getRange(lastRow+1, 1, 1, lastCol);
  var statText = rangeToInsert.getA1Notation();
  rangeToInsert.setValues(<some data from webapp form>);
  app.getElementById('statusLabel').setText(statText);
  lock.releaseLock();
  return app;
}

But it seems that this does not work. When I open two forms and click submit button within one second, it shows same range in statsLabel and writes data into same range. So I lose data from one form.

What is wrong with this code? It seems like tryLock() does not block script.

Is there any other way how to prevent concurrent write access to sheet?

Upvotes: 2

Views: 1315

Answers (3)

AdamL
AdamL

Reputation: 24599

It might be worth taking a look at appendRow(), rather than using getLastRow()/setValues() etc.

Allows for atomic appending of a row to a spreadsheet; can be used safely even when multiple instances of the script are running at the same time. Previously, one would have to call getLastRow(), then write to that row. But if two invocations of the script were running at the same time, they might both read the same value for getLastRow(), and then overwrite each other's values.

Upvotes: 6

idobatter
idobatter

Reputation: 99

You must insert this code when using getLastRow()/setValues() with lock.

SpreadsheetApp.flush();
// before
lock.releaseLock();

Upvotes: 0

Robert Harvey
Robert Harvey

Reputation: 180787

while (! lock.tryLock(1000))
    ;

seems a bit hinky. Try this instead:

  if (lock.tryLock(30000))  {
    // I got the lock!  Wo000t!!!11 Do whatever I was going to do!
  } else {
    // I couldn’t get the lock, now for plan B :(
    GmailApp.sendEmail(“[email protected]”, “epic fail”,
        “lock acquisition fail!”);
  }

http://googleappsdeveloper.blogspot.com/2011/10/concurrency-and-google-apps-script.html

Upvotes: 0

Related Questions