Google Script: How to implement conditional formatting into spreadsheet

Question

Data:

B1: =GoogleClock // This function shows the hour in real time. 28/11/2013 15:45:00
C3: 0:15:00 //Time in hours
E3: 28/11/2013 15:45:00
F3: =SUM(E3+C3) //Result will be 28/11/2013 16:00:00
G3: =IF(C3>0;IF($B$1>=$F3;"Call";"In Time");" ")

The formula will check if B1 >= F3 the resul will Call if not In Time.

As you see in this example right now is 15:45 and the G3 will show "In Time" but when the =googleclock function mark 28/11/2013 16:00:00, G3 will change the value to Call. In that moment the background should be change from gree to Red-

Situation:

I have a formula in the Column G, the result of that formula "Call" or "In Time".

I set a conditional formatting using Menu > Format > Conditional Formatting to Column G. but the conditional only work for the existing rows.

Problem:

If I add new rows the Conditional Formatting is not set to that news rows.

Once solution is create a script that detect the value in the cell, and if the Value is Call turn backgroundcolor to Red and if In Time turn to green.

If I set this script to run in OnEdit or Run every 5min or Min the system will turn slow because this script will check the value every time.

Does exist any way to set conditional formatting like as I set from menu?

Mogsdad · Accepted Answer · 2013-11-28 19:47:27Z

3

Range.copyTo() can copy conditional formatting.

For example, this snippet will copy the conditional formatting from cell A1 to the rest of the column:

function copyFormat() {
  var source = ss.getRange('A1');
  var destination = ss.getRange('A2:A');
  source.copyTo(destination, {formatOnly:true});
}

answered Nov 28, 2013 at 19:47

Mogsdad

46k21 gold badges167 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

LAD Service Desk Over a year ago

Thanks so much.... The workaround if working... The only problem if anyone modify the A1 formatting or reset that cell. The Conditional Formatting will loss. Does exist any way to configure the conditional formatting from Script and apply it to a Cell? I mean is the same but change the ss.getRange for an script that configure that part and apply into A2:A... that way if anyone change or reset the formating the script will apply it to again automatically

Mogsdad Over a year ago

Is that possible? Maybe, I haven't tried. I imagine an onChange() trigger could assume that formatting has changed and copy it from any other cell in the column that hasn't changed.

Collectives™ on Stack Overflow

Google Script: How to implement conditional formatting into spreadsheet

Situation:

Problem:

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Situation:

Problem:

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related