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:


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?

13 Answers 13


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:

For the new Google Sheets:


  • 1
    Lifesaver. It works, thank you. Commented Nov 27, 2018 at 4:57
  • 2
    @EmersonFarrugia I am referring to the ability for google sheets to auto-update relative cell references when you copy functions from one cell to another Commented Apr 13, 2019 at 18:02
  • If you want to link to a range in another sheet, you can do something like =myFunction("Sheet 1"&CELL("address",A1)&":"&CELL("address",A2))
    – grenmester
    Commented Aug 10, 2021 at 11:17

range is treated as javascript's 2d array. You can get the number of rows with range.length and the number of columns with range[0].length. If you want to get the value from row r and column c use: range[r][c].

  • I'm trying to find out what the first column in the range is. For example, in the range C1:F1, I want to know that the range starts on column C.
    – Senseful
    Commented Dec 24, 2010 at 23:38
  • 1
    @Senseful If you'll use your function in a cell like: =myFunction(C1:F1), then inside the function range[0][0] will return the value of C1, range[0][1] will return the value of D1, range[0][2] will return the value of E1, etc.. Is that clear?
    – Lipis
    Commented Dec 25, 2010 at 13:20
  • I think I'm not explaining myself clearly... look at your answer to this question. You recommended that I use =weightedAverage(B3:D3,$B$2:$D$2), I would like to make the function more error proof by not having to send the 2nd argument (i.e. I want to call it as =weightedAverage(B3:D3)), and then have the code automatically know that the range starts at B and ends at D, so it gets the corresponding values from the 2nd row. Note: the value "2" can be hardcoded, but "B" should not be hardcoded.
    – Senseful
    Commented Dec 25, 2010 at 19:26
  • 1
    @Senseful In general I'm against hardcoded stuff, and I think it's clearer if the weighted average would take two params. So if you are about to hard code then there are many other things that you could do. I can't find right now how can we get the B out of a given range. I would suggest you to go through the getting started guide (goo.gl/hm0xT), if you haven't already, to get an idea on how can you play with ranges and cells.
    – Lipis
    Commented Dec 26, 2010 at 16:08

When passing a Range to a Google spreadsheet function, the framework executes paramRange.getValues() implicitly and your function receives the values in the Range as a 2-dimensional array of strings, numbers or objects (like Date). The Range object is not passed to your custom spreadsheet function.

The TYPEOF() function below will tell you what kind of data you receive as parameter. The formula


will call the script like this:

function calculateCell() {
  var resultCell= SpreadsheetApp.getActiveSheet().getActiveCell();
  var paramRange= SpreadsheetApp.getActiveSheet().getRange('A1:A4');
  var paramValues= paramRange.getValues();

  var resultValue= TYPEOF(paramValues);

function TYPEOF(value) {
  if (typeof value !== 'object')
    return typeof value;

  var details= '';
  if (value instanceof Array) {
    details+= '[' + value.length + ']';
    if (value[0] instanceof Array)
      details+= '[' + value[0].length + ']';

  var className= value.constructor.name;
  return className + details;

This can be done. One can get a reference to the passed range by parsing the formula in the active cell, which is the cell containing the formula. This makes the assumption that the custom function is used on its own, and not as a part of a more complex expression: e.g., =myfunction(A1:C3), not =sqrt(4+myfunction(A1:C3)).

This function returns the first column index of the passed range.

function myfunction(reference) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  var args = formula.match(/=\w+\((.*)\)/i)[1].split('!');
  try {
    if (args.length == 1) {
      var range = sheet.getRange(args[0]);
    else {
      sheet = ss.getSheetByName(args[0].replace(/'/g, ''));
      range = sheet.getRange(args[1]);
  catch(e) {
    throw new Error(args.join('!') + ' is not a valid range');

  // everything so far was only range extraction
  // the specific logic of the function begins here

  var firstColumn = range.getColumn();  // or whatever you want to do with the range
  return firstColumn;

I have extended the excellent idea in user79865's answer, to make it work for more cases and with multiple arguments being passed to the custom function.

To use it, copy the code below, and then in your custom function call GetParamRanges() like this, passing it your function name:

function CustomFunc(ref1, ref2) {

  var ranges = GetParamRanges("CustomFunc"); // substitute your function name here

  // ranges[0] contains the range object for ref1 (or null)
  // ranges[1] contains the range object for ref2 (or null)

  ... do what you want

  return what_you_want;

Here's an example to return the color of a cell:

* Returns the background color of a cell
* @param {cell_ref} The address of the cell
* @return The color of the cell
* @customfunction
function GetColor(ref) {

  return GetParamRanges("GetColor")[0].getBackground();

Here's the code and some more explanation:

* Returns an array of the range object(s) referenced by the parameters in a call to a custom function from a cell
* The array will have an entry for each parameter. If the parameter was a reference to a cell or range then 
* its array element will contain the corresponding range object, otherwise it will be null.
* Limitations:
* - A range is returned only if a parameter expression is a single reference.
*   For example,=CustomFunc(A1+A2) would not return a range.
* - The parameter expressions in the cell formula may not contain commas or brackets.
*   For example, =CustomFunc(A1:A3,ATAN2(4,3),B:E) would not parse correctly.
* - The custom function may not appear more than once in the cell formula.
* - Sheet names may not contain commas, quotes or closing brackets.
* - The cell formula may contain white space around the commas separating the custom function parameters, or after
*   the custom function name, but not elsewhere within the custom function invocation.
* - There may be other limitations.
* Examples:
*   Cell formula: =CUSTOMFUNC($A$1)
*   Usage:        var ranges = GetParamRanges("CustomFunc");
*   Result:       ranges[0]: range object for cell A1 in the sheet containing the formula
*   Cell formula: =CUSTOMFUNC(3, 'Expenses'!B7)
*   Usage:        var ranges = GetParamRanges("CustomFunc");
*   Result:       ranges[0]: null
*                 ranges[1]: range object for cell B7 in sheet Expenses
*   Cell formula: =sqrt(4+myfunction(A1:C3))
*   Usage:        var ranges = GetParamRanges("MyFunction");
*   Result:       ranges[0]: range object for cells A1 through C3 in the sheet containing the formula
*   Cell formula: =CustomFunc(A1+A2, A1, A2)
*   Usage:        var ranges = GetParamRanges("CustomFunc");
*   Result:       ranges[0]: null
*                 ranges[1]: range object for cell A1 in the sheet containing the formula
*                 ranges[2]: range object for cell A2 in the sheet containing the formula
* @param {funcName} The name of the custom function (string, case-insensitive)
* @return The range(s) referenced by the parameters to the call
function GetParamRanges(funcName) {
  var ourSheet = SpreadsheetApp.getActiveSheet();
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  var re = new RegExp(".+" + funcName + "\\s*\\((.*?)\\)","i");
  var ranges=[]; // array of results

  try {
    var args = formula.match(re)[1].split(/\s*,\s*/) // arguments to custom function, separated by commas
    // if there are no args it fails and drops out here

    for (var i=0; i<args.length; i++) {
      var arg=args[i].split('!'); // see if arg has a sheet name
      try {
        if (arg.length == 1) { // if there's no sheet name then use the whole arg as the range definition
          ranges[i] = ourSheet.getRange(arg[0]);
        else { // if there's a sheet name, use it (after removing quotes around it)
          var sheetName=arg[0].replace(/'/g, '');
          var otherSheet=SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
          ranges[i] = otherSheet.getRange(arg[1]);
      catch(e) { // assume it couldn't be identified as a range for whatever reason
  catch(e) {}

  return ranges
  • 1
    not bad at all, but please state that this makes the assumption a custion function is only used once in a formula. F.i. I got to use forumals like: =CountCcolor(B5:B38,$A40)/2+CountCcolor(B5:B38,$A41)/2+CountCcolor(B5:B38,$A42)/2+CountCcolor(B5:B38,$A43)/2+CountCcolor(B5:B38,$A44)/2 Which from how I understand it, will not work this way. If we solve it beeng able to handle this it would be great.
    – haemse
    Commented Sep 12, 2019 at 11:01
  • Thank you so much! It helped me build: function generateBooleanOr(ref1){ let result = ""; const ranges = GetParamRanges("generateBooleanOr"); const range = ranges[0]; for (let r = 1; r <= range.getNumRows(); r+=1) { if(!range.getCell(r,1).isBlank()){ if(result){ result += " OR "; } else { result = "("; } let value = range.getCell(r, 1).getValue(); if(value.toString().includes(" ")){ value = '"' + value + '"'; } result += value; } } if(result){ result += ")"; } return result; }
    – Ryan
    Commented Aug 6, 2021 at 23:31

As an alternative, inspired by the comment from @Lipis, you can call your function with the row/column coordinates as additional parameters:

=myFunction(B1:C2; ROW(B1); COLUMN(B1); ROW(C2); COLUMN(C2))

In this form, the formula can easily be copied (or dragged) into other cells, and the cell/range references will be automatically adjusted.

Your Script function would need to be updated as such:

function myFunction(rangeValues, startRow, startColumn, endRow, endColumn) {
  var range = SpreadsheetApp.getActiveSheet().getRange(startRow, startColumn, endRow - startRow + 1, endColumn - startColumn + 1);
  return "Range: " + range.getA1Notation();

Note that the getRange function takes startRow, startColumn, and then number of rows, and number of columns - not end row and end column. Thus, the arithmetics.

  • That's the exact answer I expect. ROW and COLUMN. Commented Jun 23, 2019 at 6:02

I was working on this a few months ago and came up with a very simple kludge: create a new sheet with the name of each cell as its contents: Cell A1 could look like:

= arrayformula(cell("address",a1:z500))

Name the sheet "Ref". Then when you need a reference to a cell as a string instead of the contents, you use:

= some_new_function('Ref'!C45)

Of course, you'll need to check if the function gets passed a string (one cell) or a 1D or 2D Array. If you get an array, it will have all the cell addresses as strings, but from the first cell and the width and height, you can figure out what you need.


I distinguish two different custom functions in Google Spreadsheet, via Google Apps Script:

  1. One that calls for an API; SpreadsheetApp, (example)
  2. One that doesn't make any calls, (example)

The first function is capable of doing almost anything. Apart from calling the Spreadsheet service, it can call upon the GmailApp or any service made available by Google. API calls will slow down the process. A range can be passed on, or retrieved through the function, to access all the method available.

The second function is confined to the "Spreadsheet" only. Here one can make use of JavaScript to rework the data. These functions are normally very fast. A range passed, is nothing more than 2D-array containing values.

In your question, you start by calling for the .getColumn() method. This clearly indicates that you need a type 1 custom function, as described above.

See the following answer on how to set the spreadsheet and sheet, to create a custom function.


Create function and pass range as argument:

function fn(input) {
    var cell = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].getRange(SpreadsheetApp.getActiveRange().getFormula().match(/=\w+\((.*)\)/i)[1].split('!'));
    // above ... cell is "range", can convert to array?
    var sum=0;
    for (var i in cell.getValues() ){
        sum = sum + Number(cell.getValues()[i]);
    return sum;

Usage in spreadsheet:


It will show value as sum(A1:A10).


I've been working on this all morning and seeing evidence of what the above non-answers discuss. Senseful and I both want the ADDRESS of the passed cell, not the value. And the answer is very easy. It cannot be done.

I found some workarounds that rely on figuring out which cell contains the formula. It is difficult to say if this would have helped Senseful above. So what was I doing?

The data.

     [A]      [B]       [C]       [D]     [E]     [F]       [H]
[1] Name      Wins     Losses    Shots   Points   Fouls   Most recent
                                                          WL Ratio
[2] Sophia     4         2         15      7       1         0
[3] Gloria     11        3         11      6       0         0
[4] Rene       2         0         4       0       0         0
[5] Sophia     7         4         18      9       1         1.5

Column H is Sophia's (Wins - PrevWins) / (Losses - PrevLosses)

(7 - 4) / (4 - 2) = 1.5

But we do not know what row Sophia previously appeared in.
This can all be done using VLOOKUP if you hard-code A as the name column. After VLOOKUP, I was getting some #NA (name not found) and #DIV0 (denominator zero) and wrapped it with =IF(IF(...)) to show more palatable text in these conditions. Now I had a monstrously large expression which was unwieldy and un-maintainable. So I wanted macro expansion (doesn't exist), or custom functions.

But when I made a helper SubtractPrevValue(cell), it was receiving "7" instead of B5. There is no built-in way to get cell or range objects from the passed arguments.
If I make the user hand-enter the cell name in double quotes then I can do it... SubtractPrevValue("B5"). But that really hamstrings copy/paste and relative cell features.

But then I found a workaround.

SpreadsheetApp.getActiveRange() IS THE CELL that contains the formula. That is all I really needed to know. The row number. The following function takes a NUMERIC column number and subtracts out the previous occurrence in that column.

function SubtractPrevValue(colNum) 
  var curCell = SpreadsheetApp.getActiveRange();
  var curSheet = curCell.getSheet();
  var curRowIdx = curCell.getRowIndex();
  var name = curSheet.getRange(curRowIdx, 1).getValue();  // name to match
  var curVal =  curSheet.getRange(curRowIdx, colNum).getValue();

  var foundRowIdx = -1;
  for (var i=curRowIdx-1;i>1;i--)
    if (curSheet.getRange(i, 2).getValue() == name)
      return curVal - curSheet.getRange(i, colNum).getValue();
  return curVal;  //default if no previous found

But then I discovered that this is really REALLY slow. One and two second delays while it displays "Thinking..." So I'm back to the massively illegible, unmaintainable worksheet formula.


Instead of providing the "Range" object, the Google Developers pass pretty much a random data type. So, I developed the following macro to print out the value of the input.

function tp_detail(arg)
    var details = '';

        if(typeof arg == 'undefined')
           return details += 'empty';

        if (typeof arg !== 'object')
            if (typeof arg == 'undefined')
                return details += 'empty';

            if (arg.map)
                var rv = 'map: {';
                var count = 1;
                for (var a in arg)
                    rv += '[' + count + '] ' + tp_detail(a);
                    count = count + 1;
                rv += '}; '
                return rv;
            return (typeof arg) + '(\'' + arg + '\')';

        if (arg instanceof Array)
            details += 'arr[' + arg.length + ']: {';
            for (var i = 0; i < arg.length; i++)
                details += '[' + i + '] ' + tp_detail(arg[i]) + ';';
            details += '} ';

            var className = arg.constructor.name;
            return className + '(' + arg + ')';
    catch (e)
        var details = '';
        details = 'err : ' + e;

    return details;

There is my compilation of previous answers

 * @customfunction
function GFR(){
  var ar = SpreadsheetApp.getActiveRange();
  var as = SpreadsheetApp.getActive();
  return ar.getFormula()
    .replace(/=gfr\((.*?)\)/i, '$1')
    .reduce(function(p, a1Notation){
        var range = as.getRange(a1Notation);
          'Is "%s" a Range? %s', 
      } catch(err){
        p.push([a1Notation + ' ' + err.message]);
    return p;

It takes current formula and checks if it is a Range.

=GFR(A1:A5;1;C1:C2;D1&D2) returns

|Is "A1:A5" a Range? true|
|1 Range not found       |
|Is "C1:C2" a Range? true|
|D1&D2 Range not found   |

For the full composition the TC can call something like this

var range = as.getRange(a1Notation);
var column = range.getColumn();

A workaround is to pass two parameters for the range. one as range like A1:B2 just to trigger function and one as string like "A1:B2" which will be actually used inside function.

So function call will look like this =myFunction(A1:B2, "A1:B2")

And function declaration:

function myFunction(range, rangeStr) {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var cells = sheet.getRange(rangeStr);

  // do your job here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.