Why is the LockService is not working as intended?

Question

I'm having a problem with the same entry begin saved multiple times and I realized it was mostly caused by double clicking. I'm trying to use LockService to avoid it: if the lock is not aquired in a millisecond the script should be aborted(because it's a duplicated operation).

//more code above
var lock = LockService.getScriptLock();
try{
  lock.waitLock(1);//get the lock timing out in 1 millisecond 
  SpreadsheetApp.flush();
  ss.insertRowBefore(6);
  ss.getRange("A6").setValue(data[0][0]);
  ss.getRange("B6").setValue(formatedString);
  ss.getRange("C6").setValue(data[1][0]);
  ss.getRange("D6").setValue(data[2][0]);
  ss.getRange("E6").setValue(data[3][0]);
  ss.getRange("F6").setValue(data[ref][0]);
  SpreadsheetApp.flush();
  Utilities.sleep(10);//This is to make sure it takes at least 1 millisecond 
}
catch(e){
  return;//It should generate a exception and end the script if the lock is not aquired
}
//more code bellow

The problem is that I still getting duplicated entries(tougth only 2 most of the time, so I believe it's working in part). What I am doing wrong?

you never release lock, see https://stackoverflow.com/questions/43223774/how-to-understand-lockservice-and-implement-it-correctly — Kos, Nov 16 '21 at 17:30
Ty for the reply. I'm liberating the lock ahead in the code, but I tried it inside the try and got the same result. — emolano, Nov 16 '21 at 17:48
Would it be possible for you to provide a [minimum reproducible example](https://stackoverflow.com/help/minimal-reproducible-example)? — Ron M, Nov 16 '21 at 19:06
Done(the problem will appear if you click submit multiple times): https://docs.google.com/spreadsheets/d/1Af3Y9ZpE8V8BG_bjLRmMwrJ17Xo7APhbhK53dxuHQPY/edit#gid=0 — emolano, Nov 17 '21 at 12:59

score 0 · Answer 1 · answered Nov 16 '21 at 17:13

0

Try this approach:

let lock = LockService.getScriptLock();
lock.tryLock(10000);
if (lock.hasLock()) {
  ss.insertRowBefore(6);
  ss.getRange(6, 1, 1, 6).setValues([data[0][0], formatedString, data[1][0], data[2][0]], data[3][0], data[ref][0])
  SpreadsheetApp.flush();
  lock.releaseLock();
}

answered Nov 16 '21 at 17:13

Cooper

59,616
6
23
54

Ty for the reply, but it don't block the following scripts. But I may be able to use it to verify if it's duplicated so tnxs. – emolano Nov 16 '21 at 17:45
I've never tried to implement in in the middle of a function I always try to implement in on a complete function and I design the system so that all similar operations have to go through that same function. It might work that way I just have no experience to confirm that it will. – Cooper Nov 16 '21 at 18:46
It was really the problem, I should've lock the reading part too. Ty for the help. – emolano Nov 17 '21 at 16:26

score 0 · Answer 2 · answered Nov 17 '21 at 13:13

I managed to solve my problem by adding a "flag" cell to count the numbers of active submissions. I'm using the lock only around it.

var rep = ss.getRange("C1");//the flag starting with 0
var lock = LockService.getScriptLock();
try{
  lock.waitLock(1000);
  var vAtual = rep.getValue();
  if(vAtual >= 1)
    return;//return hopefully if it already have one active submission
  rep.setValue(vAtual+1);//increment for each active submit
  SpreadsheetApp.flush();
  lock.releaseLock();
}
catch(e){
  return;
}

It's not pretty but it worked, so far. But I would still like to know why my original lock strategy failed. It can help some other people with similar problems too.

score 0 · Accepted Answer · answered Nov 17 '21 at 15:42

Based on your sample sheet, you already read the data in the cells before you lock your succeeding code and clear its content.

Your original code:

  var data = ss.getRange("B1:B2").getValues();
  if(data[0][0] == "" || data[1][0] == "")
    return;
  var lock = LockService.getScriptLock();
  try{
    lock.waitLock(1);//get the lock timing out in 1 millisecond 
    SpreadsheetApp.flush();
    ss.insertRowBefore(7);
    ss.getRange("A7").setValue(data[0][0]);
    ss.getRange("B7").setValue(data[1][0]);
    SpreadsheetApp.flush();
    Utilities.sleep(10);//This is to make sure it takes at least 1 millisecond 
    lock.releaseLock();
    ss.getRange("B1:B2").setValue("");
  }
  catch(e){
    return;//It should generate a exception and end the script if the lock is not aquired
  }

What it does?

When you click submit button multiple times to execute your code, it will have n-times execution instance. As long as the clearing of cells don't take effect, each execution can write the data read from B1:B2.

Example:

Execution 1 started at 01:00:00.001 - already read the values in `B1:B2`
Execution 2 started at 01:00:00.005 - already read the values in `B1:B2`
Execution 3 started at 01:00:00.010 - already read the values in `B1:B2`

Execution 1 cleared B1:B2 content at 01:00:00.012. Hence you will have 3 copies of the submitted data. The writing of data in a new row was pended using the lock service, but the reading of data to add was not locked.

Solution

function submit() {
  Utilities.sleep(1000);//simulate the upper part of the code
  var ss = SpreadsheetApp.getActive().getSheetByName("spr1");
  
  var lock = LockService.getScriptLock();
  try{
    lock.waitLock(1);//get the lock timing out in 1 millisecond 
    Logger.log("Locked: "+Utilities.formatDate(new Date(),Session.getScriptTimeZone(),"yyyy-MM-dd'T'HH:mm:ss.SSS"));
    var data = ss.getRange("B1:B2").getValues();
    Logger.log(data);
    if(data[0][0] == "" || data[1][0] == "")
      return;
    SpreadsheetApp.flush();
    ss.insertRowBefore(7);
    ss.getRange("A7").setValue(data[0][0]);
    ss.getRange("B7").setValue(data[1][0]);
    Utilities.sleep(10);//This is to make sure it takes at least 1 millisecond 
    ss.getRange("B1:B2").setValue("");
    SpreadsheetApp.flush();
    lock.releaseLock();
    Logger.log("UnLocked: "+Utilities.formatDate(new Date(),Session.getScriptTimeZone(),"yyyy-MM-dd'T'HH:mm:ss.SSS"));
  }
  catch(e){
    return;//It should generate a exception and end the script if the lock is not acquired
  }
  Utilities.sleep(1000);//simulate the lower part of the code
}

Changes Done:

Lock the script first before reading the data in B1:B2
Make sure to clear the content of B1:B2 once it was added in a new row using flush() before releasing the lock.