Google Script LockService on different Spreadsheets

1
votes

I am trying to make the LockService work across different Spreadsheets. I do not even know if that is possible as I it seems to be used for users within the same function, right?

I made some example sheets to demonstrate what I mean. Currently there is a small possibility that my users generate the same number. Is there any way to alleviate this?

User Sheet 1 // Here the user shall press the button to make the number go up in the Database. https://docs.google.com/spreadsheets/d/1P0_UQc02MRxHq_yFegjCc2UGmzRbkn2gxR8dw6yKvLY/edit#gid=0

User Sheet 2 // Here the user shall press the button to make the number go up in the Database. https://docs.google.com/spreadsheets/d/1Ua8mdZ-IBwcAt2AO9yuoa8PHiXlIGWfxPRx7rP9OXXA/edit#gid=0

Database Sheet: Here the number shall go up by one if users from sheet 1 or 2 press their button. https://docs.google.com/spreadsheets/d/146OaFXGInuzMduvQDshh5ZDlsKf--eOqJUfa3Hrt_To/edit#gid=0

function Lock() {  
   // Generates a unique ticket number for every form submission.
  var lock = LockService.getScriptLock();
   var ratenprogramm = SpreadsheetApp.openByUrl(
    'https://docs.google.com/spreadsheets/d/146OaFXGInuzMduvQDshh5ZDlsKf--eOqJUfa3Hrt_To/edit'); // Leads to Lockscript 3
   var main = ratenprogramm.getSheetByName("Main");
  
 // var raten = SpreadsheetApp.getActiveSpreadsheet();
  
   var targetCell = main.getRange("A2").getValue();

  // Get a script lock, because we're about to modify a shared resource.
  
  
   Utilities.sleep(8000);
  // Wait for up to 10 seconds for other processes to finish.
  lock.waitLock(20000);  
  
  main.getRange("A2").setValue(targetCell + 1);
  // Release the lock so that other processes can continue.
  lock.releaseLock(); 
}
1
google-apps-scriptlocking
LockService will lock the script it is called from so it can't be called by other instances of the script running at the same time. Put all your number generation and resource editing inside the lock and that will alleviate any chance of clashes. - I hope this is helpful to you
Hello Rafa, thank you for your answer. However: Is what you say also true for different scripts in different spreadsheets like in my example given? Thank you. - Kristian

1 Answers

1
votes

Answer:

The LockService is indeed script-bound, and a call to LockService.getScriptLock() in one script will not have an effect on an independent script.

More Information:

When making a script lock, the script from which the lock is called is the one which is under its effect. In the case where external resources are modified, it is the code which modifies the external resource within the script which is affected, and not any other independent, external resource or script.

In this case, if the button in Spreadsheet 1 and Spreadsheet 2 are pressed at the same time then it is possible that the number returned to each will be the same.

If the code to modify the shared resource in Spreadsheet 1 is placed inside a lock, the script will only lock Spreadsheet 1, and Spreadsheet 2's code is still free to make modifications.

If the time at which Spreadsheet 1's lock is released and Spreadsheet 2's button is pressed happen to coincide, then you are going to have the same problem.

What you can do:

Unfortunately there is very little that you are able to do here, aside from trying to marry the two scripts together in one file.

You could, for example, on button press, make a UrlFetchApp.fetch() request to an Apps Script web app which in turn modifies your master sheet, the Web App code being the one containing the lock. That way the lock would be contained within one script and therefore get over the hurdle of modifying the sheet from two points.