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

Question

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?

Answer 1

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))

Answer 2

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].

Answer 3

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

=TYPEOF(A1:A4)

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

  resultCell.setValue(resultValue);
}

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

Answer 4

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

Answer 5

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
        ranges[i]=null;
      }
    }
  }
  catch(e) {}

  return ranges
}

Answer 6

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.

Answer 7

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.

Answer 8

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.

Answer 9

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.

Answer 10

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 = '';

    try
    {
        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 += '} ';
        }
        else
        {

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


    return details;
}

Answer 11

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:

=fn(A1:A10)

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

Answer 12

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')
    .split(/[,;]/)
    .reduce(function(p, a1Notation){
      try{
        var range = as.getRange(a1Notation);
        p.push(Utilities.formatString(
          'Is "%s" a Range? %s', 
          a1Notation,
          !!range.getDisplayValues
        ));
      } 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();

Answer 13

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
}