Google Apps Script - Sheets - Conditional Formatting - X = highlight

Question

I have a sheet with over 100,000 cells (will soon be 300,000+ cells) used as a Gantt Chart. Each cell has an IF formula which returns the letter X if the corresponding column's header (a date) is between the row's start and end date.

But what's slowing down the sheet dramatically is the conditional formatting behind it. The conditional formatting says if the cell's value is an X, then change the cell's background color and font color to green. If no X, the background color should be white. Since conditional formatting, as I understand it, is recalculated any time you make any edit to the sheet, the performance is drastically poor. So my thought is to remove the conditional formatting and add it as a Script, with a menu button that I can click any time I want it ran, as opposed to conditional formatting running every time I make an edit to the sheet.

Here is where I've gotten with the script, which doesn't work. I've tried a couple dozen variations of this, but can't find something that works - sometimes I get it to run with no error, sometimes there is an error. Is my context wrong somewhere?

function formatting() {

  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet 1");
  var range = ss.getRange("A1:A100");
  var cellValue = range.getValues();
  if (cellValue === 'X') {
    ss.range.setBackgroundColor('#000000'); }
  else {
    cellValue.setBackgroundColor('#ffffff'); }
}

Answer 1

• cellValues is a 2D array; each cell has to be checked for being an X. Run it in a loop to get the (x, y) of the tested element.
• Neither ss.range nor cellValue could be used to set the background. It would have to be ss.getRange(x, y).setBackground... using the (x, y) coordinates to specify the Xed cell.

Have not tested it, but this should work:

function formatting() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet 1");
  var range = ss.getRange("A1:A100");
  for (var x = 0; x < range.length; x++) {
    for (var y = 0; x < range[0].length; y++) {
      if (cellValue == 'X') {
        ss.getRange(x, y).setBackgroundColor('#000000');
      } else {
        ss.getRange(x, y).setBackgroundColor('#ffffff');
      }
    }
  }
}

While this may be what you're looking for, there are concerns about speed. Another way would just be to add/remove the rule when wanted. Could be done programmatically with ConditionalFormatRule if it's too much to recreate manually often.

Answer 2

As mentioned here, you are not referencing the check correctly. However, using setBackground is not really amenable to even 100 cells, much less so 1000 or 300,000. You will find it a requirement to use the batch method Range#setBackgrounds() with a "2D" array of the background colors you wish to apply.

You can further reduce the need for massive API throughout by using application-specific details regarding the construction and operation of your Gantt chart spreadsheet to reduce the ranges you modify. Perhaps the X's can only appear or disappear in a cell that has just been edited, or perhaps they only get filled in left-to-right, or up-to-down, etc.

This function assumes the worst-case - every time it is called, you need a full recalculation of background colors.

function greenify() {
  const sheet = SpreadsheetApp.getActive().getSheetByName("Gantt");
  const HAS_X = "green", NO_X = null;
  const dr = sheet.getDataRange();
  const colors = dr.getBackgrounds();
  const VALUES = dr.getValues();

  // Inspect the value array and modify the corresponding index in colors.
  for (var r = 0, rows = VALUES.length; r < rows; ++r)
    for (var c = 0, cols = VALUES[0].length; c < cols; ++c)
      colors[r][c] = (VALUES[r][c] === "X") ? HAS_X : NO_X;

  // Write the output.
  dr.setBackgrounds(colors);
}

If only the just-modified cell needs to be considered, I recommend using a "simple trigger."

Answer 3

Here's an approach using the batch function Range.setBackgrounds(). This function takes a 2D array of values as it's argument, allowing you to set all of the cell backgrounds for a range in a single API call.

Also note that the function Range.getValues() returns a 2D array of values. To inspect each individual cell value you need to loop through the arrays.

Since you're dealing with a 2D array as both an input and an output, the logic for building the output array of background values mirrors the logic you need to use to check the current cell values. Therefore, you can build the 2D array of backgrounds values while you are looping through the cell values.

function setCellBackgrounds() {
  // The name of the sheet to process.
  var sheetName = "Sheet1";
  // The range of cells to inspect.
  var range = "A1:Z100";

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var range = sheet.getRange(range);
  var values = range.getValues();
  var colors = [];
  for (var x = 0; x < values.length; x++) {
    colors[x] = [];
    for (var y = 0; y < values[x].length; y++) {
       if (values[x][y] == 'X') {
         colors[x][y] = '#999999';
       } else {
         colors[x][y] = '#ffffff';
       }
    }
  }
  range.setBackgrounds(colors);
}

Using batch functions, instead of repeated calls to the non-batch version, is a documented Apps Script best practice.