Google Apps Script does not parse variable as int in array coordinate

Question

I'm attempting to change the font color in an entire row based on the content in column B matching some pre-defined strings.

Works just fine although the index is off by 1 and I need to account for this:

values[row][1]

Error: "TypeError: Cannot read property "1" from undefined.":

values[row+1][1]

Attempting also throws the same error:

values[1+Number(row)][1]

Here is the entire codeblock:

function colorRow(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var range=sheet.getDataRange();
  var values = range.getValues();

  for (var row in values){ 
    var result;
    if(values[1+Number(row)][1].toString()=="stalker"){
      result = sheet.getRange(row,1,1,range.getLastRow())
      result.setFontColor("purple");
    }else if(values[1+Number(row)][1].toString() == "engineer"){
      result = sheet.getRange(row,1,1,range.getLastRow())
      result.setFontColor("yellow");
    }else if(values[1+Number(row)][1].toString() == "warrior"){
      result = sheet.getRange(row,1,1,range.getLastRow())
      result.setFontColor("red");
    }else if(values[1+Number(row)][1].toString() == "medic"){
      result = sheet.getRange(row,1,1,range.getLastRow())
      result.setFontColor("green");
    }else if(values[1+Number(row)][1].toString() == "esper"){
      result = sheet.getRange(row,1,1,range.getLastRow())
      result.setFontColor("blue");
    }else if(values[1+Number(row)][1].toString() == "spellslinger"){
      result = sheet.getRange(row,1,1,range.getLastRow())
      result.setFontColor("orange");
    }
  }
  SpreadsheetApp.flush(); 
}

rGil · Accepted Answer · 2014-08-10 02:12:25Z

According to the Mozilla JS docs in reference to 'for in' loops:

Because the order of iteration is implementation dependent, iterating over an array may not visit elements in a consistent order. Therefore it is better to use a for loop with a numeric index (or Array.forEach or the for...of loop) when iterating over arrays where the order of access is important.

You will best be served by using a standard 'for loop' with a numeric index for this case:

for (var row=0; row<values.length; row++){ 
var result;
if(values[1+row][1].toString()=="stalker"){
  result = sheet.getRange(row,1,1,range.getLastRow())
  result.setFontColor("purple");
} // stuff
}

But actually, to answer the question, you have the right idea with converting the index to a number, because it is actually a string.

The error is coming not from the loop, but from the call to getRange(). Apps script wants an integer/number as a parameter, but you are actually giving it a string. If you really want to keep the 'for-in' loop (I would advise against it), you need to change all instances of row within the loop to a number or integer.

result = sheet.getRange(Number(row),1,1,range.getLastRow());

Collectives™ on Stack Overflow

Google Apps Script does not parse variable as int in array coordinate

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related