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

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.

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

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")
  }

CodePudding user response：

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((e => {
      if((dtv - new Date(e).valueOf()) > 2 * hr) {
        return ["#ffff00"];//hilite
      } else {
        return ["#ffffff"];//default bg
      }
   }));
   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.

CodePudding user response：

I consider you should keep your conditional formatting formula, and create a time-triggered "inocuous" App-Script function, something like this:

function myFunction() {
  var sameValue = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange("A1").getValue()
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange("A1").setValue(sameValue)
}

It takes the value in A1 and "updates" its own previous value. But what it does importantly is that it updates the value of "Now()" each minute by "activating" the spreadsheet and making a "change". So each minute "Now()" will be updated and your conditional formatting too