Hi still a relative newbie here...I've got some nested loops which cycle through each cell in a sheet range looking for a background color and if it finds a match then the background colour is changed. Yes - I am sorry for this approach(!). Am thinking I must be able to get the background colours via an array and then apply a transformation to the array using .map(?) but have experimented and haven't worked it out so far. Other examples on S/O don't show a substitute approach that I have been able to apply across. Any advice/thoughts would be greatly appreciated. Thanks

function myColorFunction() {
 var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("My Sheet");
 
 for (var y = 1; y < 70; y  ){
   for (var x = 1; x < 50; x  ){
   var bghex = ss.getRange(y,x).getBackground();
   if(bghex == '#001d66') { //occasional cell colour
     ss.getRange(y,x).setBackground("#027db6");
   }
   else if (bghex == '#00288b') { //main bkgrnd colour
     ss.getRange(y,x).setBackground("#0297db");
   }
   else if (bghex == '#fbf025') { //yellow fill-in
     ss.getRange(y,x).setBackground("#fdd201");
   }
   }
   Logger.log('Line '   y   ' complete');
 }
}

CodePudding user response：

Instead of using getBackground(), use getBackgrounds(), this way instead of getting individual values, you get an array where you can apply the map() function.

Here is an example replacing red backgrounds.

function myColorFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("My Sheet");
  var range = sheet.getRange("A1:B10");
  var backgrounds = range.getBackgrounds(); // Return 2D arr

  // Applies map() function on each row
  for (let i = 0; i < backgrounds.length; i  ) {
    backgrounds[i] = backgrounds[i].map(function(item) { return item == "#ff0000" ? "#0000ff" : item; });
  }

  // Set new backgrounds
  range.setBackgrounds(backgrounds);
}