Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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.

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

2 Answers 2

up vote 3 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]);
  }

  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)

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){
  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

share|improve this answer

Your Answer

 
discard

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.