Google App Script. Check if Range1 intersects Range2

Question

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);    

    }            
}

Answer 1

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.

Update Tested with v8 engine. Speed of all suggested solutions are almost the same:

• Script. Range intersect speed test

Answer 2

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());
}

Answer 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);
}

Answer 4

Checking that the sheets are the same. I think this is as fast as you can make it:

function rangesIntersect(r1, r2) 
{
  if (r1.getSheet().getIndex() != r2.getSheet().getIndex()) return false;
  if (r1.getLastRow() < r2.getRow()) return false;
  if (r2.getLastRow() < r1.getRow()) return false;
  if (r1.getLastColumn() < r2.getColumn()) return false;
  if (r2.getLastColumn() <  r1.getColumn()) return false;
  return true;
}

Answer 5

I didn't check to see if my code is faster than the other options. I know that Google recommends to minimize the number of reads and writes ref, so I did this for myself for checking both the range and sheet:

function rgInter(r1,r2){
  let a1 = rgNum(r1),a2 = rgNum(r2);
  return ((a1[1] === a2[1]) && (a2[5] >= a1[3] && a2[3] <= a1[5]) && ( a2[4] >= a1[2] && a2[2] <= a1[4])); 
}
function rgNum(rg){ //0.text, 1.gid, 2.colI, 3.rowI, 4.colF, 5.rowF
  const re = /gid=(\d+)&range=([A-Z]+)(\d+):?([A-Z]*)(\d*)/g;  
  let trg = rg.getDataSourceUrl();
  let ma = Array.from(trg.matchAll(re))[0]; 
  ma[2] = lTcol(ma[2]);ma[3] = parseInt(ma[3]);
  if (ma[4].length === 0) {  ma[4] = ma[2]; ma[5] = ma[3];}
  else { ma[4] = lTcol(ma[4]); ma[5] = parseInt(ma[5]);}
  return ma;
}   
function lTcol(tcol)//number of the col from text {
  const a = "A".charCodeAt(0)-1;
  let n = 0, tl = tcol.length;
  for (let i =0; i < tl; i++)  {        
    n+= Math.pow(26,i);
    n+= (tcol.charCodeAt(i)-a -1) * Math.pow(26,tl-1-i);  }
  return n;
}