5

I have a GSheet that is using IMPORTRANGE() to import data from some other sheet. On this destination sheet I want to color each row based on the value of column A.

Because the source dataset may have hundreds or thousands of rows I can't easily use conditional formatting for each column in the row so I use the example code below:

    function formatColor(event) {
      var sheet = SpreadsheetApp.openById("ID OF THE SHEET I WANT TO FORMAT");
      var sheetCount = sheet.getSheets().length;

      // for each sheet
      for (var i = 0; i < sheetCount; i++)
      {
        var sheet = sheet.getSheets()[i];
        var maxRows = sheet.getMaxRows();
        var maxCols = sheet.getMaxColumns();
        var colors = new Array(maxRows);
        var values = sheet.getRange(1,1,maxRows).getValues();

        //for each row
        for (var j = 0; j < maxRows; j++)
        {
          //get th value of column 1
          var range = sheet.getRange(j, 1);
          var value = values[j];

          // make an array to hold the background color for each column
          colors[j] = new Array ()

          // pick a color based on the value
          if      (value == "Red")    color = "Red";
          else if (value == "Green"){ color = "Green"; }
          else if (value == "Blue") { color = "Blue"; }
          else                        color = "#ffffff";

          // fill the array (column) with the selected color
          for (var c=0; c < maxCols ; c++){
            colors[j].push(color)
          }
        }
        // in one command send the colors for each cell in the grid since
        // since setBackgroundColors() is an expensive call

        //deprecated, use setBackgroundRGB()
        sheet.getRange(1,1,maxRows,maxCols).setBackgroundColors(colors);           }  
    }

Because this function can't be triggered by onEdit on onChange (due to the IMPORTRANGE()) I am using a Time-Driven event every minute to run the function. This works well enough, changing the background color of each row every minute based on the value of the first column entered in another sheet.

However I keep getting the error:

Method Range.setBackgroundColors is deprecated.

Sometime ago I did some reasearch and I believe it said to use setBackgroundRGB() instead.

The issue with using this function is there isn't a plural version setBackgroundRGBs() that would allow be to set all of the cells at once. If you would try and use this on a sheet with hundreds of cells to format it would take forever and likely error out.

Am I missing something or is there some better way to do this.

  • Welcome to Web Applications! Great question! – Vidar S. Ramdal Mar 9 '17 at 19:56
  • Thanks, it's been bugging me for some time and I've never gotten around to fixing it. My guess is it's an oversight in the API, but it's totally possible I'm doing something wrong. – hoss Mar 9 '17 at 21:21
5

As you (maybe) know, the fact that a method is "deprecated" does not mean it doesn't work - just that it might be removed in the future.

So you're probably not getting an error, but a warning, and your code should work as intended.


That being said, there is a replacement: It seems that setBackgroundColors has just been renamed to setBackgrounds, which it is know called in the documentation.

I tried these two functions:

function doesGiveWarning() {
  var range = SpreadsheetApp.getActiveSheet().getRange("A2:C2");
  range.setBackgroundColors([["red", "white", "blue"]]);
}

function doesNotGiveWarning() {
  var range = SpreadsheetApp.getActiveSheet().getRange("A2:C2");
  range.setBackgrounds([["red", "white", "blue"]]);
}

Running doesGiveWarning causes the script editor's lightbulb to turn on: Screenshot from Script Editor
... and, clicking on it displays the warning,

while doesNotGiveWarning runs without any warning. In both cases, the cells are filled with the colors I specify.


However, you seem the be right, there is no plural version of setBackgroundRGB (yet). If you are willing to convert your RGB values to hex, you may still use setBackgrounds, as it accepts both CSS color names (red, white, blue) and hex values (#FF0000, #FFFFFF, #0000FF).

  • Oh my, to be honest I didn't even notice the name change, you might have noticed my link to setBackgroundColors actually links to setBackgrounds. I'm actually face palming. When I first noticed this issue about a year ago somewhere the docs suggested using the RGB version, it seemed like so much extra work for no gain that I put it off until now. I should have read the docs more closely before posting. – hoss Mar 10 '17 at 12:04
  • 1
    It could be that the docs have just been updated, so that it now says setBackgrounds (I noticed the name change today, too). The fact that the anchor part of your link (#setBackgroundColors) still works, points to that. – Vidar S. Ramdal Mar 10 '17 at 12:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.