thanks for the tutorial! was wondering if there was a way to specifically hide (and unhide) specific rows? I am working on a sheet where it is being used as a template and they're 82 rows that i have to keep hidden by default but if i have to make any edits to anything on those rows, i have to unhide all of them (as they are repeated formulas). I guess what i'm trying to ask is if there was a way to hide by row numbers rather then criteria from a cell
I'm not 100% sure what you mean here by "hide by row numbers rather then criteria from a cell", sorry. If the range of rows that you want hidden is together, then you might want to consider 'group' in Google Sheets. Otherwise, if you have some means to recognize those cells then you could use a button, checkbox or menu item to hide those rows. There is no way to make the row numbers blank, though. I hope this explains what you need.
@@yagisanatode ahh no, not exactly that. so i have specific rows that will always be hidden. it's not in any particular pattern. i was trying to see if i can just put those rows into a script rather then have the script detect which rows to hide by values in a cell
You're welcome. You could use the offset() method to look at another cell value or range of cell values in the same row or column, and if it matches, do the thing.
Absolutely! the hideColumn or hideColumns methods are your go to here: developers.google.com/apps-script/reference/spreadsheet/sheet#hideColumns(Integer,Integer)
Hi Scott, Thanks a bunch for this script. Worked out for just what I was looking for. Had one question for you - If you wanted to reverse the "hide" function and "unhide", do you have a suggestion?
I will try this. Many appreciations to your quick reply. 2 quick questions as I was trying to implement, but a bit out of my depths. 1. Can you have 2 scripts targeting one sheet? As I tried to addd a second one, it seemed to deactivate the first one (hide script). 2. Can I duplicate the 1st script and change a bit of code to make the unhide work in that one? I struggle with coding. This goes beyond expectations so I thank you for even the first reply! @@yagisanatode
@@allanburns2 Re 1: I'm not too sure what you mean by 2 scripts. Scripts generally refer to the project bound to the Sheet. You can set an if or switch statement within the Function that reads for a certain condition and carry it out. Say your text says "show" when show is found, it shows the row and when the text says "hide" the row is hidden. Remember each function should have a unique name. Otherwise the last function that the Apps Script V8 reads will be the one that is run. Re2: Yes. Just rename the function name
Hi Scott, Thank you so much for you tutorial, it is really helpful! Now I would like to use this function for all sheets in my workbook. Can you help me how I can change the script so that the values in all sheets are hidden? Thank you in advance! Kirsten
Hi Kirsten, Glad it was helpful. Yes you will have to look at the .getSheets() method which will return an array of sheets when called from the spreadsheet class and then iterate over that method with something like a forEach() loop. This would make a great learning exercise to fortify your knowledge and extend it a little. Best of luck! developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getsheets developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/forEach
Thank you so much! I am glad I found this video
thanks for the tutorial! was wondering if there was a way to specifically hide (and unhide) specific rows? I am working on a sheet where it is being used as a template and they're 82 rows that i have to keep hidden by default but if i have to make any edits to anything on those rows, i have to unhide all of them (as they are repeated formulas). I guess what i'm trying to ask is if there was a way to hide by row numbers rather then criteria from a cell
I'm not 100% sure what you mean here by "hide by row numbers rather then criteria from a cell", sorry. If the range of rows that you want hidden is together, then you might want to consider 'group' in Google Sheets. Otherwise, if you have some means to recognize those cells then you could use a button, checkbox or menu item to hide those rows. There is no way to make the row numbers blank, though. I hope this explains what you need.
@@yagisanatode ahh no, not exactly that. so i have specific rows that will always be hidden. it's not in any particular pattern. i was trying to see if i can just put those rows into a script rather then have the script detect which rows to hide by values in a cell
@@DonneDiscordia You can hide set rows based on say, a menu item click or button with just your permissions to access it.
Thank you so much Scott for this video, how about hide the cell with 2 value ?
You're welcome. You could use the offset() method to look at another cell value or range of cell values in the same row or column, and if it matches, do the thing.
Hi! Is it possible instead of hiding rows to hide columns with a certain condition? Thanks!
Absolutely! the hideColumn or hideColumns methods are your go to here: developers.google.com/apps-script/reference/spreadsheet/sheet#hideColumns(Integer,Integer)
Hi Scott, Thanks a bunch for this script. Worked out for just what I was looking for. Had one question for you - If you wanted to reverse the "hide" function and "unhide", do you have a suggestion?
Great to hear.
You can use the unhideRow() method for this: developers.google.com/apps-script/reference/spreadsheet/spreadsheet#unhiderowrow
I will try this. Many appreciations to your quick reply.
2 quick questions as I was trying to implement, but a bit out of my depths.
1. Can you have 2 scripts targeting one sheet? As I tried to addd a second one, it seemed to deactivate the first one (hide script).
2. Can I duplicate the 1st script and change a bit of code to make the unhide work in that one? I struggle with coding.
This goes beyond expectations so I thank you for even the first reply!
@@yagisanatode
@@allanburns2 Re 1: I'm not too sure what you mean by 2 scripts. Scripts generally refer to the project bound to the Sheet.
You can set an if or switch statement within the Function that reads for a certain condition and carry it out. Say your text says "show" when show is found, it shows the row and when the text says "hide" the row is hidden.
Remember each function should have a unique name. Otherwise the last function that the Apps Script V8 reads will be the one that is run.
Re2: Yes. Just rename the function name
Hi, Is it possible to hide certain column for users who has editor permission?
No, unfortunately. All users will see edits and changes like hiding a column.
Hi Scott,
Thank you so much for you tutorial, it is really helpful! Now I would like to use this function for all sheets in my workbook. Can you help me how I can change the script so that the values in all sheets are hidden?
Thank you in advance!
Kirsten
Hi Kirsten, Glad it was helpful. Yes you will have to look at the .getSheets() method which will return an array of sheets when called from the spreadsheet class and then iterate over that method with something like a forEach() loop.
This would make a great learning exercise to fortify your knowledge and extend it a little. Best of luck!
developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getsheets
developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/forEach
@@yagisanatode Great, thanks Scott!