CODEWITHSUNDEEP
google-apps-script
Kevin P.
Kevin P.

Reputation: 1053

Toggle columns visible based on checkbox

I don't understand why this is not doing anything...? Hopefully a simple solution for someone.

I have columns A:B hidden and a checkbox in D1 that when checked I would like to show/hide the columns. (this script was watered down from the second one below as I was trying to just test the basics).

function onEdit(e) {
  if (e.range.getA1Notation() != "D1") return;

  if (e.value == "TRUE") {
    ui.alert('Checkbox checked');
    //SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().hideColumns(1, 3);
  }
  else if (e.value == "FALSE") {
    ui.alert('Checkbox unchecked');
    //SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().showColumns(1, 3);
  }
}

I have also tried a different approach, this is ultimately an exact replica of my needs.

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var activeSheet = ss.getActiveSheet();
  
  //Ensure on sheet.
  if(activeSheet.getName() == 'Sheet3' && activeSheet.getRange(1,4).getValue() == "TRUE"){
        //ui.alert('Cell toggled on');
        activeSheet.showColumns(1,3);
  }else if (activeSheet.getName() == 'Sheet3' && activeSheet.getRange(1,4).getValue() == "FALSE"){
        //ui.alert('Cell toggled off');
        activeSheet.hideColumns(1,3);
  };
};

Link to test sheet if needed: https://docs.google.com/spreadsheets/d/1mMxyTpLk5ixldcbPwehMqFz7vfEnTy4fkgRgcdDH3WM/edit?usp=sharing

Upvotes: 2

Views: 79

Answers (1)

Tanaike
Tanaike

Reputation: 201398

Modification points:

  • If you actually use your showing script. ui is not defined.
  • You can use the event object.
  • In order to confirm the checkbox, you can also use isChecked(). When the method of isChecked() of Class Range is used, when the range is the checkbox, the boolean type (true or false) is returned. When the range is not a checkbox, null is returned. By this, it can be checked whether the cell is a checkbox.

When these points are reflected in your script, it becomes as follows.

Modified script:

function onEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();
  if (sheet.getSheetName() != "Sheet3" || range.rowStart != 1 || range.columnStart != 4) return;
  const ui = SpreadsheetApp.getUi();
  if (range.isChecked() === true) {
    ui.alert('Checkbox checked');
    sheet.hideColumns(1, 3);
  } else if (range.isChecked() === false) {
    ui.alert('Checkbox unchecked');
    sheet.showColumns(1, 3);
  }
}

References:

Upvotes: 1

Related Questions