Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
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:


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.

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

Your Answer


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.