Google-sheets – How to pass a range into a custom function in Google Spreadsheets

google sheetsgoogle-apps-scriptgoogle-sheets-custom-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
}

A cell would reference it using:

=myFunction(A1:A4)

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?

Best Answer

So I've searched long and hard for a good answer to this and here is what I have found:

  1. an unmodified range parameter passes in the values of cells in the range, not the range itself (as Gergely explained) ex: when you do this =myFunction(a1:a2)

  2. to use a range in your function you need to first pass the range in as a string (ex: =myFunction("a1:a2") ), then turn it into a range with the following code inside the function:

    Function myFunction(pRange){
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var range = sheet.getRange(pRange);
}

  3. Finally, if you want the benefits of passing in a range (like intelligently copying range references to other cells) AND you want to pass it in as a string, you have to use a default function that accepts a range as a parameter and outputs a string you can use. I detail both ways I have found below, but I prefer the 2nd.

For all Google spreadsheets:
=myFunction(ADDRESS(row(A1),COLUMN(A1),4)&":"&ADDRESS(row(A2),COLUMN(A2),4))

For the new Google Sheets:

=myFunction(CELL("address",A1)&":"&CELL("address",A2))

Related Topic