Struggling with boolean flag in Google apps script-CodePudding

I'm attempting to create a script that fires when a checkbox is checked, and when it is unchecked and rechecked. When it's initially checked, it's supposed to output the current date, and the user's email in columns N and O. When it's unchecked, nothing is supposed to happen, and when it is rechecked, only the users email should be outputted in column P.

What's currently happening when I run my script is columns N and O are inputted with the date and email, and column P is also getting an email input simultaneously. Also, when you attempt to delete the email in column P it immediately shows up 3 columns over.

I've attempted reorganizing the boolean flags however it has been to no avail. If anyone recognizes any errors in my code, I would greatly appreciate any input.

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") { //If checkbox is checked
    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") { //If checkbox is unchecked
    uncheck = "TRUE";
    //e.range.offset(0, 2, 1, 1).setValues([[email]]);
  }

  if (uncheck && recheck) { //If uncheck and recheck are true
    //sh.getName() == "Form Responses 1", range.columnStart == 13 && e.value == "TRUE"
    e.range.offset(0, 3, 1, 1).setValues([[email]]); //supposed to output email in column P
  }
}

CodePudding user response：

Try this:

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

It wasn't clear to me yesterday when you asked this question and it's still not but let's try starting here. Tell me what's working and what's not

CodePudding user response：

In this case, the use of flags is not helping. I think that the simplest alternative is to read the values of column N and P to determine the "check" / "recheck" status.

function onEdit(e) {
  var email = Session.getActiveUser().getEmail();
  var sh = e.range.getSheet();
  e.range.setNote(email   new Date());

  if (sh.getName() == "Form Responses 1" && e.range.columnStart == 13){
    // Get cells from the same row, columns N and O
    const range = e.range.offset(0, 1, 1, 2);
    const values = range.getValues().flat()
    if(e.value == "TRUE"){
      if(values.every(v => v == '')) { // If N and O are empty then the state is checked
      range.setValues([[new Date(), email]]);
      } else {  // If N or O aren't empty then the state is rechecked
        // Write the email to same row, column P
        e.range.offset(0,3).setValue(email);
      }
    } else if (e.value == "FALSE") {
    // do nothing
    }
  }
}