I've been grappling with a script to reset all checkboxes in a Google Sheet to unchecked as part of my daily cleanup routine. I've managed to identify and uncheck checkboxes in one sheet but am struggling to efficiently extend this to all sheets. The method uncheck() seems promising, and I've shared my script and array below. Here are my queries...
Currently, I'm looping through the array to uncheck each box individually, but the process is slow. My goal is to apply this to all sheets in the spreadsheet. I'm worried about potential timeouts. Is there a way to uncheck all checkboxes on a single sheet at once, avoiding the need for a loop? Or better yet, can I tackle all checkboxes on all sheets simultaneously? Unchecking them in one go would definitely be more efficient, but so far, my attempts have been futile. (I've toyed with the idea of replacing the file daily with a template where all checkboxes are unchecked, but I can't afford to change the document ID)
How can I modify my script to cover all tabs in the spreadsheet? Do I need to fetch all the sheets and employ another loop, or is there a smarter approach?
Script:
function uncheckCheckboxes() {
var spreadsheet=SpreadsheetApp.getActive(); // grab spreadsheet
var sheet=spreadsheet.getActiveSheet(); // grab current sheet
var allRange=sheet.getDataRange(); // fetch all data
var validations=allRange.getDataValidations();
var Checkboxes=[]; // initialize array
for(var i=0;i<validations.length;i++) {
for(var j=0;j<validations[i].length;j++) {
var rule=validations[i][j];
if(rule!=null) {
var criteria = rule.getCriteriaType();
if(criteria == SpreadsheetApp.DataValidationCriteria.CHECKBOX) {
Checkboxes.push(Utilities.formatString('%s', sheet.getRange(i+1,j+1).getA1Notation())); // store cells with checkboxes only
}
}
}
}
Logger.log(Checkboxes) // Logging
for (var CB in Checkboxes) { // Loop through array
var checkbox = Checkboxes[CB];
sheet.getRange(checkbox).uncheck(); // uncheck checkbox
}
}
Array from 1 sheet (stored in 'Checkboxes' variable): [C3, D3, E3, F3, G3, H3, C4, D4, E4, F4, G4, H4, C5, D5, E5, F5, G5, H5, C6, D6, E6, F6, G6, H6, C7, D7, E7, F7, G7, H7, C8, D8, E8, F8, G8, H8, C9, D9, E9, F9, G9, H9, C10, D10, E10, F10, G10, H10, C11, D11, E11, F11, G11, H11, C12, D12, E12, F12, G12, H12, C13, D13, E13, F13, G13, H13, C14, D14, E14, F14, G14, H14, C15, D15, E15, F15, G15, H15, C16, D16, E16, F16, G16, H16, C17, D17, E17, F17, G17, H17, C18, D18, E18, F18, G18, H18, C19, D19, E19, F19, G19, H19, C20, D20, E20, F20, G20, H20, C21, D21, E21, F21, G21, H21, C22, D22, E22, F22, G22, H22, C23, D23, E23, F23, G23, H23, C24, D24, E24, F24, G24, H24, C25, D25, E25, F25, G25, H25, C26, D26, E26, F26, G26, H26, C27, D27, E27, F27, G27, H27, C28, D28, E28, F28, G28, H28, C29, D29, E29, F29, G29]
I've been laboring on this for hours and would greatly appreciate insights from someone knowledgeable in JavaScript. (I'm definitely not a JavaScript expert!)