I am a wanna-be developer who is trying to figure out how to hide a set of columns based off a checkbox being clicked.

Would anyone want to help with this code?

I have 12 different sheets(one for each month) and I would like to hide columns A-H with the checkbox in I being clicked.

Ideally I can implement on each individual sheet.

Link to spreadsheet

CodePudding user response：

There are few ways one can do it.

Easiest and most recommended among all is to group those column and it will have pretty much same use which you're looking for.

If you're willing to use appscript for it. Here how it should be done:- Open Script Editor from your spreadsheet. Declare the onEdit simple trigger which will run every time when sheet will be edited. So whenever you'll click on tickbox on I1 this function will trigger. When a trigger fires, Apps Script passes the function an event object as an argument, typically called e. For this object, we're gonna have the information we need to do our task, and also to restrict our operation to only to those months sheet and range belongs to it.

Here is the code, I tried my best to explain what happening in the code:-

function onEdit(e)
{
  var rangeEdited = e.range; // This will us range which is edited
  var sheetEdited = rangeEdited.getSheet().getName() // from range we can get the sheetName which is edited
  var mySheets = ["Jan List","Feb List"] // Put all the month sheet name in this array where you want to have this functionality 
  var rowEdited = rangeEdited.getRow() // From Range we can get Row which is edited
  var columnEdited = rangeEdited.getColumn() // From Range we can get Column which is edited
  if(mySheets.indexOf(sheetEdited) > -1) // Now we want to only restrict the operation on those sheets,so if other sheet is edited, we shouldn't run our hide function
  {
    if(rowEdited === 1 && columnEdited === 9) // we're further restricting the range of operation to run this function when only I1 is edited that is Row:- 1 and Col:- 9
    {
      hideUnhide(sheetEdited) // calling our hide function within OnEdit and passing sheetName as an argument in it
    }
  }
}





function hideUnhide(sheetEdited) {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var ssname = ss.getSheetByName(sheetEdited) // accessing the sheet which is edited
  var isItHidden = ssname.isColumnHiddenByUser(1) // checking if range is already hidden
  if(isItHidden === false) // if No, hide that range
  {
   ssname.hideColumns(1, 6)
  }
  else // if Yes, unhide that range
  {
    var hideThisRange = ssname.getRange('A:H')
    ssname.unhideColumn(hideThisRange)
  }
}

Documentation:- AppScript Events