CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
user1650538
user1650538

Reputation: 228

Is it possible to reference discrete ranges in google scripts?

I have a google script function that gets me a range from a google spreadsheet.

function getGrainWeights() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  return range = ss.getRangeByName("Brew_Grains");
}

This range is then processed by another function which modifies and upadtes the values in the range.

"Brew_Grains" is a named range in my spreadsheet but I would like to replace it with a discrete range such as "B2,C3,D10" etc. Is this possible, or is there some workaround? Cheers

Upvotes: 2

Views: 1247

Answers (2)

Phrogz
Phrogz

Reputation: 303460

You cannot (as far as I can tell) join two Range objects. However, if you have a custom function that operates on multidimensional arrays, you can convert the ranges into arrays and concatenate those.

For example:

function SUM_RANGE_COLUMN(myRange,colIndex){
  var val,sum=0;
  for (var i=0;i<myRange.length;i++) if (val=myRange[i][colIndex]) sum+=val;
  return sum;
}

function UNION(range1,range2){
  return range1.concat(range2);
}

With the above as custom functions, you can write this in your spreadsheet:

=SUM_RANGE_COLUMN( UNION(E4:F5,E9:F12), 1 )

To convert two real Ranges to arrays, use getValues():

function joinRanges( range1, range2 ){
  return range1.getValues().concat( range2.getValues() );
}

Upvotes: 0

wchiquito
wchiquito

Reputation: 16569

You have the following ways to get a range, in addition to the named range used getRangeByName(name).

Get a range

You can find the documentation.

UPDATE

A workaround to what you want to do can be something like:

  ...
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  var ranges = [];
  var range = ss.getRange('B2:C3');
  ranges.push(range);
  range = ss.getRange('D10');
  ranges.push(range);
  processingFunction(ranges);
  ...

You can then pass the array (ranges) to any other function for processing.

Upvotes: 3

Related Questions