Google App脚本。检查Range1是否与Range2相交 [英] Google App Script. Check if Range1 intersects Range2

问题描述

我想在Google表格中使用与VBA的 intersect 类似的功能脚本。有没有一个好办法做到这一点？该函数必须返回：

• RangeIntersect（R1，R2）= true 如果R1与R2有共同的单元格，如果R1没有与R2共用的单元格，


• RangeIntersect（R1，R2）= false
  

预先感谢您。

速度测试

我希望函数尽可能快地工作。这是因为它将在 onEdit 函数内的循环中使用。如果您喜欢，请使用此脚本测试提供的功能：

  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 = []; 
 
 //为（var x = 0; x <30; x ++）{
为（var i = 0; 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（）; （var f = 0; f  C = RangeIntersect（r1，rr2 [f]）; 
 
 
} 
 
 end = new Date（）。getTime（）; 
时间=结束 - 开始; 
 Logger.log（'Execution time ='+ time）; 
 
 
 $ b  

解决方案

 函数RangeIntersect（R1，R2）{
 
 var LR1 = R1.getLastRow（）; 
 var Ro2 = R2.getRow（）; 
 if（LR1  
 
 var LR2 = R2.getLastRow（）; 
 var Ro1 = R1.getRow（）; 
 if（LR2  
 var LC1 = R1.getLastColumn（）; 
 var C2 = R2.getColumn（）; 
 if（LC1  
 var LC2 = R2.getLastColumn（）; 
 var C1 = R1.getColumn（）; 
 if（LC2  
返回true; 
 
 
 
 
 $ p 
 $ b 
这个脚本的工作速度快1.4倍，因为它每次返回当2个范围不相交时。  
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);    

    }            
}

解决方案
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.  

                        
这篇关于Google App脚本。检查Range1是否与Range2相交的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！