CODEWITHSUNDEEP
google-apps-script
Dave
Dave

Reputation: 23

Turn all check boxes to false in total workbook

I've used the following script to uncheck checkboxes on an active spreadsheet.

function ResetCheckboxesOnSheet() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var rg=sh.getDataRange();
  var vA=rg.getDataValidations();
  var cbA=[];
  for(var i=0;i<vA.length;i++) {
    for(var j=0;j<vA[i].length;j++) {
      var rule=vA[i][j];
      if(rule!=null) {
        var criteria = rule.getCriteriaType();
        if(criteria == SpreadsheetApp.DataValidationCriteria.CHECKBOX) {
          sh.getRange(i+1,j+1).setValue(false)

        }
      }
    }
  }
}

But I'm trying to figure out if I can uncheck the boxes on a workbook. I have 38 timesheets, 38 different tabs. Each employee has 5 or 6 different questions to check a true or false checkbox. I would like to be able to uncheck all the check boxes on all 38 tabs by running the script one time.

Upvotes: 2

Views: 214

Answers (2)

alberto vielma
alberto vielma

Reputation: 2342

If you want to uncheck all checkboxes in every sheet inside a Spreadsheet, you can do it by using the uncheck() method. This is an example code on how you would do it: 

function uncheckAllBoxes() {
  // Get the Spradsheet linked to this Apps Script project
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  // Get all sheets in the Spreadsheet
  var sheets = ss.getSheets();
  // Uncheck all checkboxes in every sheet
  sheets.forEach((sheet) => sheet.getDataRange().uncheck());
}

Notice:

  • getDataRange() will return a 2D array with all the sheet values.

  • Keep in mind the Apps Script Quotas because you are handling a Spreadsheet with a big amount of sheets.

Upvotes: 1

pgSystemTester
pgSystemTester

Reputation: 9932

You just need to loop through the entire workbook. This should do it for you:

function KillALlChecks(){
var ss = SpreadsheetApp.getActive();
var allSheets = ss.getSheets();

allSheets.forEach(function(sheet){
    ResetCheckboxesOnSheet(sheet);
    }
}



function ResetCheckboxesOnSheet(sh) {

  var rg=sh.getDataRange();
  var vA=rg.getDataValidations();
  var cbA=[];
  for(var i=0;i<vA.length;i++) {
    for(var j=0;j<vA[i].length;j++) {
      var rule=vA[i][j];
      if(rule!=null) {
        var criteria = rule.getCriteriaType();
        if(criteria == SpreadsheetApp.DataValidationCriteria.CHECKBOX) {
          sh.getRange(i+1,j+1).setValue(false)

        }
      }
    }
  }
}

Upvotes: 0

Related Questions