Color Cells based on chart “Start Date” and “End Date” using Google Script in Google Sheets

Question

Currently I have a working version of this by using conditional formatting in google sheets. Unfortunately the performance diminishes the more conditions I'm adding. I'd like to convert my conditional formatting to google script that runs every hour. This is basically a gantt chart that works perfectly for my needs.

The formula for conditional formatting is

=and(AF$2>=$L3,AF$2<=$M3)

where Column L is Start Date and Column M is End Date

Cell AF$2, AG$2, AH2... are Dates, starting from today, tomorrow, day after tomorrow, etc.

What would be the alternative to use google script instead. This is what I have so far:

function columnToLetter(column)
{
  var temp, letter = '';
  while (column > 0)
  {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}


function setCellBackgrounds() {
  // The name of the sheet to process.
  var sheetName = "MySheet";

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var range = sheet.getRange("AF3:BJ100");
  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++) {

 //trying to apply the formula "=and(AF$2>=$L3,AF$2<=$M3)" here but I'm getting an error
       if (columnToLetter(32+y)+2 >= columnToLetter(12)+x && columnToLetter(32+y)+2 <= columnToLetter(13)+x ) {
        colors[x][y] = '#999999';
       } else {
         //colors[x][y] = '#ffffff';
       }
    }
  }
  range.setBackgrounds(colors);
}

Put the error in your question. Note that if you have the values as an array, you don't need to use the letter notations - just offset the appropriate amount in the 2D array. Note that you will need to be careful about indexing elements that don't exist (arrays are 0-base) — tehhowch
you mean something like values[0][2]? How do I access the actual value in that cell. When i try values[0][2] or whatever offset in this array the result is null/empty — Victor --------
Yes, that would be correct. It's empty because the rangeAF3:BJ100 is actually empty. — TheMaster

tehhowch tehhowch · Accepted Answer · 2018-10-09T12:52:21

You can replace your conditional format formula with a comparison of two ranges, L3:M and AF2:BJ2, and applying the colors to your output range, AF3:BJ.

const cols = sheet.getRange("AF2:BJ2").getValues()[0], // Extract the first (& only) row.
      rows = sheet.getRange("L3:M" + sheet.getLastRow()).getValues();

const inWindowColor = "#999999",
      otherColor = null; // null values -> reset color to default.
// Create a rectangular 2D array of color strings. Each row needs an array of colors
// with each inner element corresponding to the given column.
const output = rows.map(function (datePair) {
  var start = datePair[0],
      end = datePair[1];
  return cols.map(function (day) {
    var inWindow = day && start && end // guard against "" values
        && day.getTime() >= start.getTime() && day.getTime() <= end.getTime();
    return (inWindow ? inWindowColor : otherColor);
  });
});

sheet.getRange("AF3").offset(0, 0, output.length, output[0].length)
    .setBackgrounds(output);

The above uses the Array#map class method, and performs date comparisons numerically (as is required when using equality checks). A null value is given for out-of-window cell colors to reset the background to its default color, in accordance with the method description. One could rewrite the last line to eliminate the offset call, but I figured "AF3" was easier to maintain than (3, 32, output.length, output[0].length).

Other reading

If the color to use is in the same row, and a known column, it can be read without significant changes. Obviously, you require the color range to be the same size as the rows range (since each row has a corresponding color). You then simply need to use the 2nd auto-parameter given to Array#map - the index of the current element. Here I show a 2-column color definition range ("in window" (V) and "ended" (W))

const lastRow = sheet.getLastRow(),
      cols = ...,
      rows = sheet.getRange("L3:M" + lastRow).getValues(),
      colorDefs = sheet.getRange("V3:W" + lastRow).getValues();

const output = rows.map(function (datePair, row) {
  ...
    var color = null;
    if (day && start && end) {
      if (day > end) { // no equality, no `.getTime()` needed
        color = colorDefs[row][1]; // "ended" color is in 2nd index.
      } else if (day.getTime() >= start.getTime()) {
        color = colorDefs[row][0]; // "in window" color is in 1st index.
      } else { /* not started yet */ }
    } else { /* `day`, `start`, and/or `end` were "falsy" */ }
    return color;
 ...

Color Cells based on chart “Start Date” and “End Date” using Google Script in Google Sheets

1 Answers