How to pass a range into a custom function in Google Spreadsheets?

Question

I am trying to compare two google sheets (for duplicate values) over a predefined range..

I am aware that google lets you compare sheets via tools, but i need a more customized comparison hence the function.

I want to create a function which takes in a range... something like:

    function myFunction(range) {
    var firstColumn = range.getColumn();
    // loop over the range
    }

The problem is that when I try doing this, the parameter seems like it is only passing the values to the function. Thus, I cannot use any of the Range methods such as getColumn(). When I attempt to do so, it gives the following error:

    error: TypeError: Cannot find function getColumn in object 1,2,3.

How can I send an actual range rather than just the values to one of my custom functions?

Note- Range in my case is the entire sheet (both of them that need to be compared).

Thanks!

Answer 1

Custom functions arguments are calculated before being passed to the code of the custom function so range will be a single value or an array of values.

You could pass a reference to a range as a string, i.e. =myFunction("Sheet!A:Z"), then use something like the following:

function myFunction(reference) {
   var range = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(reference);
   var firstColumn = range.getColumn();
   // do something
}

Note: getRangeByName work both with named ranges and references os it could be a better choice than getRange.

Answer 2

Google Script treats ranges as Arrays. So, you could better work with entered range as you work with an array. Here's good technique how to loop through an array: https://stackoverflow.com/a/14991272/5372400