Selecting a range from within a named range google sheets script

1
votes

Is there any way to select a range specified from within another named range?

For example if I have a named range say "firstRange" and I want to get just the 2nd column from within that range to copy to another named range.

I can do this by copying cells individually with a for loop but it takes a long time.

Unfortunately getRangeByName("firstRange").getRange(1,1,2) is not valid as getRange is not a method of getRangeByName

Thanks

2
javascriptgoogle-apps-scriptgoogle-sheets

2 Answers

1
votes

You can get the named range and then get the values which returns a multi-dimensional array. Then you can parse the values that you are looking for out of that. For example

var parsed = SpreadsheetApp.getActiveSpreadsheet()
    .getRangeByName('namedRange')
    .getValues()
    .map(function(row) {
      return [row[1]];
    });

This gets all the values from your named range and then maps over them and grabs the values from the second column (the 1 index in this example)

I hope that helps.

1
votes

How about this method? I think that there are several answers for your situation. So please think of this as one of them. The flow is as follows.

Flow:

  • Range of values you want is retrieved from the source named-range by offset().
    • When getLastRow() is used to the named range, the returned value is the last row of the named range.
  • Retrieve the destination named-range.
  • Copy the retrieved source range to the destination range.

Sample script:

In this sample script, the 2nd column of named range of firstRange is copied to the named range of secondRange.

var ss = SpreadsheetApp.getActiveSpreadsheet();

// Retrieve source range.
var sourceRange = ss.getRangeByName("firstRange");
var src = sourceRange.offset(0, 1, sourceRange.getLastRow(), 1);

// Retrieve destination range.
var destinationRange = ss.getRangeByName("secondRange");

// Copy from source range to destination range.
src.copyTo(destinationRange);

For example, if you want to copy the retrieved source range to 2nd column of the destination range, please modify var destinationRange = ss.getRangeByName("secondRange") as follows.

var destinationRange = ss.getRangeByName("secondRange").offset(0, 1, 1, 1);

References:

If this was not what you want, I'm sorry.