How to get a1notation of multiple rows in Google Sheets

Question

I've got a master sheet where a user selects rows to "reserve". My code so far is:

var selectedRow = ss.getActiveRange().getRow();
var mySelection = master.getSelection();
var myRange = mySelection.getActiveRangeList().getRanges();

for ( var i = 0; i < myRange.length; i++) {
    var reserveData = myRange[i].getValues()
} 

var numRows = myRange.length

if (numRows = 1) //one row selected { 
      a1notation = "A" + selectedRow + ":" + "U" + selectedRow;
      headers = master.getRange(1, 1, 1, 21);
      newRange = master.getRange(a1notation);
      //copy headers
      headers.copyTo(resSheet.getRange(1, 1));
} else if (numRows >= 2) //multiple rows selected {
      a1notation = "A" + selectedRow + ":" + "U" + ( ??? );  
      }

I'm unsure how to get the other rows that are selected. These could all be adjacent rows or non-adjacent rows (if that matters) Any help would be greatly appreciated.

added

a1notation = "A" + selectedRow + ":" + "U" + selectedRow;
newRange = master.getRange(a1notation);
newRange.copyTo(resSheet.getRange(resSheet.getLastRow() + 1, 1));

@TheMaster, it's not, I just had it higher up in my code. Was trying to just show what was relevant and I missed that part :) I added it. Thanks for catching. — Michael
– Michael, Commented Mar 30, 2020 at 15:39

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

Use map to get all a1 notations:

const a1nots = mySelection.getActiveRangeList().getRanges().map(range=>range.getA1Notation());
console.log(a1nots);

In addition, myRange.length would give the number of ranges in this selection and not the number of rows in each range.

References:

Class Selection

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Mar 30, 2020 at 16:03

TheMaster

51.6k7 gold badges76 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Michael Over a year ago

I will give this a try, thank you. It actually does return the number of rows selected, so I'm going with it.

TheMaster Over a year ago

@Mich only if it's non adjacent. It it's adjacent, it won't

Michael Over a year ago

I just tried it with a single row, and multiple rows (both adjacent and non adjacent) and it works... Ok so the above answer does return what I need! Thank you. However, before, I was using a1notation.copyTo( copies the row data to new sheet ) I cannot use .copyTo() with the above. Would you mind telling me how to accomplish that?

Michael Over a year ago

Just added the part I'm referring to as reference.

TheMaster Over a year ago

@Mich a1notation is a string. copyTo is a method of range class. you don't need a1notation. range=>range.copyTo() should work. If you need more help, consider asking a new question

|

Collectives™ on Stack Overflow

How to get a1notation of multiple rows in Google Sheets

1 Answer 1

References:

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

References:

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related