0

I have aRange which I do not know what it contains (not size nor values).

Now I want to check if 'B2:B4' is within aRange and get the value(s) of B2:B4 if indeed it is inside aRange.

I am really frustrated to be unable to figure out how to solve this (to me apparently) trivial requirement.

Thank you for any suggestions.

mortpiedra
  • 81
  • 8
  • 1
    Take a look at range.getRow(), range.getColumn(), range.getWidth() and range.getHeight(). One range contains another if it's start row and column are less the or equal to the contain range start row and column. And if the end row and colum or greater than or equal to the contained end row and column – Cooper Sep 06 '20 at 17:25

1 Answers1

0

B2:B4 matches exactly given range:

Try range.getA1Notation():

function myFunction(){

const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName('Sheet1');
const range = sh.getRange('B2:B4'); //given range
 
  if (range.getA1Notation()=='B2:B4'){
    var vals = range.getValues().flat(); //flat is optional but it returns 1D list
  }
Logger.log(vals)
}

B2:B4 is within given range:

As Cooper suggested in his comment, use getRow(), getColumn(), getWidth(), getHeight():

function myFunction(){

const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName('Sheet1');
const range = sh.getRange('B1:B5'); //given range 

  if(range.getRow()<=2 && range.getColumn()<=2 && range.getWidth()>=1 && range.getHeight()>=3){
    var vals = sh.getRange('B2:B4').getValues().flat(); //flat is optional but it returns 1D list
  }
  Logger.log(vals)
}

References:

Marios
  • 26,333
  • 8
  • 32
  • 52
  • Thanks Marios for clarifying and it is always the second case (i.e. within). One main doubt still remains though. I should have explained from the beginning, but the aRange ( in your example const range = sh.getRange('B1:B5'); ) has been passed as a parameter into the function and hence I dont have the sheet. Thus, the question is how to get the value (there is only one) in 'B2:B4' ? – mortpiedra Sep 06 '20 at 18:20
  • @mortpiedra I am not sure what you mean and stack overflow does not allow follow up questions. Please accept this one and consider up voting and post a new question regarding the new issue you are having. Thanks – Marios Sep 06 '20 at 18:22
  • @mortpiedra also your question does not make sense anyway. You ask me how you can get the range? How can I know that if you haven't provide any code whatsoever. – Marios Sep 06 '20 at 20:00