CODEWITHSUNDEEP
google-sheets
eengebruiker
eengebruiker

Reputation: 43

Set colors at once on a range of cells

Setting colors of cells 1 by 1 is extremely slow. It is advised to do that by assigning an array to the range. I read about that and there is often refered to the following sample. However I can't get this to work so it doesn't help me.

var cell = sheet.getRange('a1');
var colors = new Array(100);
for (var y = 0; y < 100; y++) {
  xcoord = xmin;
  colors[y] = new Array(100);
  for (var x = 0; x < 100; x++) {
    colors[y][x] = getColorFromCoordinates(xcoord, ycoord);
    xcoord += xincrement;
  }
  ycoord -= yincrement;
}
sheet.getRange(1, 1, 100, 100).setBackgroundColors(colors);

My piece of code is the following and ends with the message (error): Cannot convert array to object[[]], pointing at the last line.

function TabelMarkeren() {
  var selection = SpreadsheetApp.getActiveSpreadsheet().getRangeByName("Proef") // 6 by 3;
  var colors = [];

  for (var row = 1; row <= selection.getNumRows(); ++row) {
    var cell = selection.getCell(row, 1);
    if (cell.isBlank()) {
  colors[row, 1] = "#86d8b6";
}// if
else {
  colors[row, 1] = "c4c4a4";
  }// else
  colors[row, 2] = "blue";
  colors[row, 3] = "green";
}// for
SpreadsheetApp.getActiveSpreadsheet().
getRangeByName("Proef").setBackgrounds(colors);
}

When I use a Browser.msgBox . . to show me some values from the array. It is ok. But clearly setBackgroundColors wants an object and not an array.

Upvotes: 3

Views: 327

Answers (2)

eengebruiker
eengebruiker

Reputation: 43

Thank you Ritz, I have it working now. I also had to change the colomn indexes from 1,2,3 to 0,1,2. That was another mistake. The code is now:

function TabelMarkerenn() {
  var selection = SpreadsheetApp.getActiveSpreadsheet().getRangeByName("Proef");
  var finalColors = []

  for (var row = 1; row <= selection.getNumRows(); ++row) {

    var colors = [];
    var cell = selection.getCell(row, 1);
    if (cell.isBlank()) {
      colors[row, 0] = "#86d8b6";
    }// if
    else {
      colors[row, 0] = "#c4c4a4";
    }// else
    colors[row, 1] = "blue";
    colors[row, 2] = "green";
    finalColors.push(colors);
  }// for
  Logger.log(finalColors);

SpreadsheetApp.getActiveSpreadsheet().
getRangeByName("Proef").setBackgrounds(finalColors);
}

Very happy with this, but . . . isn't there a way to directly use the finalColors array? Or is that slower or just not common practice.

Upvotes: 0

Ritesh Nair
Ritesh Nair

Reputation: 3355

The setBackgrounds expect the value in [][] and you are passing the values in []. Refer the below code.

function TabelMarkeren() {
    var selection = SpreadsheetApp.getActiveSpreadsheet().getRangeByName("Proef") // 6 by 3;
    var finalColors = [];
    for (var row = 1; row <= selection.getNumRows(); ++row) {
        var colors = [];
        var cell = selection.getCell(row, 1);
        if (cell.isBlank()) {
            colors[row, 1] = "#86d8b6";
        } // if
        else {
            colors[row, 1] = "c4c4a4";
        } // else
        colors[row, 2] = "blue";
        colors[row, 3] = "green";
        finalColors.push(colors)
    } // for
    Logger.log(colors)
    SpreadsheetApp.getActiveSpreadsheet().getRangeByName("Proef").setBackgrounds(finalColors);
}

Upvotes: 2

Related Questions