1

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

enter image description here

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

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);
}
TheMaster
  • 45,448
  • 6
  • 62
  • 85
Victor --------
  • 512
  • 1
  • 11
  • 29
  • 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 Oct 08 '18 at 21:39
  • 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 -------- Oct 08 '18 at 21:55
  • Yes, that would be correct. It's empty because the range`AF3:BJ100` is actually empty. – TheMaster Oct 08 '18 at 22:25

1 Answers1

3

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;
 ... 
tehhowch
  • 9,645
  • 4
  • 24
  • 42
  • wow, you got it right from the first try, exactly what I wanted, thank you. Could you please explain a little more about the creation of an array of colors strings? I basically need it to pick a different color depending on values from that row in Column V. So if column V row 13 is "1" color the range of cells red, etc. Should I create another range (3-rd) or expand the second range "rows" to look into column V? – Victor -------- Oct 09 '18 at 18:21
  • Thank you for the updated answer. Sorry for not making this clear.It looks like you are assuming that Column V contains the colors but instead it actually contains values, ex. V3=Completed, V4=Pending, V5=Started, etc. Based on a defined set of words I'd like to color ex("Completed" range with Red, Pending range with Yellow, Started range with green) – Victor -------- Oct 09 '18 at 21:16
  • That's a simple modification I'll let you handle. The 2nd example shows how you can access other columns in the same row. What you do with those values (such as perform a conversion `y = f(x)`) is up to you. – tehhowch Oct 09 '18 at 21:22
  • Got it sorted out! Thank you! – Victor -------- Oct 09 '18 at 22:04