Run script when checkbox is checked

Question

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

When the checkbox is checked (column M), the date and time, and the email of the person who checked the box should be entered into columns N and O respectively. — cogre, Aug 01 '22 at 18:34

Rubén · Accepted Answer · 2022-08-02T21:05:38.053

1

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.

edited Aug 02 '22 at 21:05

answered Aug 01 '22 at 18:36

Rubén

34,714
9
70
166

1

Thank you! My spreadsheet now works properly, however when I attempt to run or debug the code in Apps Script I receive an error. It doesn't really effect the outcome now that it works, but is it something I should be worried about? This is the error: TypeError: Cannot read property 'range' of undefined onEdit @ onEdit.gs:5 – cogre Aug 01 '22 at 18:40
@cogre See [How can I test a trigger function in GAS?](https://stackoverflow.com/q/16089041/1595451) – Rubén Aug 01 '22 at 18:42
Would it be possible to add an additional input for column P if the checkbox is unchecked and checked again? – cogre Aug 01 '22 at 19:17
Yes, it's possible. Please try to do this by yourself. If get stuck, please post a new question. – Rubén Aug 01 '22 at 19:24
@cogre I noticed that in your recent question you have oversight the replacement of a comma by `&&`... I edited my answer to highlight this change – Rubén Aug 02 '22 at 21:06

Run script when checkbox is checked

1 Answers1