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

function getNamedRange(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.

  • Are those ranges dynamic or static? – Jacob Jan Tuinstra Feb 18 '13 at 11:19
  • As in, do they use $A$1 or A1? It's the latter. Don't know why that'd matter, the data in them changes rarely. – jcollum Feb 18 '13 at 16:42
  • Range X = [first,second,third], interaction user will make it X = [second,third,first]. – Jacob Jan Tuinstra Feb 18 '13 at 16:47
  • Sorry, I don't understand that. I made a sample spreadsheet to show you what I'm encountering: docs.google.com/spreadsheet/…; that's a simple spreadsheet with one named range and a function that accesses it. The function can't find the named range. – jcollum Feb 18 '13 at 16:55
  • I already tried to answer your question but I deleted it. Check the code of the deleted answer. – Jacob Jan Tuinstra Feb 18 '13 at 17:11

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]);


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)

  • I don't see the difference between what I'm doing and that code. – jcollum Feb 18 '13 at 17:15
  • OK your sample is working and mine isn't, I'll look into it a bit. – jcollum Feb 18 '13 at 17:17
  • That's why I deleted it........ – Jacob Jan Tuinstra Feb 18 '13 at 17:18
  • so the only difference I see is this: var sh = ss.getActiveSheet();, could that be it? I added that to my code (and cleaned up a typo) and it seems to be working. – jcollum Feb 18 '13 at 17:22
  • Hmm, taking out ss.getActiveSheet doesn't seem to have changed anything. I'm going to have to look at the original sheet later... – jcollum Feb 18 '13 at 17:24
function getNamedRange(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 variable1.. 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 ;)...


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

function getNamedRange(n){

you need

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


var items = getNamedRange("budgetItems"); 

should work just fine

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.