CODEWITHSUNDEEP
javascriptgoogle-apps-script
Michael Blaustein
Michael Blaustein

Reputation: 901

Error calling function second time

I have a function which works just fine the first if I call it just once, but when I call it repeatedly within a for loop, I get the following error:

TypeError: getNamedRange is not a function, it is string.

Doing a search on this error gives me a clue that this is a javascript error, not a Google Apps Script error. I haven't worked much with javascript, but I suspect it may have something to do with how I return the value from the function.

This is the code which calls the function:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var baseSheet = ss.getSheetByName("Base");

var catCol = 9;
var riskAreaColumn = 10;

var numRows = baseSheet.getDataRange().getNumRows();

// I am not using this var, should I be?
var data = baseSheet.getDataRange().getValues();

var cell;
var rangeName;
var range;
var rule;

for(var i=2; i<numRows; i++){

  cell = baseSheet.getRange(i, riskAreaColumn);

  rangeName = getNamedRange("CategoryRiskRange",baseSheet.getRange(i, catCol).getValue());

  range = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(rangeName);

  rule = SpreadsheetApp.newDataValidation().requireValueInRange(range).build();

  cell.setDataValidation(rule);

  }

  SpreadsheetApp.flush();
}

This is the function being called:

function getNamedRange(categoryRange, category) {

var categoryList = SpreadsheetApp.getActive().getRangeByName(categoryRange).getValues();
for (var i = 0; i < categoryList.length; i++) {

  if (categoryList[i][0] == category) {
    getNamedRange = categoryList[i][1];
    return getNamedRange;
  }
}
}

The first time through the for loop works, the second time gives me the aforementioned error. Thank you for reading this, I hope it's clear.

Upvotes: 1

Views: 2009

Answers (2)

Bilal
Bilal

Reputation: 429

you are overwriting the function definition here:

getNamedRange = categoryList[i][1];

this will work:

 if (categoryList[i][0] == category) {
    return categoryList[i][1];
  }

Upvotes: 4

KathyA.
KathyA.

Reputation: 681

Javascript doesn't interpret things until it gets to them, and is very happy to redefine things when you tell it to.

The first time through, it sees 

function getNamedRange(categoryRange, category)

and says "oh, a function! Cool!" But in that function, you have the line 

getNamedRange = categoryList[i][1];

and it says "Oh, so getNamedRange is something else now. Okay, I'm fine with that."

Rename your variable, and you should be fine.

Upvotes: 3

Related Questions