Using setBackgroundRGB() over setBackgroundColors()

Question

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.

Answer 1

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:
... 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).