CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsrange
user.j
user.j

Reputation: 55

Get a non-contiguous active range by selection and use it programmatically Google script

I want to get a non-contiguous range by selection and use it programmatically.

If I select say, A1 to F10 so I have a Contiguous range I can use below and it will give A1:F10

function getSelectedRange_Contiguous(){
  var selected = SpreadsheetApp.getActiveSheet().getActiveRange(); // Gets the selected range
  var rangeString = selected.getA1Notation(); // converts it to the A1 type notation
  Logger.log(rangeString)
  return rangeString;
}

If I use the macro recorder and select A1:A10 & C1:C10 & E1:E10 I get a function that will select a discontinuous range

function UntitledMacro() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRangeList(['A1:A10', 'C1:C10', 'E1:E10']).activate();
};

But how do you get a Non-Contiguous range from selection to be used programmatically

Non-Contiguous does not work, it gives E1:E10

function getSelectedRange_NonContiguous() {
  var spreadsheet = SpreadsheetApp.getActive();
  var selected = spreadsheet.getActiveRange().getRangeList([]);
  var rangeString = selected.getA1Notation();
  Logger.log(rangeString)
  return rangeString;
};

Upvotes: 2

Views: 927

Answers (1)

Marios
Marios

Reputation: 27390

Explanation:

You need to use getActiveRangeList() instead of getActiveRange to get the Non-Contiguous active range.

  • Also it is better if you apply this method to a particular sheet instead of the spreadsheet object so you can later on be more specific on the sheet object.

Solution:

function getSelectedRange_NonContiguous() {
  const sheet = SpreadsheetApp.getActive().getActiveSheet();
  const selected = sheet.getActiveRangeList().getRanges();
  // an array of selected ranges
  const notations = selected.map(rng=>rng.getA1Notation()); 
  // log them:
  notations.forEach(nt=>console.log(nt));
  return notations;
};

Keep in mind that if you are planning to use that function as a custom function (a custom formula in your sheet) you won't get live updates because of the custom formula restrictions.

Upvotes: 3

Related Questions