CODEWITHSUNDEEP
dategoogle-apps-scriptgoogle-sheetscell
aku181
aku181

Reputation: 17

Change sheet cells color onEdit

I figured out how to compare dates in Google Sheets but when I try to enter more dates for some reason all the cells that were green and red become all red. Also how can I make two cells red if only one cell has a date?

Example: In cell D18 the Due date is 4-18-2014 and in cell E18 the cell is blank. I want to make both cells red so I would know that I should find out why is that cell red.

This is the code I have so far:

function onEdit() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName('Copy of Project Sheet 1');
var values1Rule1 = s.getRange('E2:E1000').getValues();
var values2Rule1 = s.getRange('D2:D1000').getValues();
var range3Rule1 = s.getRange('D2:E2');
var color1 = 'Red';
var color2 = 'Green';

for (var row in values1Rule1) {
for (var col in values1Rule1[row]) {
if (values1Rule1[row][col] > values2Rule1[row][col]) s.getRange(s.getRange('D2').offset(row, col, 1, 2).getA1Notation()).setBackgroundColor(color1);
else if (values1Rule1[row][col] < values2Rule1[row][col]) s.getRange(s.getRange('D2').offset(row, col, 1, 2).getA1Notation()).setBackgroundColor(color2);
  else s.getRange(s.getRange('D2').offset(row, col, 1, 2).getA1Notation()).setBackgroundColor('white'); }}
};

Upvotes: 0

Views: 174

Answers (1)

Henrique G. Abreu
Henrique G. Abreu

Reputation: 17752

All you need to do is add this condition as an OR clause in your red condition, e.g.

if (values1Rule1[row][col] > values2Rule1[row][col] || values1Rule1[row][col] === '')

But there's lots of "minor" problems with your code. First of all, you're doing way too many API calls unnecessarily. This is a big performance issue. For example, when you offset, you already have the new range, there's no need to getA1Notation then get the range again, you could do:

s.getRange('D2').offset(row, col, 1, 2).setBackgroundColor(color1);

But that's still two calls, getting D2, then offseting. You could get the desired range at once:

s.getRange(row+1, 4, 1, 2).setBackgroundColor(color1);

I'd go even further and build a matrix of colors and set it all at once after the loop:

s.getRange('D2:E1000').setBackgroundColors(colors);

But even better, inside an onEdit you should only work on what has just being edited, instead of triggering a full recalculation of your colors because the user edited something on another column or another sheet entirely.

I think your code should be something like this:

function onEdit(e) {
  var ss = e.source;
  var s = ss.getActiveSheet();
  if( s.getName() !== 'Copy of Project Sheet 1' ) return; //only interested in one sheet
  var r = s.getActiveRange();
  var c = r.getColumn();
  if( c !== 4 && c !== 5 ) return; //only interested in changes on columns D or E
  r = r.offset(0, c === 4 ? 0 : -1, 1, 2);
  var v = r.getValues()[0];
  r.setBackgroundColor(v[1] === '' || v[1] > v[0] ? 'red' : v[1] < v[0] ? 'green' : 'white');
}

--edit

You can not run this function manually directly, because it needs a parameter that is passed only when it runs automatically. But you can emulate it with a test function, like this:

function testEdit() { onEdit({source:SpreadsheetApp.getActive()}); }

Upvotes: 1

Related Questions