CODEWITHSUNDEEP
google-apps-scriptweb-applications
MrPea
MrPea

Reputation: 655

Am I using the Google Apps Script Lock Service correctly?

I have 3 Apps Script functions in a script. The script is deployed as a public access Web App.

Each function reads from a it's own google sheet. Each function does not write anything to the sheets.

I have applied the Apps Script 'LockService' to each function to prevent multiple users accessing the spreadsheets, via the webapp, at the same time. In effect making each user wait until the previous user's script has finished reading. They they get to read the 3 sheets.

I've found this can sometimes create a bit of a delay of 4 or 5 seconds for users before the App is updated - which is quite off putting.

My question then is should I - a) bother with the LockService, as I am just reading the spreadsheet data and perhaps the main purpose of LockService is to prevent conflicts between concurrent read AND writes

Or

b) am I correct to implement it, as reading data concurrently could also cause conflicts is getting things like Range values etc.

thanks

EDIT: if option 'a' then I wonder what the purpose of LockService is? Is one reason perhaps (as I have elsewhere in other scripts) when you use multiple 'google.script.run.' One after the other to access the same spreadsheet, via different functions, then - as these run asynchronously - there could be conflicts created if both requests are trying to Get data.

just musing really....

Upvotes: 3

Views: 2706

Answers (1)

Henrique G. Abreu
Henrique G. Abreu

Reputation: 17752

You do not need to use the LockService if all you're doing is reading data. Therefore, option (a).

There's no need to protect Apps Script or Google Sheets from concurrency, even write operations from the same user. Each parallel access you do from Apps Script runs completely independently from each other, it is a new instance of your app that do not share anything with others, it might even be running on a different server entirely. And Google Sheets handle concurrency just fine, concurrent collaboration is the whole point of the G Suite apps.

So, when do you need LockService?

When your app logic requires such. For example, to protect itself from messing up its own data. Let's say you want to count how many clicks you had and save that number on a sheet, e.g. Sheet1!B2. So your code would look like:

function countClicks() {
  var r = SpreadsheetApp.getActive().getSheetByName('Sheet1').getRange('B2');
  var clicks = r.getValue();
  clicks = clicks + 1;
  r.setValue(clicks);
}

If this function runs concurrently, you're not going to corrupt your memory or make Google Sheets break, all that's going to happen is that you might overwrite yourself, as if you had multiple users trying to type in the same cell: the last one wins.

But your logic would be broken, that is, multiple concurrent clicks may be counted as a single one. That's when LockService comes in, to protect your logic:

function countClicks() {
  var r = SpreadsheetApp.getActive().getSheetByName('Sheet1').getRange('B2');
  var lock = LockService.getDocumentLock();
  lock.waitLock(10000); //throws exception if fails to acquire lock
  //you could try-catch it, but that's irrelevant in this example
  r.setValue(r.getValue()+1);
  SpreadsheetApp.flush(); //forces the script to actually send the values to Sheets
  lock.releaseLock();
}

Hopefully this clarifies it.

By the way, this is just a very lame example. This is not how you should count clicks because, as you've pointed out, locking adds a significant delay in your system. Therefore you should look for alternatives, maybe splitting your data per user or using atomic operations like appendRow.

Upvotes: 3

Related Questions