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?

2 Answers 2


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


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;


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


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.
    – system64
    Commented Aug 28, 2013 at 9:47

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:


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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