CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetscheckbox
Gert-Jan M.
Gert-Jan M.

Reputation: 23

Using checkboxes to show and hide sheets with apps script

I would like to use 5 checkboxes to hide (false) and show (true) sheets. I have the impression that this is not possible with onEdit. Can someone help me how I can solve this. Thank you very much for the help. GJM

Upvotes: 1

Views: 315

Answers (2)

Yuri Khristich
Yuri Khristich

Reputation: 14537

Something like this:

function onEdit(e) {
  const name = e.source.getActiveSheet().getName();
  if (name != 'Sheet1') return; // name of the first sheet
  const col = e.range.columnStart;
  if (col != 1) return;
  const row = e.range.rowStart;
  const value = e.range.getValue();
  const sheet = e.source.getSheets()[row]; // row1 is sheet[1], row2 is sheet[2], etc
  const _ = (value) ? sheet.showSheet() : sheet.hideSheet();
}

enter image description here

enter image description here

Updated version

Show/hide sheets by its names you can this way:

function onEdit(e) {
  const name = e.source.getActiveSheet().getName();
  if (name != 'Sheet1') return;
  const col = e.range.columnStart;
  if (col != 1) return;
  const value = e.range.getValue();
  const name_to_hide = e.range.offset(0,1).getValue(); // get a name
  const sheet = e.source.getSheetByName(name_to_hide);
  const _ = (value) ? sheet.showSheet() : sheet.hideSheet();
}

It takes a name from the next column ('B' in this case), and show/hides a sheet with this name.

Or you can use solution of Dmitry Kostyuk if you don't want show sheet names in column 'B'. That way you can wire the names inside a script.

Upvotes: 0

Dmitry Kostyuk
Dmitry Kostyuk

Reputation: 1459

This code works for me:

function onEdit(e) {
  const sheet = SpreadsheetApp.getActive().getSheetByName('Sheet2');
  const targetRange =  '{"columnEnd":1,"columnStart":1,"rowEnd":1,"rowStart":1}';
  if (JSON.stringify(e.range) !== targetRange) return; // validate that the right checkbox is checked
  if(e.value === 'TRUE') return sheet.hideSheet();
  return sheet.showSheet();
}

Make sure your trigger is properly set up though. And likely you want to do better range validation that I did, but that's just for demo purposes :)

Upvotes: 1

Related Questions