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

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

The first problem, you have not assigned the result of this chained statement to anything. getRangeByName returns an object that references the named range and must be assigned to something, or its result is lost. Second, you have not returned anything in the function. With these changes you would have:

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

^{(and yes, you can do away with the result variable and just return the full statement. It just looks nicer this way)}
That takes care of the getNamedRange function.

Now to deal with the calling part.

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

Assuming the changes to the function above, you should now have a correctly initializeditems variable^1.. It still won't work, as is, however, because the getValues method returns an array for all the cells within the items range. You haven't assigned anything to that.

You should change that line to:

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

The array variable 'budgetItemsValues' should now contain all the cell values within the budgetItems named range, formatted as an array of an array.

Once you have that, you can work on the array using the many methods in google app scripting available. But that's beyond the scope of this answer.

^1.This is assuming that you pass a valid name range to the getNamedRange function in the first place. If not, then the result will be undefined. This can be checked by using the Array.isArray(object) method which returns true if the object parameter is a valid Array. If you really want to go the full nine yards on error checking, you can check for this in the function, issuing a throw statement, whilst using the try / catch statements when calling into the function. But now I'm really going beyond the scope of this answer ;)...

Answer 3

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