Google App Script. Check if Range1 intersects Rang

问题:

Good day everyone!

I'd like to have a function similiar to VBA's intersect in Google Sheets Script. Is there a nifty way to do this? The function must return:

• RangeIntersect(R1, R2) = true if R1 has common cells with R2,
• RangeIntersect(R1, R2) = false if R1 has no common cell with R2

Thank you in advance.

Speed test

I want the function to work as fast as possible. That's because it would be used in loops inside onEdit function. If you like, test provided function with this script:

function speedtest () {
  var sheet;      
  sheet = SpreadsheetApp.getActiveSheet();      
  var rr1 = ['A1:C16', 'B2:B88', 'D1:D8', 'E1:E17', 'A18:B51', 'A13:A14', 'A17:C17'];

  var r1, r2;

  r1 = sheet.getRange(rr1[0]);      
  var rr2 = [];

  // define some ranges
  for (var x = 0; x < 30; x++) {
  for (var i = 0; i < rr1.length; i++) {
    r2 = sheet.getRange(rr1[i]);
    rr2.push(r2);
  }
  }

  var C;

  var start, end, time;
  // timer start     

    for (var t = 0; t < 10; t++) {
        start = new Date().getTime();

        for (var f = 0; f < rr2.length; f++) {
             C = RangeIntersect(r1, rr2[f]);               
        }

        end = new Date().getTime();
        time = end - start;
        Logger.log('Execution time = ' + time);    

    }            
}

回答1:

First, you will need to get your range coordinates:

function Coordinates (range) {
  var self = this
  ;
  self.x1 = range.getColumn();
  self.y1 = range.getRow();
  self.x2 = range.getLastColumn();
  self.y2 = range.getLastRow();
}

Second, you will need to know if the column bounds and row bounds overlap:

function Overlaps (a, b, c, d) {
  return (a >= c && a <= d) || (b >= c && b <= d) || (c >= a && c <= b) || (d >= a && d <= b);
}

Your requested function to compare two ranges:

function RangeIntersect (R1, R2) {

  R1 = new Coordinates (R1);
  R2 = new Coordinates (R2);

  return (Overlaps(R1.x1, R1.x2, R2.x1, R2.x2) && Overlaps(R1.y1, R1.y2, R2.y1, R2.y2));
}

And here's a quick test:

function test () {
  var sheet, r1, r2, r3
  ;
  sheet = SpreadsheetApp.getActiveSheet();
  r1 = sheet.getRange(1, 1, 2, 2);
  r2 = sheet.getRange(2, 2, 2, 2);
  r3 = sheet.getRange(4, 4);

  Logger.log("%s %s overlap %s", r1.getA1Notation(), (RangeIntersect(r1, r2) ? "does" : "does not"), r2.getA1Notation());
  Logger.log("%s %s overlap %s", r2.getA1Notation(), (RangeIntersect(r2, r3) ? "does" : "does not"), r3.getA1Notation());
  Logger.log("%s %s overlap %s", r1.getA1Notation(), (RangeIntersect(r1, r3) ? "does" : "does not"), r3.getA1Notation());

  return;
}

Please note that this is just a quick solution. Also note that getActiveRange will include any rows and columns hidden through hide or filtering and would otherwise be included in the selected range.

Added 2016-05-04

A shortened version of Max's quicker solution with a single return (I prefer a single exit point):

function RangeIntersect (R1, R2) {
  return (R1.getLastRow() >= R2.getRow()) && (R2.getLastRow() >= R1.getRow()) && (R1.getLastColumn() >= R2.getColumn()) && (R2.getLastColumn() >= R1.getColumn());
}

回答2:

I've made my own version of this script:

function RangeIntersect(R1, R2) {

  var LR1 = R1.getLastRow();
  var Ro2 = R2.getRow();
  if (LR1 < Ro2) return false;


  var LR2 = R2.getLastRow();
  var Ro1 = R1.getRow();
  if (LR2 < Ro1) return false;

  var LC1 = R1.getLastColumn();
  var C2 = R2.getColumn();
  if (LC1 < C2) return false;

  var LC2 = R2.getLastColumn();
  var C1 = R1.getColumn();
  if (LC2 < C1) return false;

  return true;

}

This script works ~1.4 times faster because it returns each time when 2 ranges don't intersect.

回答3:

If you want the range representing the intersection you can use the following code:

function getIntersection(range1, range2) {
  if (range1.getSheet().getSheetId() != range2.getSheet().getSheetId()) {
    return null;
  }
  var sheet = range1.getSheet();
  var startRow = Math.max(range1.getRow(), range2.getRow());
  var endRow = Math.min(range1.getLastRow(), range2.getLastRow());
  var startColumn = Math.max(range1.getColumn(), range2.getColumn());
  var endColumn = Math.min(range1.getLastColumn(), range2.getLastColumn());
  if (startRow > endRow || startColumn > endColumn) {
    return null;
  }
  return sheet.getRange(startRow, startColumn, endRow - startRow + 1, endColumn - startColumn + 1);
}