Uncheck all Checkboxes in all Google Sheets Tabs-CodePudding

I am using this script to uncheck all checkboxes in the PRODUCE tab. But I am wanting to uncheck all checkboxes in ALL tabs and not sure how to alter the getSheetByName to do so, or if that is even needed:

function checkBoxes() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const ws = ss.getSheetByName("Produce")
  ws.getRange(2,1,ws.getLastRow()-1).setValue(false)
}

CodePudding user response：

For now, here is the script you need based on this post.

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const ws = ss.getSheets();

  for (var sheets of ws) {
     sheets.getRange(2,1,sheets.getLastRow()-1).setValue(false);
  }
}

Let me know if there are any clarifications/modifications needed.

CodePudding user response：

In your situation, how about the following modified script?

Modified script:

function checkBoxes() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  sheets.forEach(s => s.getRange("A1:A"   s.getLastRow()).uncheck()); // Or, if you want to uncheck all checkboxes in the sheet, please use sheets.forEach(s => s.getDataRange().uncheck());
}

When uncheck() is used, only the checkboxes are unchecked.

Note:

If you want to set the exclude sheets, you can also use the following modification.

  functioncheckBoxes() {
    const excludeSheets = ["Sheet1", "Sheet3",,,]; // If you want to set the exclude sheets, please put the sheet names.

    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheets = ss.getSheets();
    sheets.forEach(s => {
      if (!excludeSheets.includes(s.getSheetName())) {
        s.getRange("A1:A"   s.getLastRow()).uncheck(); // Or, if you want to uncheck all checkboxes in the sheet, please use sheets.forEach(s => s.getDataRange().uncheck());
      }
    });
  }

Modified script:

Note:

References: