Run script when checkbox is un-checked and re-checked-CodePudding

Currently I have a script that inputs the current date and time and the users email in two adjacent columns when they check a checkbox in Google Sheets. I'd like to be able to add additional emails if the checkbox is un-checked and re-checked, however I'm lost and not sure which direction to go for completing this. This is what my sheet currently looks like: 1. Ideally I want nothing to happen when the box is unchecked, and when it is rechecked I want the users email added to the next email column. All users are in the same Google Workspace domain so hopefully recording emails shouldn't be an issue. My initial thought is that I'll have to add an additional checkbox to complete this, but I wanted to see if there was another way. How would I do this?

This is what my code looks like:

function onEdit(e) {
  var email = Session.getActiveUser().getEmail();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName("Form Responses 1");
  const range = e.range;
  range.setNote(email   new Date());
    if(sh.getName() == "Form Responses 1", range.columnStart == 13 && e.value == "TRUE") {
      e.range.offset(0,1,1,2).setValues([[new Date(),email]]);
    }
    else if(sh.getName() == "Form Responses 1", range.columnStart == 13 && e.value == "FALSE") {
      e.range.offset(0,1,1,3).setValues([[email]]);
    }
  }

Thanks for any suggestions or help

CodePudding user response：

I understand that you have a checkbox and when a user unchecks and then checks the checkbox, you would want to add additional operations. If my understanding of the question is accurate, then you can quickly reach your goal by using simple boolean logic.

You only have to add a boolean flag that fires when the user unchecks the checkbox, let's call that uncheck. Then you could add a boolean flag called recheck that activates when the checkbox is activated, but only if it was previously deactivated. Finally, you could use both booleans to introduce your additional operations. See the example below and let me know if you need further explanations.

function onEdit(e) {
  var email = Session.getActiveUser().getEmail();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName("Form Responses 1");
  var uncheck = FALSE;
  var recheck = FALSE;
  const range = e.range;
  range.setNote(email   new Date());

  if (sh.getName() == "Form Responses 1", range.columnStart == 13 && e.value ==
    "TRUE") {
    e.range.offset(0, 1, 1, 2).setValues([
      [new Date(), email]
    ]);
    if (uncheck == TRUE) {
      recheck = TRUE;
    }
  } else if (sh.getName() == "Form Responses 1", range.columnStart == 13 && e
    .value == "FALSE") {
    uncheck = TRUE;
    e.range.offset(0, 1, 1, 3).setValues([
      [email]
    ]);
  }

  if (uncheck && recheck) {
    // Additional operations
  }
}