Google Spreadsheet Script : ELSE statement overriding IF statement

Question

I've hacked together a Google Spreadsheet script that changes the background color of a specific country cell of the row a country code was entered into. My ELSE statement returns the BG color to white, so if a cell doesn't have a country code, it's BG remains white.

I am using a toString().match, so I can include input such as: "AU, BEfr, BEnl" in one cell and highlight all of their cells individually.

It was working great for awhile until I started adding more lines. Perhaps I've deleted something by accident?

The correct country cell highlights for 1 second and then instantly returns to a white background.

What am I missing here?

Many thanks!

function onEdit() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var s = ss.getSheetByName('R186');
   var values1Rule1 = s.getRange('Locales').getValues();
   var color1 = '#ADD8E6';

   for (var row in values1Rule1) {
      for (var col in values1Rule1[row]) {
         if(values1Rule1[row][col].toString().match("AR") == "AR")
            s.getRange(s.getRange('AR').offset(row,col,1,1).getA1Notation()).setBackgroundColor(color1);
         if(values1Rule1[row][col].toString().match("AU") == "AU") 
            s.getRange(s.getRange('AU').offset(row,col,1,1).getA1Notation()).setBackgroundColor(color1);
         if(values1Rule1[row][col].toString().match("BEfr") == "BEfr") 
            s.getRange(s.getRange('BEfr').offset(row,col,1,1).getA1Notation()).setBackgroundColor(color1);
         if(values1Rule1[row][col].toString().match("BEnl") == "BEnl") 
            s.getRange(s.getRange('BEnl').offset(row,col,1,1).getA1Notation()).setBackgroundColor(color1);
         if(values1Rule1[row][col].toString().match("BR") == "BR")
            s.getRange(s.getRange('BR').offset(row,col,1,1).getA1Notation()).setBackgroundColor(color1);
         if(values1Rule1[row][col].toString().match("CAen") == "CAen")
            s.getRange(s.getRange('CAen').offset(row,col,1,1).getA1Notation()).setBackgroundColor(color1);

         else 
            s.getRange(s.getRange('I3:BO').offset(row,0,1,200).getA1Notation()).setBackgroundColor('white').setFontColor('black'); 
      }
   }
};

AndyG AndyG · Accepted Answer · 2014-01-29T19:43:58

What is happening is that you have a lot of if-statements that will be evaluated separately, and then a final if-else-statement that will be evaluated. So long as the input is not "CAen", it seems you will be resetting the background to white.

Change your code to read

         if(values1Rule1[row][col].toString().match("AR") == "AR")
            s.getRange(s.getRange('AR').offset(row,col,1,1).getA1Notation()).setBackgroundColor(color1);
         else if(values1Rule1[row][col].toString().match("AU") == "AU") 
            s.getRange(s.getRange('AU').offset(row,col,1,1).getA1Notation()).setBackgroundColor(color1);
         else if(values1Rule1[row][col].toString().match("BEfr") == "BEfr") 
            s.getRange(s.getRange('BEfr').offset(row,col,1,1).getA1Notation()).setBackgroundColor(color1);
         else if(values1Rule1[row][col].toString().match("BEnl") == "BEnl") 
            s.getRange(s.getRange('BEnl').offset(row,col,1,1).getA1Notation()).setBackgroundColor(color1);
         else if(values1Rule1[row][col].toString().match("BR") == "BR")
            s.getRange(s.getRange('BR').offset(row,col,1,1).getA1Notation()).setBackgroundColor(color1);
         else if(values1Rule1[row][col].toString().match("CAen") == "CAen")
            s.getRange(s.getRange('CAen').offset(row,col,1,1).getA1Notation()).setBackgroundColor(color1);
         else 
            s.getRange(s.getRange('I3:BO').offset(row,0,1,200).getA1Notation()).setBackgroundColor('white').setFontColor('black'); 
      }

The current code is pretty poorly written otherwise. You can simply get the toString() result into a temporary variable and compare on that (don't know why you need the match function instead of the == operator). Also, you can see about putting your highlighting into its own function that takes a string argument and a color.

Google Spreadsheet Script : ELSE statement overriding IF statement

1 Answers