2

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!

Improve this question

3 Answers 3

Reset to default
1

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.

Improve this answer
3
  • Thank you! I think this would work well in a less complex item... Right now, I have an intro page with lots of information, including checkboxes that open tabs as you check them. I think having additional tabs open from the 7 sheets already there would get confusing in this instance. But I will keep that in mind for future documents, to help avoid the problem in the first place!
    – EMC
    Jun 10, 2020 at 12:15
  • I used the dashboard technique in very complex sheets. Up to 11 tables with 2500 rows and columns of data, I keyed in by hand. About 185 intermediate calculations. All feeding into the dashboard. Altogether I am using seven tabs, two of which are hidden and protected. The system is entirely dynamic and allows the user to update checkboxes, drop downs and fill in numbers on the dashboard to get instant results. Admittedly the thing is so complex that I only feel comfortable being the sole user. And it does give me a tool for quick over-the-phone answers to client questions.
    – dougp01
    Aug 24, 2020 at 21:06
  • The easiest way I know to do this method is to build the sheet like you have already done. Then insert a new blank tab and reference the input and outputs from your working sheet. Best of luck ~doug
    – dougp01
    Aug 24, 2020 at 21:08
0

What you found over and over regarding that hiding specific cells is not possible is completely true, but this is a typical X-Y problem (asking about X to do Y instead of asking how to do Y)

Instead of asking how to hide a cell, you, all those who did the same question before, should ask is how to hide from the user the content.

Solution: Use Google Apps Script to add / remove the content

You could use the Google Apps Script Properties Service, another sheet (even in form another spreadsheet), a file, etc. to store the cell values

The labels and structure could be set completely by using the script or by copy it from a "template"

Improve this answer
1
  • Thank you! I think I have the gist of what you mean, which is a great idea for a solution. As I said, I'm a newbie... do you have any great resources or a similar example that I could reference as I tinker?
    – EMC
    Jun 9, 2020 at 12:34
-2

Just make the text white........

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.