Reset checkboxes to false

Question

I have a Google spreadsheet where column A has checkboxes in each row. I have written a script to perform a function on all rows where the checkboxes are checked, but I want to add in at the end a reset function so that all checked boxes are unchecked again after the script is run.

I've tried using a for loop like this:

var dataRange = sheet.getRange('A3:A');
var values = dataRange.getValues();

for (var i = 0; i < values.length; i++) {
for (var j = 0; j < values[i].length; j++) {     
  if (values[i][j] == true) {
    values[i].setValue(false);
  }
}    
} 

But clearly this doesn't work as I get an error.

Does anyone know how this could be done?

Answer 1

Alternatively, Since you want to uncheck everything in the range (and all of the range have checkboxes in them), just do:

sheet.getRange('A3:A').setValue(false);

without checking/looping.

Answer 2

This solution posted above does reset all checkboxes; however, it also unexpectedly changes cells with the value of 1 in it to FALSE.

I want to make a script to reset checkboxes on ALL sheets...

function resetCheckBoxesAllSheets() {
var ss = SpreadsheetApp.getActive();
var allsheets = ss.getSheets();
for (var s in allsheets){
var sheet=allsheets[s]

var dataRange = sheet.getRange('A4:Z100');
var values = dataRange.getValues();
for (var i = 0; i < values.length; i++) {
  for (var j = 0; j < values[i].length; j++) {
    if (values[i][j] == true) {
      values[i][j] = false; // Modified
    }
  }
}
dataRange.setValues(values);


}//end of  sheets loop.
}// end of function...

Answer 3

How about this modification? I think that there are several solutions for your situation. So please think of this as one of them.

Modification points :

• The reason of the issue is values[i].setValue(false);. values[i] is an array. Please use the range for setValue().
  • But to use setValue() in the for loop leads to higher cost. So in this modification, I used setValues().
• Put "false" to values, if values[i][j] is "true".
• Put the modified values to the sheet using setValues().

Modified script :

var dataRange = sheet.getRange('A3:A');
var values = dataRange.getValues();
for (var i = 0; i < values.length; i++) {
  for (var j = 0; j < values[i].length; j++) {
    if (values[i][j] == true) {
      values[i][j] = false; // Modified
    }
  }
}
dataRange.setValues(values); // Added

Reference :

• setValue()
• setValues()

If this was not what you want, please tell me. I would like to modify it.