Web Applications Stack Exchange is a question and answer site for power users of web applications. Join them; it only takes a minute:

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'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.

share|improve this question
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
up vote 4 down vote accepted

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)

share|improve this answer
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

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

share|improve this answer
function getNamedRange(n) {

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 it's 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 initialised items 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 ;)...

share|improve this answer

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.