Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

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

share|improve this answer

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

share|improve this answer
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
@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

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;
share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

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;
share|improve this answer
I think that this post also answer the following question on Stack Overflow: passing cell references to spreadsheet functions – Rubén Jun 8 at 3:37

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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