Clear cells when checking a checkbox

Question

I had this spreadsheet/script combo working perfectly, then made some visual/organization changes, and now it's not and I'm stumped why.

On the 1st sheet 'Filter' the checkbox in G1 is supposed to clear all of the cells in row 3 (which are my filter conditions).

Any pointers what I'm missing are appreciated!

https://docs.google.com/spreadsheets/d/1syLb6XaAWzmDecMzKWP9TpxZrMprYhLxBqQ4E8hTmmM/edit?usp=sharing

I'm not sure if you're able to view the script or not – here's what I've got currently:

function onEdit(e) {
  if (e.range.columnStart === 7 && e.range.getValue() === TRUE)
    ClearCells();
}

function ClearCells() {
 var sheet = SpreadsheetApp.getActive().getSheetByName('Filter');
 sheet.getRange('A3:F3').clearContent();
}

Your question can be greatly improved if you add a table to the question. [Tables](https://webapps.stackexchange.com/a/161855/) are a better alternative than spreadsheets to show your data structure. If you share spreadsheets, make sure to also add images of your sheet to avoid closure of your question, as questions here must be [self](https://meta.stackoverflow.com/a/260455/) [contained](https://meta.stackexchange.com/a/149892). [Your email address can also be accessed by the public](https://meta.stackoverflow.com/questions/394304/), when you share Google files. — TheMaster, Aug 11 '22 at 08:20

score 1 · Accepted Answer · answered Aug 10 '22 at 15:33

1

Replace e.range.getValue() === TRUE by e.range.getValue() === "TRUE" or e.value === "TRUE"

The default options for checkboxes are strings, "TRUE" and "FALSE".

answered Aug 10 '22 at 15:33

Rubén

34,714
9
70
166

Thanks! I used function your 2nd option and it worked great. – RRS Aug 10 '22 at 16:47
1

They are actually of type Boolean and not strings. The problem is `e.value` always returns a string. So ``e.range.getValue() === "TRUE"`` won't work. This ``e.value === "TRUE"`` will. This `e.range.getValue() === TRUE` doesn't work, because js boolean is `true` and not `TRUE`. Related: https://stackoverflow.com/questions/72871670 – TheMaster Aug 10 '22 at 20:54
@TheMaster Thanks... Should we mark this Q/A As duplicate of the one that pointed as related? – Rubén Aug 10 '22 at 21:21
@Rubén If you don't mind, I think we should. – TheMaster Aug 11 '22 at 08:17

Cooper · Answer 2 · 2022-08-10T15:38:23.627

1

Try this:

function onEdit(e) {
  const sh = e.range.getSheet();
  if (sh.getName() == "Filter" && e.range.columnStart == 7 && e.value == "TRUE")
  sh.getRange("A3:F3").clearContent();
}

edited Aug 10 '22 at 15:38

answered Aug 10 '22 at 15:33

Cooper

59,616
6
23
54

Thanks! That almost worked, but for some reason the script was getting triggered with some other checkboxes I have on the sheet (which do NOT use values of TRUE/FALSE). The other solution posted here worked for me, so it looks like I'm good to go now. – RRS Aug 10 '22 at 16:49
All of the default chieckboxes use values "TRUE" and "FALSE" which are strings. TRUE and FALSE are not boolean values. Only true and false are boolean values. – Cooper Aug 10 '22 at 17:01

Clear cells when checking a checkbox

2 Answers2