CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsspreadsheetconditional-formatting
Kevin
Kevin

Reputation: 77

Google Apps Script function to change cell color based on current time vs the time typed in that cell

I have been struggling to make a consistent function that can do this. I have a column on a sheet that contains either blank or a time (in 24:00 notation), that is used to record when the data was last updated by the user. I would like for that cell to change colors when 2 hours have elapsed between the cell's content and the actual current time. The purpose is to notify myself that an update needs to made.

Conditional formatting fails to complete this reliably. Copy/pasting rows overwrites CF, and then leaves gaps in the range that the CF is applied to.

Example Sheet

enter image description here

Using Google's conditional formatting works, but not consistently. The CF formula I use is:

=TIMEVALUE(now()-time(2,0,0))>TIMEVALUE(C1) applied to the whole column

enter image description here

I have made a poor attempt to do this using another script that changes cell colors, along with a trigger, and am failing. I can't figure out the most efficient way to read and edit the data and not sure how to integrate the time. How do I solve this and what is the best way to approach this problem?

Failed attempt & not sure how to proceed or restart:

function checkTime(e) {

  var sheet = e.source.getActiveSheet();

  // do nothing if not column 3
  if (e.range.getColumn() !== 3) return
  if (sheet.getName() !== "Stocks") return

  const mainsheet = sheet.getSheetByName("Stocks")
  const times = mainsheet.getRange("C2:C").getValues()

  // not sure how to make this if-statement

  // set the cell color
  e.range.setBackground("yellow")
  }

Edit: So for clarification, I dont need the time entered automatically, nor for this to be run OnEdit. A 15min trigger would suffice. Basically all I am trying to do is check column X for times greater than 2 hr old and highlight them yellow. If not greater than 2h old, or blank, do nothing or if already yellow, return to the color of other cells in that same row.

Upvotes: 0

Views: 1126

Answers (2)

Cooper
Cooper

Reputation: 64100

Update column3 when columns 1 or 2 change and highlight column 3 that are older than 2 hours. If edits occurs more often than every two hours then this should work. Other wise you may have to call a separate polling function with a timebased trigger.

function onEdit(e) {
  //e.source.toast("Entry");
  const sh = e.range.getSheet();
  if (sh.getName() == "Sheet0" && e.range.columnStart < 3 && e.range.rowStart > 2) {
    //e.source.toast("Gate1")
    let hr = 3600000; 
    let dtv = new Date().valueOf();
   sh.getRange(e.range.rowStart,3).setValue(new Date()).setNumberFormat("HH:mm");
   let bg = sh.getRange(3,3,sh.getLastRow() - 2).getValues().flat().map((el => {
      if((dtv - new Date(el).valueOf()) > 2 * hr) {
        return ["#ffff00"];//hilite
      } else {
        //return ["#ffffff"];//default bg
        //Logger.log(e.range.offset(0, -1).getBackground());
        return [e.range.offset(0, -1).getBackground()];//alternative solution
      }
   }));
   if(bg && bg.length > 0) {
     //e.source.toast("Gate2");
     sh.getRange(3,3,bg.length, bg[0].length).setBackgrounds(bg);
   }
  }
}

Demo:

Changed the 2 hour threshold to a smaller time to work faster for the demo.

enter image description here

Here's a timebased update script:

function update() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  let hr = 3600000; 
  let dtv = new Date().valueOf();
  let bg = sh.getRange(3, 3, sh.getLastRow() - 2).getValues().flat().map((el => {
    if ((dtv - new Date(el).valueOf()) > 2 * hr) {
      return ["#ffff00"];//hilite
    } else {
      return [e.range.offset(0, -1).getBackground()];//alternative solution
    }
  }));
  if (bg && bg.length > 0) {
    sh.getRange(3, 3, bg.length, bg[0].length).setBackgrounds(bg);
  }
}

Run this to create update trigger or create it manually.

function createUpdateTrigger() {
  if(ScriptApp.getProjectTriggers().filter(t => t.getHandlerFunction("update")).length == 0) {
    ScriptApp.newTrigger("update").timeBased().everyMinutes(5).create();
  }
}

Upvotes: 2

Lle.4
Lle.4

Reputation: 616

I don't love the solution from @Cooper since it doesn't apply to the general case if you're not updating the sheet every two hours. Even if you are, in our worst case scenario, you update the sheet 1 hour and 59 minutes after a cell was last updated; and then not again for another 1 hour and 59 minutes, in which case the flip to yellow is 1 hour and 58 minutes delayed. I would suggest also putting this function in a trigger--any less frequent than once every 5 minutes should not run into a max trigger executions error (but my suggestion is usually every 15 minutes). This way, you keep your data fairly fresh no matter what, and you can also have it update when edits are made if you want to.

Upvotes: 1

Related Questions