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.

From what I understand, you want to check if a cell is within a specified range? Have a look at this post. — CalamitousCode
@s1c0j1 Thank you, but this is not exactly what you need. With the onEdit trigger, everything is clear, but in my example there is no cell change event The user clicks on the cell and a SelectionChange Event occurs. This is from Excel, but the logic is the same. This is an event when the value of the cell has not changed yet, but the cell is already selected I need to catch it. — Boris Baublys
Right. Apologies.Yes, it's unfortunate, but it doesn't seem like Google Apps Script can track user selections. Also, the most often you can trigger a script is once a minute. You could get around this by making sure your function runs for at least a minute and triggering it every minute.. — CalamitousCode
@s1c0j1 In Excel, the user now spends about 1 second to select one item from the Directory and transfer it to Calculation. Thus, it takes no more than a few minutes to complete the Calculation. If this is done at a frequency of 1 time per minute, then it will take a couple of hours. In Google app script monitor spreadsheet selected ranges the sheet is queryed every 200 ms. That would be good, but this example does not work for me. And you tried it, does this code work for you? — Boris Baublys
The range can be checked several times in one function, so it won't be just once a minute. I have not tried the code because I don't believe it would work in the Google Apps Script editor. It looks unfamiliar and I think it's another way of calling a script from outside the editor. I can't be sure, though. — CalamitousCode

Peter Kalu Peter Kalu · Accepted Answer · 2020-04-29T07:27:45

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

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

3 Answers

Intersection of two Ranges