1

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?

0

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.

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

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.