google sheets – How best to handle a range moving between getValue and setValue?

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
  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

 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.


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