Max Makhrov Max Makhrov - 3 months ago 17
Javascript Question

Google App Script. Check if Range1 intersects Range2

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

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.

Comments