0

I have a simple experiment to illustrate the issue:

function testRangeAddressAfterSort() {
  var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  var testSheet = spreadSheet.getSheetByName("Tests");

  var r = testSheet.getRange('K7');
  var v = r.getValue(); // gives 'J'
  var a = r.getA1Notation(); // gives 'K7'
  // sort here
  //App.Sheets.waitForSpreadsheet();
  var v2 = r.getValue(); // gives 'J', even tho it's now 'N'
  var a2 = r.getA1Notation(); // gives 'K7'
  r.setValue('test'); // sets K7 to 'test'
}

I set up my sheet range K4:K10 with values

    K
 ----
 4| E
 5| A
 6| N
 7| J
 8| P
 9| S
10| R

I put a breakpoint on the var v2 line, then debug. I do a manual sort of the range. Then I debug step through the rest of the lines. I put the result of each line in a comment to show what happens.

The conclusion of the experiment seems to be that if a sort (or any kind of range moving around) happens between my initial getRange and my setValue, it sets a cell I didn't intend.

In reality I have a bunch of robots (scripts running off timers) managing a list, which means multiple threads, and I don't want them stepping on each other, or if I do a manual sort on the list, I don't want that to mess up my list because robots now save to the wrong rows.

As far as adding rows go, appendRow is great because it's atomic and thread safe nothing can mess it up.

But there doesn't seem to be an updateRow or updateRange that's also atomic, right?

I either need a range to track the cell as it moves or I need an atomic update operation.

I looked into the NamedLocks library, and it looks like it may achieve it, but I am wondering if there's a more simpler, basic or built-in way of doing it.

EDIT

I achived it with the NamedLocks library, and it looks like it is working.

  • Google Apps Script hasn't an official library directory so it's not clear what you mean by "the NamedLocks library". – Rubén Dec 9 '19 at 15:59
  • @Rubén, sorry. I added a link to what I'm referring to. – toddmo Dec 9 '19 at 16:04
0

When Google Apps Script reads the spreadsheet by using the Spreadsheet Service (SpreadsheetApp) it takes a snapshot of the spreadsheet. If you need that your script read the changes made my using the Google Sheets UI after the spreadsheet was read then you should make that your script reads again the spreadsheet.

You could use the Lock Service to block the execution of scripts that are in the same project but it will not block changes by scripts that are in other projects.

Bear in mind that the Lock Service has three methods, one for each of three different scopes: user, document & script. If you add the getLock method at the very beginning of the script and release it at the very end then the locked section embraces the whole code but the "effect" will depend on the lock scope.

| improve this answer | |
  • One issue, is that there are 3 places in the code where it's doing updates: one for add, one for update and one for sort. So if there's a lock on one place, it won't prevent another thread from running in the other code locations. – toddmo Dec 9 '19 at 16:06
  • @toddmo It's not clear what you mean by "in other code locations" – Rubén Dec 9 '19 at 16:28
  • lock locks a section of code. So I was referring to other sections of code, in other functions. I have an add function, an update function, a delete function and a sort function basically. If you get a lock on one section of code, it won't lock other sections of code. – toddmo Dec 10 '19 at 2:41
  • The Lock Service has three methods, one for each of three different scopes user, document & script. If you add the get lock at the very beginning of the script and release it at the very end then the locked section embraces the whole code but the "effect" will depend on the lock scope. – Rubén Dec 10 '19 at 3:01
  • I see. I had to use the NamedLocks library because I need to lock at the sheet level which is below the document level. Thank you!! – toddmo Dec 17 '19 at 22:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.