Google-sheets – Google Sheets – Hide Specific Cells (NOT Rows or Columns) based on Checkbox within a Sheet (Is this even possible?)

google sheetsgoogle-apps-scriptgoogle-sheets-cell-format

First off, let me say that I am as newbie as possible when it comes to Scripts and coding in general (up until last Friday, my experience was one class in C++ and some HTML back in high school…) so I welcome all ideas.

I have a document with multiple Sheets within a Workbook in Google Sheets. I have managed, with a lot of googling and tinkering, to create a script so that the first sheet with checkboxes dictate hiding/showing of the other sheets in the workbook. On each of those additional sheets, however, there are three "columns" (quotation marks used because each "column" is made up of multiple actual columns; the "columns" are just visual dividers between the different parts of each sheet when the gridlines are not viewable – see image.) "columns"

What I'd like to do is similar to the first page, where clicking a checkbox makes a sheet appear/disappear, but I'd like it to be pre-determined and pre-filled cells on the same sheet that are shown/hidden. So, if you click something in the far left "column", say "individual daily schedule" the corresponding portion in the middle "column" would appear; when un-checked, the middle "column" portion would disappear and allow cells from farther down the page to move up into that space (without the information being deleted, so that checking the box would make it appear again) . example of unchecking, hiding and moving

The biggest part of my issue here is that I can't hide specific rows (I'd lose material that I want to keep in the other "columns") nor can I hide specific columns (same issue, since this main middle "column" has information related to multiple areas with checkboxes.)

From my googling, I have found, over and over, that hiding specific cells (vs. columns and rows) is not possible in Google Sheets. I'm curious as to whether anyone has an idea on how to make something like this work – hiding a range vs. a row/column – with a checkbox. I'm open to any and all ideas, whether script-related or formatting-related!

Best Answer

One can look into conditional formatting to make a single cell white background and white text but a cell selection would highlight the content.

My preference is to display results on a new tab. I call it a dashboard. You can ask for user inputs and use lookups of one type or another to display results. All the heavy lifting is on other tabs I can then protect the contents and hide the tab. It's not perfect but seems to work for me.