Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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?

share|improve this question

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

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

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

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

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

Your Answer

 
discard

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.