GetRange and dynamic rows-CodePudding

Let's say I am getting the range A2:B2:

sheet.getRange("A2:B2").getValues();

But I added a row under A1:B1, so now my values are in A3:B3

Is it possible for Apps Script to dynamically catch that my values are now on another range ?

If not, any alternative ideas on how I can dynamically get the range of moving rows ?

Thanks,

CodePudding user response：

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

Note:

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

References:

CodePudding user response：

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