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.

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

This is a workaround that will dynamically update the range references when the range is updated (such as when a new row or column is inserted into it), but still input it as a string to myFunction:

=myFunction(CELL("address",A1)&":"&CELL("address",A2))

Courtesy of @Nathan Hanna at https://webapps.stackexchange.com/a/58179/22521

| improve this answer | |

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.