CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Bardi
Bardi

Reputation: 25

Color only white cells

I'm trying to work out a way to color only uncolored (white) cells, but leave the ones with color already untouched.

What I'm working with right now looks something like this: 

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetSelect = ss.getSheetByName("DataFile");
var range = sheetSelect.getRange(2,1,5,16)

if (range.getBackgrounds() == "#ffffff" )
   {
     range.setBackground("#EEEEEE")
   }

However it does not seem to be working, any tips or suggestions?

Upvotes: 0

Views: 117

Answers (1)

jvdh
jvdh

Reputation: 612

The problem is that range.getBackgrounds() returns an array of arrays, like this: [["color in row 1, column 1"],["color in row 1, column2"]...]

So you need to access the background color by using two nested for-loops and either keep the already changed value (i.e. if not #ffffff) or change it to #EEEEEE:

function main() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetSelect = ss.getSheetByName("Sheet1");
  var range = sheetSelect.getRange(2,1,5,16);
  var oldBackground = range.getBackgrounds()
  var newBackground = [];
  for (var i=0; i < oldBackground.length; i++) {
    newBackground.push([])
    for (var j=0; j < oldBackground[i].length; j++)
    {
      if (oldBackground[i][j] != "#ffffff") {
        newBackground[i].push(oldBackground[i][j]);
        }
      else {
        newBackground[i].push("#EEEEEE");
        }
      }
  }
  range.setBackgrounds(newBackground);
}

Upvotes: 1

Related Questions