Soooo nice. Another great content. After 6+ month watching you and getting thrilled by your app-script tutorials it's nice to see a progress. Nice to understand everything you teach us. Thank you, Sir
Not sure I would need this script to hide or unhide sheets but some parts of the script can be really useful for another script of mine. 😏😉 Always learning something in this channel. Keep on the good work! 👍
Thanks yet again for another great video. I accomplished the same thing, but in a slightly different way. Rather than hiding/showing multiple sheets at once, I tied the script to the individual checkboxes. So, when you check a box, it shows that sheet, and when you uncheck it, it hides it. Here's the script: function onEdit(a) { var range = a.range if (range.getA1Notation() == "B9") { var Sheet1 = a.source.getSheetByName("Sheet1"); if (range.isChecked()) { Sheet1.showSheet(); } else { Sheet1.hideSheet(); } } if (range.getA1Notation() == "B10") { var Sheet2 = a.source.getSheetByName("Sheet2"); if (range.isChecked()) { Sheet2.showSheet(); } else { Sheet2.hideSheet(); } } if (range.getA1Notation() == "B11") { var Sheet3 = a.source.getSheetByName("Sheet3"); if (range.isChecked()) { Sheet3.showSheet(); } else { Sheet3.hideSheet(); } } if (range.getA1Notation() == "B12") { var Sheet4 = a.source.getSheetByName("Sheet4"); if (range.isChecked()) { Sheet4.showSheet(); } else { Agencies.hideSheet(); } } }
Awesome tool, very elegant code, every single time I learn something new from you. Many thanks! If I dare point out a minor bug: In the case you delete a sheet, it does not clear the empty row at the bottom when you get the list. So maybe (keep in mind I'm just an amateur :) before the line setting the values, do: wsManager.getRange(2,1,wsManager.getLastRow()-1,2).clearContent() Also just for looks, Col B conditional formatting =A1="" --> white text, white background
First off, this was AMAZING!!!! I Have been trying to find a video walking through how to do this for a long time before I found this video. This was EXACTLY what I was looking for. I Have a question and wondering if I could get your input. How do I change the code if instead of using checkboxes to indicate which ones I want to show or hide, I want to use an if-then formula. I followed your steps exactly and make it with the checkboxes. But when I switched the checkboxes for if/then formulas the returned the words "true" for the ones I want to show, and "false" for the ones to hide, all of the sheets get hidden that have that formula in the box regardless of it it turned out true or false. I have ZERO coding background. I am just a financial planner making a google sheets worksheet with a bunch of tools for clients, so any insights into how to do this would be amazing!!!
great tutorial, thank you! one suggestion... to NOT show the "Sheet Manager" sheet in the list, so people do not have option to hide that particular sheet.
I followed almost every Script tutorials, thank you very much, Sir. That's a huge improvement during these two months. I encountered the problem of lock apps script with password, if I share to others (also as a editor) with partially protection the sheets, others user easily to login my apps script. How to solve it? Thanks.
Great ! thanks Is it possible from within Gsheet , to search the Corporate Gmail directory, sama way as I do when I compose an email from gmail ? search for people by just start typing their names.
How about an automatic un/hide where the master list tab of a sheet followed by many more & increasing tabs has a column titled "status" with a drop-down-list with the word "closed"? Would that un/hide a tab faster that scripting? Thank you PS.: II understand my question leads to the video instructions so I would assume.
You could probably do that in an onEdit() function. But that would mean the script would be checking the Sheet every time there's an edit to ensure it's the right tab and range. I'm not sure it would be a good use of script resources.
Sir, i had already make it that app in menu, which create Delete sidebar App, and glad its normally function. My question is? how if that script G"oogle Sheets - Hide / Unhide Multiple Worksheets - Bulk Script" could apply in same thing like Metroui Sidebar App, and appear one button Hide to do same thing like Hide-Unhide did, i am did trying but yet failed..can you help me. thanks very much Hery in Indonesia
@@ExcelGoogleSheets thanks vet much, for attention n reply, firstable about the script is : 1. Hide/Unhide Button
Un/Hide -> why this button couldn't join in 1 row, and is palcing in up n bottom 2. Instruction for Hide (in html) WorksheetHideApp.hideSheets = function(){ WorksheetHideApp.wait.classList.remove("no-visible") const sheetNamesToHide = WorksheetHideApp.worksheetsTable.getSelectedItems().map(r => r[0]) const sheetNamesToHideAsString = JSON.stringify(sheetNamesToHide) google.script.run.withSuccessHandler(() => { WorksheetHideApp.loadWorksheetNames() }).hideWorksheets(sheetNamesToHideAsString) } // hideSheets document.getElementById("hideButton").addEventListener("click",WorksheetHideApp.openDialogActions) 3. Instruction for Hide (in gas) function hideWorksheets(sheetNamesToHideAsString){ const sheetNamesToHide = JSON.parse(sheetNamesToHideAsString) const ss = SpreadsheetApp.getActiveSpreadsheet() const sheets = ss.getSheets() const sheetsToHide = sheets.filter(sheet => sheetNamesToHide.includes(sheet.getSheetName())) sheetsToHide.forEach(sheet =>{ ss.hideSheet(sheet) }) Pardon me, if those script was just edited by copying from what you've did it in video. Hereby, i'm just try to adding some script to use it in Hide Worksheet, but in Metroui version in that Sidebar App.
@@ExcelGoogleSheets pardon me, was that script in 1, 2 ,3 was totally agree can use it in function of Metroui and put in the Delete Sidebar App? hope for some correction, if any wrong in of all script. cause it had been try , but it can't. thanks so much for reply. #mean; if "sheet.hideSheet()" repalce in what text ?
I still do not understand why the hell until today there is no single option - hide range, sheet visualy from other users, but let me edit it. Just hide something other users do not even know exists. This is so stupid attitude. Standard functionality in other group work apps. Google shoudl be ashamed of it.
Google sheets is sadly lacking in a lot of areas, and can be incredibly slow as well. It's painfully obvious that they should have an option to unhide all hidden sheets, or at least have a dialog that comes up with a list of sheets where you can hide or unhide and select all you want included all at once, rather than doing them individually. I wouldn't use Sheets if I had an alternative (I have to use it because of the people I'm working for and their environment).
Soooo nice. Another great content. After 6+ month watching you and getting thrilled by your app-script tutorials it's nice to see a progress. Nice to understand everything you teach us. Thank you, Sir
Not sure I would need this script to hide or unhide sheets but some parts of the script can be really useful for another script of mine. 😏😉
Always learning something in this channel. Keep on the good work! 👍
Thanks yet again for another great video. I accomplished the same thing, but in a slightly different way. Rather than hiding/showing multiple sheets at once, I tied the script to the individual checkboxes. So, when you check a box, it shows that sheet, and when you uncheck it, it hides it. Here's the script:
function onEdit(a) {
var range = a.range
if (range.getA1Notation() == "B9") {
var Sheet1 = a.source.getSheetByName("Sheet1");
if (range.isChecked()) {
Sheet1.showSheet();
} else {
Sheet1.hideSheet();
}
}
if (range.getA1Notation() == "B10") {
var Sheet2 = a.source.getSheetByName("Sheet2");
if (range.isChecked()) {
Sheet2.showSheet();
} else {
Sheet2.hideSheet();
}
}
if (range.getA1Notation() == "B11") {
var Sheet3 = a.source.getSheetByName("Sheet3");
if (range.isChecked()) {
Sheet3.showSheet();
} else {
Sheet3.hideSheet();
}
}
if (range.getA1Notation() == "B12") {
var Sheet4 = a.source.getSheetByName("Sheet4");
if (range.isChecked()) {
Sheet4.showSheet();
} else {
Agencies.hideSheet();
}
}
}
This is so AWESOME!! I did not even know I needed this for my application that I am building!! Holy cow, you rock man!!
Awesome tool, very elegant code, every single time I learn something new from you.
Many thanks!
If I dare point out a minor bug:
In the case you delete a sheet, it does not clear the empty row at the bottom when you get the list.
So maybe (keep in mind I'm just an amateur :) before the line setting the values, do:
wsManager.getRange(2,1,wsManager.getLastRow()-1,2).clearContent()
Also just for looks, Col B conditional formatting =A1="" --> white text, white background
Great point!
Thanks a a lot for your elegant code
Managing sheets is definitely a common issue for many people ( I feel it)
First off, this was AMAZING!!!! I Have been trying to find a video walking through how to do this for a long time before I found this video. This was EXACTLY what I was looking for.
I Have a question and wondering if I could get your input. How do I change the code if instead of using checkboxes to indicate which ones I want to show or hide, I want to use an if-then formula. I followed your steps exactly and make it with the checkboxes. But when I switched the checkboxes for if/then formulas the returned the words "true" for the ones I want to show, and "false" for the ones to hide, all of the sheets get hidden that have that formula in the box regardless of it it turned out true or false.
I have ZERO coding background. I am just a financial planner making a google sheets worksheet with a bunch of tools for clients, so any insights into how to do this would be amazing!!!
muchas gracias. es la continuación de delete sheets, God bless your job!
Thank You!
You're the man! I was just thinking something like this would be nice to have.
Nice! By any chance, do you know how to create script to hide/unhide column/row and bulk printing? And can I prevent people from unhide hidden sheets?
Thank you, it really helped me so much on my payroll worksheet
Glad it helped!
Very useful!!! Thanks!!!
Now I need to learn how to run the script from another spreadsheet, if it is actually possible...
A good way to catch up with the functionality of MS Excel.
I always wanted the Google Sheets look like Excel in this query. We can literally make Excel look like a website, and that's beautiful.
great tutorial, thank you!
one suggestion... to NOT show the "Sheet Manager" sheet in the list, so people do not have option to hide that particular sheet.
//console.log(sheet)
return [sheet.getSheetName()]
})
sheetNames.shift()
Love this! Thank you for the awesome videos you make!!!
Always the best! Thank you!
Thank you!
I followed almost every Script tutorials, thank you very much, Sir. That's a huge improvement during these two months. I encountered the problem of lock apps script with password, if I share to others (also as a editor) with partially protection the sheets, others user easily to login my apps script. How to solve it? Thanks.
For the algorithm :))) thank you for your content
Very nice, i tried it. Its just a bit slow when you have more sheets
fantastic, thanks
Bravo 👏 👏 👏 👏 👏 👏 👏 👏
Great ! thanks
Is it possible from within Gsheet , to search the Corporate Gmail directory, sama way as I do when I compose an email from gmail ? search for people by just start typing their names.
How about an automatic un/hide where the master list tab of a sheet followed by many more & increasing tabs has a column titled "status" with a drop-down-list with the word "closed"? Would that un/hide a tab faster that scripting? Thank you PS.: II understand my question leads to the video instructions so I would assume.
Woah! The assign script to an image totally broke me!
Can you do one showing how to hide/unhide columns in bulk
Great
How can we delete or pervent people when work togather or when share to add sheets like this
Is there a copy of the of the formula?
Hi, very nice video!
Where can i find the code? Thank you
Hi I have a question - How to hide sheet for specific user after sharing google sheet with multiple user.
You best bet is to protect that sheet and then hide. That way they would not be able to unhide.
@@ExcelGoogleSheets Thanks for responding will try .
How to hide in "all page option" ?
can you make it run on edit, i mean it hides the sheet when you press the check box
You could probably do that in an onEdit() function. But that would mean the script would be checking the Sheet every time there's an edit to ensure it's the right tab and range. I'm not sure it would be a good use of script resources.
Why whoud you put i window right where you er trying to show something????
How to hide formula, app script when iam sharing sheet with others
How to add Font in Google sheet like- Kurti Dev 10
Great Script but it seems not to work in protected sheets, also with the rights for this user
Sir, i had already make it that app in menu, which create Delete sidebar App, and glad its normally function. My question is? how if that script G"oogle Sheets - Hide / Unhide Multiple Worksheets - Bulk Script" could apply in same thing like Metroui Sidebar App, and appear one button Hide to do same thing like Hide-Unhide did, i am did trying but yet failed..can you help me. thanks very much
Hery in Indonesia
Yes, what have you tried? Share your script.
@@ExcelGoogleSheets thanks vet much, for attention n reply, firstable about the script is :
1. Hide/Unhide Button
Un/Hide
-> why this button couldn't join in 1 row, and is palcing in up n bottom
2. Instruction for Hide (in html)
WorksheetHideApp.hideSheets = function(){
WorksheetHideApp.wait.classList.remove("no-visible")
const sheetNamesToHide = WorksheetHideApp.worksheetsTable.getSelectedItems().map(r => r[0])
const sheetNamesToHideAsString = JSON.stringify(sheetNamesToHide)
google.script.run.withSuccessHandler(() => {
WorksheetHideApp.loadWorksheetNames()
}).hideWorksheets(sheetNamesToHideAsString)
} // hideSheets
document.getElementById("hideButton").addEventListener("click",WorksheetHideApp.openDialogActions)
3. Instruction for Hide (in gas)
function hideWorksheets(sheetNamesToHideAsString){
const sheetNamesToHide = JSON.parse(sheetNamesToHideAsString)
const ss = SpreadsheetApp.getActiveSpreadsheet()
const sheets = ss.getSheets()
const sheetsToHide = sheets.filter(sheet => sheetNamesToHide.includes(sheet.getSheetName()))
sheetsToHide.forEach(sheet =>{
ss.hideSheet(sheet)
})
Pardon me, if those script was just edited by copying from what you've did it in video. Hereby, i'm just try to adding some script to use it in Hide Worksheet, but in Metroui version in that Sidebar App.
@@datahrd346 should be
sheet.hideSheet()
instead of
ss.hideSheet(sheet)
@@ExcelGoogleSheets pardon me, was that script in 1, 2 ,3 was totally agree can use it in function of Metroui and put in the Delete Sidebar App?
hope for some correction, if any wrong in of all script. cause it had been try , but it can't. thanks so much for reply. #mean; if "sheet.hideSheet()" repalce in what text ?
replace
ss.hideSheet(sheet)
with
sheet.hideSheet()
i am working on making ludo in gsheets...
u wanna join?
I still do not understand why the hell until today there is no single option - hide range, sheet visualy from other users, but let me edit it. Just hide something other users do not even know exists. This is so stupid attitude. Standard functionality in other group work apps. Google shoudl be ashamed of it.
Google sheets is sadly lacking in a lot of areas, and can be incredibly slow as well. It's painfully obvious that they should have an option to unhide all hidden sheets, or at least have a dialog that comes up with a list of sheets where you can hide or unhide and select all you want included all at once, rather than doing them individually. I wouldn't use Sheets if I had an alternative (I have to use it because of the people I'm working for and their environment).