-1

I'm trying to set a block of colors at the same time.

I could use setBackgrounds for a range and do it all at once by first giving each cell a color name like so

Multiple column conditional formatting

but I want to other blended colors and the RGB function setBackgroundRGB but for a range. Can it be done? or do I have to loop through and set it individually?

setBackground has setBackgrounds for a range. Is there something equivalent for setBackgroundRGB?

I don't see it here in the reference. https://developers.google.com/apps-script/reference/spreadsheet/range . Was wondering if anybody had a solution beside just loop through everything.

Community
  • 1
  • 1
jason
  • 3,811
  • 18
  • 92
  • 147
  • Why can't you use the setBackgrounds and use colour code instead of name. `#FF0000` instead of `red` – Srik Aug 30 '13 at 10:10
  • I need this color `RGB(207,226,243)`. Can you express that in one code? – jason Aug 30 '13 at 10:13

2 Answers2

2

One option is to create your own function (a kind of wrapper), something like (you need to apply the validations necessary):

/* CODE FOR DEMONSTRATION PURPOSES */
function setColorToRange() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var colorsRGB = [
    [
      [255, 0, 0],
      [0, 255, 0],
      [0, 0, 255],
      [0, 0, 0]
    ],
    [
      [0, 0, 0],
      [0, 0, 255],
      [0, 255, 0],
      [255, 0, 0]
    ],
    [
      [0, 255, 0],
      [0, 255, 0],
      [0, 255, 0],
      [0, 255, 0]
    ]
  ];
  var cell = sheet.getRange("B5:E7");

  setBackgroundsRGB(cell, colorsRGB);
}

function setBackgroundsRGB(range, colors) {
  var row = range.getRow(), lastRow = range.getLastRow();
  var initColumn = range.getColumn(), lastColumn = range.getLastColumn(), column;
  var ss = range.getSheet(), colorRow, colorColumn, subRange;
  for (colorRow = 0; row <= lastRow; ++row, ++colorRow) {
    for (column = initColumn, colorColumn = 0; column <= lastColumn; ++column) {
      subRange = ss.getRange(row, column);
      subRange.setBackgroundRGB.apply(subRange, colors[colorRow][colorColumn++]);
    }
  }
}
/* CODE FOR DEMONSTRATION PURPOSES */
wchiquito
  • 16,177
  • 2
  • 34
  • 45
1

Use the rgbToHex() function provided in this answer: RGB to Hex and Hex to RGB.

function rainbow() {
  var back = [];
  for (var row=0; row <16; row++) {
    back[row]=[];
    for (var col=0; col <16; col++) {
      back[row][col] = rgbToHex(row*16,col*16,Math.round(row+col/2)*16);
    }
  }
  debugger;
  SpreadsheetApp.getActiveSheet()
                .getRange(1, 1, back.length, back[0].length)
                .setValues(back)
                .setBackgrounds(back)
}

Footnote:

rgbToHex(207,226,243) == #cfe2f3
Community
  • 1
  • 1
Mogsdad
  • 44,709
  • 21
  • 151
  • 275