Using a simple Trigger with onEdit with specific Cell on Specific time

Question

I need some help with creating a function for a trigger in google sheets. The scenario is that I have a checkbox on C15 and D15. The idea is to have the checkbox be unchecked by itself after 7days of checking it off. For example, If I were to check the box on a specific time, I would like to have the box be unchecked 7days on that specific time.

I'm having the issue of figuring out how to make a function to activate a trigger based on editing the cell. I currently use a week time based trigger that looks like this for other checkboxes but it's based on week timer and not on a onEdit.

 function WeeklyReset(){
  var ws = SpreadsheetApp.openById("Sheet");
  var tArray = ["C15:D15","C18:D21","C24:D25"];
  var d = new Date();
  var hr = d.getHours();
  var day = d.getDay();
  var min = d.getMinutes(); 

  if (hr == 5 && day == 1){
       for (tr of tArray){
      var tRange = ws.getRange(tr);
    tRange.setValue(false);
  }
 }
 }

Updated Code Credit to AndrewJames for helping build the code

function onEdit(e) {
 // resets the cell note's timestamp - but only if the cell's
 // checkbox was changed to "selected" (value of TRUE):
 var range = e.range;
 var rangeA1 = range.getA1Notation(); 
 var sheetName = e.range.getSheet().getName();
 if (sheetName === "Sheet Id" && rangeA1 === "C15" && range.getValue()) {
var date = new Date();
 // date.getTime() is a value in milliseconds:
range.setNote('Checked:' + date.getTime() + ':' + date);
}
}

function resetCheckbox() {
// resets the checkbox from "selected" to "unselected", if it has been 
// selected for longer than the required period of time. This is 
// run by a time-based trigger (e.g. once per minute):
var mySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet Id");
var range = mySheet.getRange("C15");
var note = range.getNote().split(':');
if (range.getValue() && note.length > 1) {
      var lastChecked = note[1];
      var now = new Date().getTime(); // milliseconds
  if (now - lastChecked > 7 * 24 * 60 * 60 * 1000) { // 7 days  ...
}
}
}

I've also set up a trigger to run function resetCheckbox On a time-driven event with a time based trigger on mintue timer and interval or every minute.

Answer 1

Here are 2 functions which can help, when used together.

Capture Checked Timestamp

This captures the time in milliseconds when the checkbox was most recently checked. The timestamp is stored as a cell note, along with a human-readable version of the timestamp (but you could store it elsewhere, if you prefer).

function onEdit(e) {
  // resets the cell note's timestamp - but only if the cell's
  // checkbox was changed to "selected" (value of TRUE):
  var range = e.range;
  var rangeA1 = range.getA1Notation(); 
  var sheetName = e.range.getSheet().getName();
  if (sheetName === 'YOUR SHEET NAME HERE' && rangeA1 === 'B14' && range.getValue()) {
    var date = new Date();
    // date.getTime() is a value in milliseconds:
    range.setNote('Checked:' + date.getTime() + ':' + date);
  }
}

EDIT: This function runs whenever a user makes an edit, but only acts if the specific cell is edited (B14 in this case). It does not fire when another script or a formula changes a value.

(Credit to @cooper for the correction.)

Expiration Checker

This function checks if the most recent timestamp is older than the expiry threshold. If it is, then the checkbox is unset:

function resetCheckbox() {
  // resets the checkbox from "selected" to "unselected", if it has been 
  // selected for longer than the required period of time. This is 
  // run by a time-based trigger (e.g. once per minute):
  var mySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("YOUR SHEET NAME HERE");
  var range = mySheet.getRange('B14');
  var note = range.getNote().split(':');
  if (range.getValue() && note.length > 1) {
    var lastChecked = note[1];
    var now = new Date().getTime(); // milliseconds
    if (now - lastChecked > 5 * 60 * 1000) { // 5 minutes
      range.uncheck();
    }
  }
}

This function needs to be attached to a time-based trigger - for example, one which runs once every minute.

In my example, for testing, I set the threshold to 5 minutes.

Remember to replace YOUR SHEET NAME HERE in both functions.

---

Update

How I created my trigger:

1. From the code page, hover over the alarm clock icon and click on "triggers".

2. Choose resetCheckbox as the function to run.

3. Choose Time-driven as the event source.

4. Choose Minutes timer as the trigger type.

5. Choose Every minute as the interval.

"there isn't a trigger for 7 days" - that is not relevant here.

You don't want to run your trigger once every 7 days. You want the trigger to run far more frequently than that. Once a minute is good - or once an hour, perhaps.

You want the now - lastChecked value to be longer than 7 days. In my test case I set that value to 5 minutes, not 7 days (I didn't want to wait for 7 days to test my code).

You would want to use a similar small threshold period, for testing.

Then, when you are happy it is all working, you can set the threshold to 7 days:

if (now - lastChecked > 7 * 24 * 60 * 60 * 1000) { // 7 days
  ...
}

---

Multiple checkboxes

When you are happy that one checkbox is working correctly, you can enhance the approach to cover multiple checkboxes.

There are various different ways this could be done. Here is one which involves minimal code changes:

Step 1: Create a new global variable:

var checkboxes = [ 'C15', 'C16' ];

This is placed outside of any existing functions (it is not inside its own function, either).

Step 2: Change the onEdit() function:

Replace this:

&& rangeA1 === 'B14'

with this:

&& checkboxes.includes(rangeA1)

This simply checks to see if the edited cell is one of your checkbox cells.

Step 3: In resetCheckbox(), wrap the checking code in a loop:

checkboxes.forEach((checkbox) => { // the new loop
  var range = mySheet.getRange(checkbox); // no hard-coded cell address!
  var note = range.getNote().split(':');
  if (range.getValue() && note.length > 1) {
    var lastChecked = note[1];
    var now = new Date().getTime();
    if (now - lastChecked > 5 * 60 * 1000) {
      range.uncheck();
    }
  }
} ) // end of the new loop

This iterates over each checkbox cell address. Note how we no longer need to use a hard-coded cell reference - now we have mySheet.getRange(checkbox); instead.