How to get range that has been moved?

I believe your goal is as follows.

• You want to know whether the values of a range of "A2:B2" is moved.

In this case, how about using the named range, OnChange trigger, and PropertiesService? I thought that when those are used, your goal might be able to be achieved. When the sample script is prepared, it becomes as follows.

Usage:

1. Create a named range.

As a sample, please create a named range to the cells "A2:B2" as "sampleNamedRange1". Ref

2. Prepare sample script.

Please copy and paste the following script to the script editor of Spreadsheet. And, please install OnChange trigger to the function installedOnChange.

function installedOnChange(e) {
  var originalRange = "A2:B2";
  var nameOfNamedRange = "sampleNamedRange1";

  if (!["INSERT_ROW", "REMOVE_ROW"].includes(e.changeType)) return;
  var p = PropertiesService.getScriptProperties();
  var pv = p.getProperty("range");
  if (!pv) pv = originalRange;
  var range = e.source.getRangeByName(nameOfNamedRange);
  var a1Notation = range.getA1Notation();
  if (a1Notation != pv) {
    p.setProperty("range", a1Notation);
    Browser.msgBox(`Range was changed from ${pv} to "${a1Notation}". Original range is "${originalRange}".`);
  }
  // var values = range.getValues(); // The values are not changed because of the named range.
}

3. Testing.

In this case, please do I added a row under A1:B1. By this, the script is automatically run by the OnChange trigger. And, you can see the dialog. You can see the demonstration as shown in the following image.

Note:

• This is a simple sample script. So, please modify this for your actual situation.

References:

• Installable Triggers
• getRangeByName(name)
• Properties Service

Added:

From the discussions, I understood that you wanted to retrieve the named range of the specific sheet using the name of the named range. In this case, the sample script is as follows.

When the sheet name is used

const range = SpreadsheetApp.getActive().getSheetByName("sheetName").getRange("nameOfNamedRange");

or

const range = SpreadsheetApp.getActive().getSheetByName("sheetName").getNamedRanges().find(n => n.getName() == "nameOfNamedRange").getRange();

When the active sheet is used

const range = SpreadsheetApp.getActiveSheet().getRange("nameOfNamedRange");

or

const range = SpreadsheetApp.getActiveSheet().getNamedRanges().find(n => n.getName() == "nameOfNamedRange").getRange();

This is possible through DeveloperMetadata. Metadata can be set to ranges or sheets and whenever such data(ranges or sheets) are moved, the associated metadata moves along with it as well. Unfortunately, this metadata cannot be set to arbitrary ranges, but only to single column or single row. For eg, with A2:B2, We have to set the metadata to the entirety of column A, column B and Row 2. However, once set, apps script is no more needed. Google sheets automatically keeps track of the movements of such data.

Sample script:

const setDevMetadata_ = (sheetName = 'Sheet1', rng = '2:2', key = 'a2b2') => {
  SpreadsheetApp.getActive()
    .getSheetByName(sheetName)
    .getRange(rng)
    .addDeveloperMetadata(key);
};

/**
 * @description Set metadata to a specific range
 * Unfortunately, this metadata cannot be set to arbitrary ranges, but only to single column or single row.
 * For eg, with `A2:B2`, We have to set the metadata to the entirety of column A, column B and Row 2.
 * @see https://stackoverflow.com/a/73376887
 */
const setDevMetadataToA2B2 = () => {
  ['2:2', 'A:A', 'B:B'].forEach((rng) => setDevMetadata_(undefined, rng));
};

/**
 * @description Get a range with specific developer metadata key
 */
const getRangeWithKey = (sheetName = 'Sheet1', key = 'a2b2') => {
  const sheet = SpreadsheetApp.getActive().getSheetByName(sheetName),
    devFinder = sheet.createDeveloperMetadataFinder(),
    [rows, columns] = ['Row', 'Column'].map((rc) =>
      devFinder
        .withKey(key)
        .withLocationType(
          SpreadsheetApp.DeveloperMetadataLocationType[rc.toUpperCase()]
        )
        .find()
        .map((devmetadata) =>
          devmetadata.getLocation()[`get${rc}`]()[`get${rc}`]()
        )
    );

  console.log({ rows, columns });
  const rng = sheet.getRange(
    rows[0],
    columns[0],
    rows[1] ? rows[1] - rows[0] + 1 : 1,
    columns[1] ? columns[1] - columns[0] + 1 : 1
  );
  console.log(rng.getA1Notation());
  return rng;
};