4

I have two Range variables. How can I make sure that they belong to the same Sheet and reference the same set of cells?

For example:

var mySheet = // ...
function onEdit(e) {
  var range1 = e.range;
  var range2 = mySheet.getRange(123, 234);
  if (areEqual(range1, range2)) {
    // cell (123, 234) was edited,
    // let's do stuff
  }
}

What should I write instead of areEqual?

  • Should I change anything to make this more googleable? – Kinrany Jan 10 '17 at 14:24
  • It is a very specific and to-the-point question (which is good!). I can't see anything that would make it more visible. BTW, welcome to Web Applications! – Vidar S. Ramdal Jan 10 '17 at 14:27
5

Instead of checking row, column, width and height, you can just compare the A1 notations (like A1:B2), in combination with something that identifies the sheet:

function areEqual(r1, r2) {
  return r1.getGridId() === r2.getGridId()
    && r1.getA1Notation() === r2.getA1Notation()
}

range.getGridId() uniquely identifies a sheet the range belongs to (within a spreadsheet).

See the documentation for the Range object, and the getA1Notation() and getGridId() in particular.


You can shorten it even further by just comparing range.getDataSourceUrl():

function areEqual(r1, r2) {
  return r1.getDataSourceUrl() === r2.getDataSourceUrl()
}
1

I'm going to use this until I find a better solution:

function areEqual(r1, r2) {
  return r1.getSheet().getName() == r2.getSheet().getName() 
         && r1.getRow() == r2.getRow() 
         && r1.getColumn() == r2.getColumn()
         && r1.getWidth() == r2.getWidth()
         && r1.getHeight() == r2.getHeight();
}

(r1.getSheet() == r2.getSheet() doesn't work, same as range1 == range2 in the original post.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.