How to get actual position of range, when new row(s) above was added

Question

consider this situation.

  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("2:2");
  var values = range.getValues();
  values[0][0] = "updated value"; //updating retrieved data
  sheet.insertRowAfter(1); //here the problem appears
  range.setValues(values); //trying to save updated row data - FAIL!

Updated row values will be written in a row number 2 (as per range selector "2:2") not in new position "3:3". Result: It will overwrite data at row 2 instead of updating previously selected/loaded row, which is now at position "3:3" and not anymore at "2:2".

Im looking for a way how to keep "address" of a range even when the range was shifted because of added/removed rows/cells somewhere else in the sheet.

In this dummy example I can track range changes. But in parallel processing/updating sheet i can't simply track all changes from many different places.

So far I came up with a solution add new rows only at the end of SS (that doesn't change range of any rows above, but I would like to have ability add new rows on top of sheet without influencing already selected ranges. Deleting rows is also dangerous situation - it changes position of ranges also.

LockService can't really solve the situation, because I have plenty scripts working on the same sheet (not using centralized library because of speed performance in addon).

Metadata for cell or row seems too complicated to handle for such an easy task.

From my point of view Object Range should keep its position even when its moved/shifted somewhere else. Otherwise I can't see reason to have Range as an Object - if it keeps only fixed information about from where it was picked. Any advices are welcomed. Thank you in advance..

EDIT:

Just to add a context. I'm using Google sheets as a database for orders (10 thousands so far) - each row means one order (customer) and not all orders are in one sheet - different products have different sheets (+- 10 products/sheets)

There is an suggestion using named ranges to solve this issue - So what will happen if a spreadsheet will have ten of thousands named ranges - can that work without serious performance issues? Im thinking about to creating named range for each order row, so I can easily pick up right row by orderId and not to be afraid of moving a row when new order arrive during processing another one

Answer 1

• You want to achieve the following flow.
  1. Retrieve the value from "2:2" (row 2).
  2. Modify the retrieved value.
  3. Insert a row just below of the row 1.
     • By this, the initial row 2 is moved to the row 3.
  4. Put the retrieved value to the row 3 which is the initial row 2.
• You want to achieve this using Google Apps Script.

If my understanding is correct, how about this answer? Please think of this as just one of several answers.

Issue and solution:

• In your script, range of var range = sheet.getRange("2:2"); is the constant. By this, even when the row is inserted, this value is not changed. This is the reason of your issue.

• I think that the idea using the named range will be resolved for your situation. But your comment says as follows.

  • I just found, that namedRange is not capable actualize its own address, if something had changed in the sheet after initialize of a range.

• When the row 2 is installed as the named range, when a row is inserted to just below of the row 1, the range of the named range is also changed. But from your comment, I thought that the named range might have not been reloaded.

In order to confirm this, I would like to propose a sample script.

Sample script:

Before you run the script, please install a named range to row 2 as the name of sampleNamedRange. Then, run this script.

var ss = SpreadsheetApp.getActiveSpreadsheet(); // Added
var sheet = ss.getActiveSheet(); // Modified

// Retrieve the range from the namedRange.
// namedRange of "sampleNamedRange" is installed for the range of "2:2" of the active sheet.
var range = ss.getRangeByName("sampleNamedRange"); // Modified
var values = range.getValues();
values[0][0] = "updated value"; //updating retrieved data
sheet.insertRowAfter(1);

// Retrieve the range from the updated namedRange.
var range = ss.getRangeByName("sampleNamedRange"); // Added
range.setValues(values);

Result:

Note:

• The point is to retrieve the range from the updated named range. By this, the updated range can be used. So in above script, the updated value is put to the row 3.

References:

• getRangeByName()
• Class NamedRange

If I misunderstood your question and this was not the result you want, I apologize