Is there a google-script method like Application.Intersect(Target, Range) in Excel?

Question

Now I understand that the question is deeper, and is connected with the tracking of events.

In Excel, I use this code:

If Not Intersect(Target, Sh.Range("$A$1:$A$300")) Is Nothing sub_do_something()

Here, Target - the address of the selected cell, Intersect determines whether the cell belongs to the specified range.

I use it in the system for filling and calculating the costing of the project.

The user clicks a row in a specific section of the calculation template. The script determines the address of the selected cell and switches the user to a specific sheet of the directory. Next, the user clicks on the desired line of the directory, the script copies a certain range of cells in the line and returns the user back to the calculation. When this happens, the copied data is inserted into a range of cells, starting with the selected one.

Thus, the creating a calculation, in which there can be more than 100 positions, is greatly simplified.

In Excel, everything works fine, but soon I plan to transfer this project to a cloud-based service, and Google Sheets is the best option.

Alas, only some events can be tracked in GAS, for example, using onOpen or onEdit triggers. Excel has much more tracked events.

After a search on the StackOverflow, I found several similar issues related to tracing events, for example, How to find where user's cursor is in Document-bound script, Can we implement some code that fires upon selecting something in google document?, Google app script monitor spreadsheet selected ranges.

From the answers to these questions, it is clear that in GAS there is no such simple solution as Intersect(Target, Range) in Excel. The last example uses the side menu, running a script from it that queries the sheet 5 times per second, and displays the address of the active cell in the "data" field.

Unfortunately, this code does not work for me. In the debugger, the getActiveRange() function works fine, but this code does not work:

$(document).ready(() => {
   setInterval(()=>{
   google.script.run.withSuccessHandler(log).getActiveRange();
   },200)    
  })       
  log(e) => {
    $('#data').val(e)
  }

Question.

If someone did something similar, please share your experience. Or tell me why this example does not work. If he can be reanimated, I will adapt him to solve my task.

Answer 1

I worked on a similar project and here's the solution:

function onSelectionChange(e)
{
  var ss = e.source;
  var Sh = ss.getActiveSheet();
  var range = Sh.getRange("A1:A300");
  var target = e.source.getActiveRange();

  //check for intersection
  if(RangeIntersects(target, range))
  {
    Logger.log("Changed Row: " + target.getRow() + "\nValue: " + target.getValue());
  }
}

//returns true if target intersects with the predefined range
function RangeIntersects(target, range)
{
  return (target.getLastRow() >= range.getRow()) && (range.getLastRow() >= target.getRow()) && (target.getLastColumn() >= range.getColumn()) && (range.getLastColumn() >= target.getColumn());
}

Answer 2

Here's an idea. I can't quite get it to work though.

Maybe someone else can give a better answer.

Also, having functions running 24/7 is not possible with GAS, I think, as there are limits to the total run-time. You may wish to add a code-guard that exits the script if the last update time is longer than 10 minutes ago or something.

function checkSelection() {

    var spreadsheet = SpreadsheetApp.getActive();
    var targetRange = spreadsheet.getRange('activate');
    // Change your named ranged name here

    var tCol = targetRange.getColumn();
    var tLastCol = targetRange.getLastColumn();
    var tRow = targetRange.getRow();
    var tLastRow = targetRange.getLastRow();

    var num = 0;

    for (num; num < 115; ++num) {
    // Repeats the code below 100 times

        var range = spreadsheet.getActiveRange();
        var row = range.getRow();
        var col = range.getColumn();

        if (col >= tCol && col <= tLastCol && row >= tRow && row <= tLastRow) {
            range.setBackground('#000000');
            // Change the code in this block to your code.
        }

        SpreadsheetApp.flush();
        Utilities.sleep(500);
        // Waits half a second before repeating
    }
}

115 repetitions * 500ms wait seems to run for almost a minute, then the trigger will fire the whole function again.

Answer 3

Intersection of two Ranges

You can use this to calculate intersection of two ranges. It requires an object in the form of: {rg1:'A1Notation String',rg2:'A1Notation String'}

function calculateIntersection1(rgObj) {
  var iObj={};
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var rg1=sh.getRange(rgObj.rg1);
  var rg2=sh.getRange(rgObj.rg2);
  var iObj={rg1colst:rg1.getColumn(),rg1colen:rg1.getColumn()+rg1.getWidth()-1,rg1rowst:rg1.getRow(),rg1rowen:rg1.getRow()+rg1.getHeight()-1,rg2colst:rg2.getColumn(),rg2colen:rg2.getColumn()+rg2.getWidth()-1,rg2rowst:rg2.getRow(),rg2rowen:rg2.getRow()+rg2.getHeight()-1};
  if(iObj.rg1colst>iObj.rg2colen || iObj.rg1colen<iObj.rg2colst || iObj.rg1rowst>iObj.rg2rowen || iObj.rg1rowen<iObj.rg2rowst || iObj.rg2colst>iObj.rg1colen || iObj.rg2colen<iObj.rg1colst || iObj.rg2rowst>iObj.rg1rowen || iObj.rg2rowen<iObj.rg1rowst) {
    return '<h1>No intersecting cells</h1>';
  }else{
    var vA1=rg1.getValues();
    var v1=[];
    var vA2=rg2.getValues();
    var v2=[];
    for(var i=0;i<vA1.length;i++){
      for(var j=0;j<vA1[i].length;j++){
        var s=Utilities.formatString('(%s,%s)', iObj.rg1rowst+i,iObj.rg1colst+j);
        v1.push(s);
      }
    }
    for(var i=0;i<vA2.length;i++){
      for(var j=0;j<vA2[i].length;j++){
        var s=Utilities.formatString('(%s,%s)', iObj.rg2rowst+i,iObj.rg2colst+j);
        v2.push(s);
      }
    }
    var oA=[];
    for(var i=0;i<v1.length;i++){
      var idx=v2.indexOf(v1[i]);
      if(idx>-1){
        oA.push(v2[idx]);
      }
    }
    return Utilities.formatString('Intersecting Cells: %s', oA.join(', '));
  }
}

It either returns the string "No Intersecting Cells" or a string identifying the intersecting cells in (row, column) format.