Why is the LockService is not working as intended?-CodePudding

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?

CodePudding user response：

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.

Output:

CodePudding user response：

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

CodePudding user response：

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.