Checkbox ticked/not ticked to prompt action in different cells in Google Sheet

Question

Does anyone recommend a script in Google Sheets, so when a checkbox is ticked the current date is printed in a different cell, please? It's important to note that some scripts work with blank/ticked checkbox. But, my need is to print the date 'ONLY' when a checkbox is ticked, please.

Welcome. That's a pretty broad statement, Would you please share a copy of the your spreadsheet (not including any confidential or private information). — Tedinoz, Aug 09 '19 at 23:42
Sounds like this is what you need [How do you get current date to be added to a cell when a check box is checked in another cell](https://webapps.stackexchange.com/q/130000/196152). — Tedinoz, Aug 09 '19 at 23:45
Possible duplicate of [Automatic timestamp when a cell is filled out](https://stackoverflow.com/questions/11458470/automatic-timestamp-when-a-cell-is-filled-out) — Tedinoz, Aug 09 '19 at 23:46
@Tedinoz, thank you very much for the references. I just found both scripts helpful, but for some reason they carry on printing the date if a simply copy and paste an empty checkbox. It would be great if date was printed once checkbox is ticked. — Eduardo Ordonez, Aug 10 '19 at 23:19
Would you please share a copy of you spreadsheet (excluding private or confidential information), as well as the code that you have written so far. You might show how you identify that the checkbox has been ticked, and the code to insert the date,hese are largely variations on a theme — Tedinoz, Aug 10 '19 at 23:54
Hi @Tedinoz. There's no large variation in the theme. I created a simple GS that can be found below. — Eduardo Ordonez, Aug 11 '19 at 13:01
https://docs.google.com/spreadsheets/d/1fXxhrSKGFq2bRZvE4k-UvatU-1w0BJOvWKaA1chF51Q/edit?usp=sharing — Eduardo Ordonez, Aug 11 '19 at 13:01
You'll see that column A has 'empty' tick boxes and the date is printed on column B. Actually, I don't mind that once the tick box is ticked and date is printed on column B, the date continues there even if the tick box is unticked afterwards. However, I do need that date is printed ONLY when the check box is ticked (true). You'll see what I mean if you insert a new tick box on cell A5 as a new date will be printed on cell B5 despite the fact the tick box is empty! Again, I want a 'ticked box' to prompt the date, NOT a blank tick box just added in the cell. Does it make sense? — Eduardo Ordonez, Aug 11 '19 at 13:07
function onEdit(e) { var aCell = e.source.getActiveCell(), col = aCell.getColumn(); if(col == 1) { var adjacentCell = aCell.offset(0,1); var newDate = Utilities.formatDate(new Date(), "GMT+1", "dd/MM/yyyy"); adjacentCell.setValue(newDate); }} — Eduardo Ordonez, Aug 11 '19 at 13:09
I suspect referring to column 1 as 'true/false' (regardless of tick boxes) in the script might be the solution for whenever a tick box is ticked it's 'true'. I just don't know how to adapt the script accordingly... — Eduardo Ordonez, Aug 11 '19 at 13:12

score 0 · Answer 1 · answered Aug 12 '19 at 00:44

Your IF statement doesn't test for whether the checkbox is ticked or not ticked.

It's possible to set values for the checkbox, but the default values are "TRUE" (ticked) and "FALSE" (unticked). You can establish this simply by entering +A1 in a cell to display the value of a checkbox in Cell A1.

The following code varies from your code in two respects.

var val = e.value;
This assigns the value of the edited cell to a variable.
if(col == 1 && val == "TRUE") {
This expands the IF statement to test if the edited cell was in column A AND whether the value of the edited cell is TRUE (the checkbox was ticked).

function onEdit(e) {
  var aCell = e.source.getActiveCell(), col = aCell.getColumn(); 
  var val = e.value;
  if(col == 1 && val == "TRUE") {
    var adjacentCell = aCell.offset(0,1);
    var newDate = Utilities.formatDate(new Date(), "GMT+1", "dd/MM/yyyy");
    adjacentCell.setValue(newDate);
  }
}

On a tangent, certain Event Objects are returned by the 'e' attribute. You can display these simply by inserting Logger.log(JSON.stringify(e)); into the code. It's possible (and arguably preferable) to use the Event Objects to obtain variables in almost all cases.

For example, in the context of your code:

var erange = e.range; returns the edited range.
var eCol = erange.columnStart; returns the edited column

Checkbox ticked/not ticked to prompt action in different cells in Google Sheet

1 Answers1