Time/Date-Based Rule for Conditional Formatting via Google Apps Scripting

Question

Alright, I'm stuck. I've got a sheet that an entire team of people use to communicate with each other and sign up for timeslots for internal logins. I've got the sheet formatting performing the way I want to, but, it's too easily edited by people, because they have to be able to edit the values in the cells.
It's currently running Conditional Formatting rules, based upon time/date. Basically, if the cell's timeslot has passed, then it's blacked out. It is also blacked out if the cell isn't associated with the current day. https://docs.google.com/spreadsheets/d/1TP5iJ9AA_xqDQPiyl89ntZehd7aqDPELcN0bRMGP-SU/edit?usp=sharing

I'm trying to create a script that "onEdit" will restore any formatting (not the value) that had been changed during the edit. Got it this far, but I'm struggling to make it conditional like the UI rules allow me to do.

function myFunction() {
   var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
   var range1 = sheet.getRange('A1:B10');
   range1.mergeAcross()
   range1.setBackground('white');
   range1.setBorder(true,true,true,true,true,true);
   range1.setFontColor('black');
   range1.setFontFamily('Arial');
   range1.setFontSize(10);
   range1.setFontWeight("normal");
   range1.setFontStyle("normal");
   range1.setHorizontalAlignment("left");
   range1.setVerticalAlignment("center");
   range1.setWrap(false);
 }

Here's where I'm struggling (notes inserted)

function myFunction() {
   var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
   var range1 = sheet.getRange('A1:B10');  //  I'd have this repeated for multiple ranges
   range1.mergeAcross()
   range1.setBackground('white');  //  Need an "IF" argument for this, such as if today is Wednesday and time is before 2:30pm, then white, otherwise black.  Could probably put this at a different point in the script.
   range1.setBorder(true,true,true,true,true,true);  //  All the following attributes would simply restore defaults
   range1.setFontColor('black');
   range1.setFontFamily('Arial');
   range1.setFontSize(10);
   range1.setFontWeight("normal");
   range1.setFontStyle("normal");
   range1.setHorizontalAlignment("left");
   range1.setVerticalAlignment("center");
   range1.setWrap(false);
 }

Thanks in advance for any help you might offer!

Adam

you mean developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/… ? — Zig Mandel

Joshua Dawson Joshua Dawson · Accepted Answer · 2016-04-20T04:40:00

I've actually been trying to deal with this same problem. Sadly, google scripts doesn't seem to have a way programmatically set the conditional programming. (Here's a link to the issue. You can cast a vote at the bottom on to get them to implement it)

However, I think you might be able to implement a solution to your problem it by scratch.

The onEdit trigger passes a variable to the function it's assigned to.

function testOnEdit(e) {
  Logger.log(JSON.stringify(e));
}

Here is an example of the JSON from the event object for onEdit.

{"range":{"columnStart":3,"rowStart":2,"rowEnd":2,"columnEnd":3},"source":{},"value":"I typed this into B3!","authMode":{},"triggerUid":*********}

You might be able to use this to directly reset the format of the cells they edit. BUT... Unfortunately, onEdit is only fired when a range's values are changed--not when formatting is changed, not when data validation is changed, not when conditional formatting is changed--so to catch the cases that some rouge user decides that purple looks nice, you may want to also make the script run every minute using a timed trigger (but you need to make sure it doesn't take too long to run since it will be going off every minute and google puts a limit on total amount of time triggers can run).

As for comparing dates, these functions may be useful, but be warned that they return the local time (if you have users from multiple time zones you may need to convert to UTC).

function myFunction() {
  var date = new Date();

  date.getDay(); // the value returned by getDay() is the index of the day of the week, where 0 is Sunday, 1 is Monday...
  date.getHours(); // the value returned by getHours() is an integer between 0 and 23
  date.getMinutes(); // the value returned by getMinutes() is the current minute in the range 0-60
}

Since I too am dealing with this problem, I plan to add more insight to this solution later. Let me know if you have any insights, Adam.

Time/Date-Based Rule for Conditional Formatting via Google Apps Scripting

1 Answers