Json - Check all boxes in slicers

JoeyTat250
May 22, 2024
133 views
0 votes
2 Answers

I’m trying to programmatically check all boxes in a Google Sheet’s sheet using Apps Script.

I think there is code somewhere whether it’s from the source or in some other code else that edits the slicers to show (e.g.) 1 of 4 checkboxes, but I was unable to find it or it may not be visible to me.

Specifically, I’m trying to get the Slicers and select all checkboxes in said slicers, then iterate to the next slicer until there are no more.

Along with extensively looking through Google documentation and forums, I have yet to find a solution.

Here are just a few of the .gs code I have tried:

function selectAllSlicers() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  var slicers = spreadsheet.getSlicers();

  slicers.forEach(function(slicer) {
    var items = slicer.getRange().getValues();
    slicer.setRangeValues(items);
  });
}

//This works in retrieving the names of the Slicers.

// -

function checkAllSlicers() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  var slicers = spreadsheet.getSlicers();

  slicers.forEach(function(slicer) {
    var range = slicer.getRange();

    var checkboxes = range.getDataValidations();

    checkboxes.forEach(function(row, rowIndex) {
      row.forEach(function(cell, colIndex) {
        if (cell == SpreadsheetApp.DataValidationCriteria.CHECKBOX) {
          range.getCell(rowIndex + 1, colIndex + 1).setValue(true);
        }
      });
    });
  });
}

// No visible change on the sheet.

//--

function checkSlicersOnSheet1() {
  const ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/<SPREADSHEET_ID>/edit');

  const sheet = ss.getSheetByName('Sheet 1');
  const slicers = sheet.getSlicers();

  for (const slicer of slicers) {
    const slicerValues = slicer.getRange().getSlicerValues();
    if (slicerValues) {
      for (const value of slicerValues) {
        slicer.setIsItemChecked(value, true);
      }
    }
  }
}

//Changes all cells to "TRUE".

Answers

Chosen as BEST ANSWER

This is what I wanted and this shows "All" or has all checkboxes ticked in all Slicers:

function selectAllFiltersInSlicers() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // Set the working sheet to the Sheet1 sheet
  var slicers = sheet.getSlicers(); // Gets slicers in sheet (Slicers are the black filters at the top of the sheet)
  
  for (var i = 0; i < slicers.length; i++) { // Iterates through each slicer in forward order
    var slicer = slicers[i];
    var column = slicer.getColumnPosition(); // Gets the column the slicer is filtering
    var criteria = SpreadsheetApp.newFilterCriteria()
                                  .setHiddenValues([]) // This sets empty array or no hidden values. So all filters including blanks will show.
                                  .build(); 
    slicer.setColumnFilterCriteria(column, criteria); // Resets the column position and adds criteria.
  }
}

(Edit)

Select the range of cells where the check boxes are in one contiguous range and then run this function.

function myfunk() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const rg = sh.getActiveRange();
  const row = rg.getRow();
  const col = rg.getColumn();
  rg.getValues().forEach((r, i) => {
    r.forEach((c, j) => {
      let r = sh.getRange(row + i, col + j).getDataValidation();
      if (r && r.getCriteriaType() == SpreadsheetApp.DataValidationCriteria.CHECKBOX) {
        Logger.log(sh.getRange(row + i, col + j).getA1Notation())
        sh.getRange(row + i, col + j).check();
      }
    })
  });
}

This version will toggle them:

function myfunk() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const rg = sh.getActiveRange();
  const row = rg.getRow();
  const col = rg.getColumn();
  rg.getValues().forEach((r, i) => {
    r.forEach((c, j) => {
      let r = sh.getRange(row + i, col + j).getDataValidation();
      if (r && r.getCriteriaType() == SpreadsheetApp.DataValidationCriteria.CHECKBOX) {
        Logger.log(sh.getRange(row + i, col + j).getA1Notation())
        let r = sh.getRange(row + i, col + j);
        if(r.isChecked()) {
          r.uncheck();
        } else {
          r.check();
        }
      }
    })
  });
}

Please signup or login to give your own answer.

Click here to cancel reply.

Json – Check all boxes in slicers

Answers