Trying to store a range of rows/cells in google sheets script. Want to then change color and formatting of row/cell

1
votes

I am changing the background color of rows and cells in a google sheet with a script. Right now I loop through rows and cells and perform the color changes. This is extremely slow. I know the best practice is to change the colors all at once. I am having trouble finding out how to store my cell range in an array and then perform a function again that range.

In the example here I'm trying to set every other row to white or grey if it is an odd or even row.

I have created a script I think would work but it fails.

function setRowColors() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var range = sheet.getDataRange();
  var oddrange=[];
  var evenrange=[];

  for (var i = range.getRow()+7; i < range.getLastRow(); i++) {
    var rowRow = i +1;
    var rowRange = range.offset(i, 0, 1);

    if (i % 2 == 0) {
      oddrange.push(rowRange);   
    } else {
      evenrange.push(rowRange);
    }         
  }

  oddrange.setBackgroundColors("#ffffff");
  evenrange.setBackgroundColors("#efefef");

  }

When I run this code I get this error "Cannot find function setBackgroundColors in object "

2
google-apps-scriptgoogle-sheetsgoogle-apps

2 Answers

2
votes

Issue:

  • Calling setBackgroundColors() on a array instead of on Range object.

Flow:

  • Create a 2 dimensional array of colors matching the range where you would like to set colors and set them in a batch.

Snippet:

var lRow = sheet.getLastRow();
var headerRows = 7;
var numRows = lRow - headerRows;
var numCols = sheet.getLastColumn();
var [rows1d, cols1d] = [numRows, numCols].map(function(num){ 
    return Array.apply([],new Array(num)); //or just `getBackgrounds()` to get a 2d array 
})
var colors2d = rows1d.map(function(row, i){
    var color = i%2 === 0 ? "#ffffff" : "#efefef";
    return cols1d.map(function(col){
        return color;
    })
})
sheet.getRange(headerRows + 1, 1, numRows, numCols).setBackgrounds(colors2d);

References:

2
votes

In addition to the other answer, I'd like to bring to your attention that you can create alternating rows in your spreadsheet by first selecting all the cells (click the space between the row index and column index) (Also works over any range of cells) then going to Format > Alternating Colors and it will bring up a menu that you can select what colors you want.

Alternating Colours