how do I get named ranges in Google Spreadsheets to be more reliable/current?

Question

I'd like to pull values from a named range into a calculation. I have this function:

function getNamedRange(n){
  SpreadsheetApp.getActiveSpreadsheet().getRangeByName(n);
}

Seems pretty simple. I have a named range called "budgetItems". It definitely exists and has about 6 values in it. But when I try to pull the values with

var items = getNamedRange("budgetItems"); items.getValues();

it usually says items is null. I've gotten it to work in the past but it seems really flaky. I suspect there is eventual consistency and caching goofing things up here.

I've attached this function (to pull the values from the range) to a menu item. When I run that menu item it takes about 15s to run 5 lines of js -- and then fails. That's... suspicious.

Answer 1

This little script will a retrieve named range and make a summation:

function namedRange() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();

  var nRange = ss.getRangeByName("budgetItems");
  var data = nRange.getValues();

  var sum=0;      
  for(var i=0; i<5; i++) {
    sum += parseInt(data[i]);
  }

  sh.setActiveSelection("B1").setValue(sum);
}

Using the above code as a formula in Google Spreadsheet, allows for significant reduction of code and API calls:

function getTest(range){
  var sum=0;
  for(var i=0, len=range.length; i<len; i++) {
    sum += parseInt(range[i]);
  }  
  return sum;
}

You can address the range as: =getTest(A1:A5) or =getTest(A1:A9)

See example file: getRangeByName (editable)

Answer 2

Not to point out the obvious but your function is never returning anything

function getNamedRange(n){
  SpreadsheetApp.getActiveSpreadsheet().getRangeByName(n);
}

you need

function getNamedRange(n){
 return SpreadsheetApp.getActiveSpreadsheet().getRangeByName(n);
}

now

var items = getNamedRange("budgetItems"); 
items.getValues();

should work just fine