Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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

>Solution :

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]]);
  }
}
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading