Checking for changed checkbox value

Question

To get around the fact you can't pop user dialogue windows on mobile devices, I have created a simple function which posts any error messages to a cell and waits for the user to check a checkbox to confirm they have read the message. Once the checkbox is checked, the message and the checkbox are removed:

function messageHandler (mssg, mssgType) {
  sheet.getRange('D7').setValue(mssg);
  sheet.getRange('I7').activate();
  sheet.getActiveRangeList().insertCheckboxes();
  /*while (sheet.getRange('I7').getValue() !== true) {
    window("checkbox value is "+sheet.getRange('I7').getValue());
    Utilities.sleep(3000);
  }*/
  do {
    window("checkbox value is "+sheet.getRange('I7').getValue());
    Utilities.sleep(3000);
  } while (sheet.getRange('I7').getValue() !== true);

  sheet.getRangeList(['D7','I7']).activate();
  sheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
}

As you can see, I tried both while and do loops, but in both cases, the value of the checkbox is always FALSE, even after checked by the user, so the function never completes.
Is the checkbox only ever evaluated once? Do I need to add a check to my onEdit() trigger?

"Is the checkbox only ever evaluated once?" - Yes, it runs server-side, so when you run your messageHandler function, it executes once on the server. This means that sheet.getRange('I7').getValue() only reads the value at the moment the script is executing. — ninadepina
– ninadepina, Commented Oct 30 at 11:39
Thanks for that. Feel free to post this as an answer and I'll mark it as such — Stuart L
– Stuart L, Commented Oct 30 at 11:58
The code runs a loop, so it will read the contents of cell I7 until the script times out. That's in 30 seconds or 6 minutes depending on context. But that's not a very good way to detect a checkbox click. — doubleunary
– doubleunary, Commented Oct 30 at 12:03

doubleunary · Accepted Answer · 2025-10-30 13:04:01Z

2

Use a simple trigger, like this:

function messageHandler(mssg, mssgType) {
  sheet.getRange('D7').setValue(mssg);
  sheet.getRange('I7').insertCheckboxes();
}

function onEdit(e) {
  if (e?.value !== 'TRUE' || e.range.rowStart !== 7 || e.range.columnStart !== 10) return;
  sheet.getRangeList(['D7', 'I7']).clearContent();
}

Also see these onEdit(e) best practices.

edited Oct 30 at 13:04

answered Oct 30 at 12:02

doubleunary

21.9k13 gold badges38 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ninadepina · Accepted Answer · 2025-10-30 12:30:16Z

1

"Is the checkbox only ever evaluated once?" - Yes, it runs server-side, so when you run your messageHandler function, it executes once on the server. This means that sheet.getRange('I7').getValue() only reads the value at the moment the script is executing. Utilities.sleep(3000) pauses the server-side execution, but it doesn't allow updating in real time (so no interacting with the checkbox).

All this explains why the while or do-while loops never complete, the server never sees their updated value..

I would suggest using an onEdit() trigger (or adding functionality to your existing one) to detect the checkbox changes.

answered Oct 30 at 12:30

ninadepina

1,08910 silver badges17 bronze badges

7 Comments

doubleunary Oct 30 at 13:02

That's inaccurate. The code runs sheet.getRange('I7').getValue() in a loop, and will read the contents of cell I7 each time through the loop until the script times out. That's in 30 seconds or 6 minutes depending on context. Running a loop to find a checkbox value isn't a very good way to detect a checkbox click.

ninadepina Oct 30 at 13:15

Yes, getValue() runs each loop, but the script runs server-side. The script can't actually "wait" for the user to check the box in real time. So even if you loop, it'll just keep reading the original value (FALSE) and never sees the update.

doubleunary Oct 30 at 13:53

That continues to be inaccurate. Checkboxes are Boolean values. The values in the spreadsheet will change whenever any user who has access to the spreadsheet edits them, and those changes will be seen by a server-side script.

ninadepina Oct 30 at 14:10

Server-side scripts can see cell changes, but a running function can't "pause" and wait for a user to check a box in real time. You can't interact with the checkbox and have the server see that change during the same script execution. When the user clicks on the checkbox, the script is either already timed out or still looping. You can see edits if a separate trigger/script reads the cell after the user has (un)checked, but it can't wait in the middle of a server-side function for that to happen.

bricks96 Oct 31 at 18:03

Here's an example where the script runs for 30 seconds and changes you make to column B during that time are used by the script to impact the blinking cells. Blinking effect with script

ninadepina Oct 30 at 13:17

Also, just to clarify, I didn't suggest using a loop, it's part of their code. I was just explaining why it didn't work and what would be a better approach.

doubleunary Oct 30 at 16:39

"a running function can't "pause" and wait for a user to check a box" — of course it can, but that seldom makes sense. A running script can certainly sleep and then check on a spreadsheet cell value again. We're not talking about dialog boxes but spreadsheet cells here. Checkbox cells are values. See checkboxes. The code in the question won't work, of course, because it's attempting to call window(), which doesn't exist in pure JavaScript. I don't think this discussion is going anywhere.

Collectives™ on Stack Overflow

Checking for changed checkbox value

2 Answers 2

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related