44

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?

12 Answers 12

23

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

  • Can you elaborate on what "intelligently copying range references to other cells" means please? – Emerson Farrugia Oct 30 '18 at 11:07
  • 1
    Lifesaver. It works, thank you. – Jithin Pavithran Nov 27 '18 at 4:57
  • @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 – Nathan Hanna Apr 13 at 18:02
9

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 Dec 24 '10 at 23:38
  • @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 Dec 25 '10 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 Dec 25 '10 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 Dec 26 '10 at 16:08
6

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

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. – Jianwu Chen Jun 23 at 6:02
3

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

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
}
  • 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 Sep 12 at 11:01
1

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.

1

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.

0

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.

0

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

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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