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!

score 0 · Answer 1 · answered Apr 08 '16 at 00:13

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.

score 0 · Answer 2 · edited May 23 '17 at 11:50

0

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

edited May 23 '17 at 11:50

Community

1
1

answered Apr 08 '16 at 06:13

Max Makhrov

17,309
5
55
81

score 0 · Answer 3 · answered Oct 21 '22 at 22:34

In your cell, you would do

=myFunction("A1")

Then in your code

function myFunction(ref)
{
    var range = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(ref);
    // do something with range
}

getRangeByName is no longer used.

Source: https://developers.google.com/apps-script/reference/spreadsheet/sheet#getrangea1notation

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

3 Answers3

Linked