CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstriggers
cogre
cogre

Reputation: 15

Run script when checkbox is checked

I'm attempting to create a script that logs a users email and the date and time when they check a checkbox in google sheets. The problem I'm having is that I would need a different script for every checkbox with how my script is currently written. This is what the sheet looks like:

google sheet

Here's what my code looks like, it's sort of a jumbled mess so any insight is appreciated.

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.getA1Notation() == 'M2' && e.value == "TRUE") {
      SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1").getRange("N2").setValue(new Date());
      
      SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1").getRange("O2").setValue(email);
    }
  }

Upvotes: 0

Views: 1882

Answers (1)

Wicket
Wicket

Reputation: 38160

Use e.range.columnStart, Range.offset and Range.setValues:

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

The if statement has two expressions (the expressions are separate by a comma) so the first expression is ignored:

sh.getName() == "Form Responses 1", range.getA1Notation() == 'M2' && e.value == "TRUE"
                                  ^
                                  |

Please note that the onEdit function above has && instead of the comma.

Upvotes: 1

Related Questions