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'm trying to create a custom Function in Google Spreadsheets that takes a range as input parameter.

 function Example(cells){
   var values = SpreadsheetApp.getActiveSpreadsheet().getRange(cells).getValues();
   var sum = 0;
   for(var i=0;i<values.length;i++){
     sum += values[i][0];
   }
   return [sum];
 }

When I try to call Example, I can't use something like Example(C15:D20) instead I need to pass the range as string like Example("C15:D20").

The only problem passing range in string format is that cells are not updated when any of the values in range is updated.

Is there any work around for this?

share|improve this question

1 Answer 1

up vote 0 down vote accepted

That's because the getRange method only accepts a string as a range. Your problem can be resolved by using just plain JavaScript:

Code

function customSum(range) {
  var sum=0;  
  for(var i=0, iLen=range.length; i<iLen; i++) {
    for(var j=0, jLen=range[0].length; j<jLen; j++) {
      sum += range[i][j];
    }
  }  
  return sum;
}

Example

I've created an example file for you: Custom Sum

Remark

Please note the difference between the two pieces of code. No API calls are used. The reference you use (cells) has more than one column !! If you want that, then you need to take that into account as well. Also note that changes in the range will be calculated immediately.

share|improve this answer
1  
Thanks! Didn't know the ranges were passed to functions as arrays. –  Akshay Aurora Aug 28 '13 at 9:47

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.