How Can I Run My Hide/show Columns Script On Multiple Ranges Of Columns In One Google Sheets Spreadsheet?
Solution 1:
This should do it for you for a while. All you have to do is type in all the column numbers that you want to hide and show. And it's helpful to run the onOpen function in the script editor so that you can authorize it.
The intermediate function showMyCols and hideMyCols just make it possible to write one function to do the showing and hiding and they keep you from having to worry about how to setup the parameters.
functiononOpen(){
SpreadsheetApp.getUi().createMenu('MyTools')
.addItem('Show', 'showMyCols')
.addItem('Hide', 'hideMyCols')
.addToUi();
}
functionshowMyCols(){
showhideCols('3,4,5,6,7,8,9,10',true);
}
functionhideMyCols(){
showhideCols('3,4,5,6,7,8,9,10',false);
}
functionshowhideCols(colscsv,show){
var ss=SpreadsheetApp.getActive();
var sh=ss.getActiveSheet();
if(!colscsv){
var resp=SpreadsheetApp.getUi().prompt('Enter Column Numbers separated by commas.', SpreadsheetApp.getUi().Button.OK);
var cA=resp.getResponseText().split(',');
}else{
var cA=colscsv.split(',');
}
for(var i=0;i<cA.length;i++){
if(show){
sh.showColumns(cA[i]);
}else{
sh.hideColumns(cA[i]);
}
}
}
Solution 2:
If you don't want to create a custom menu option for each month and have a function to show and another to hide columns for each month you have to use parameters and figure a way to set and to get those parameters and send them to your functions.
One way is to use a cell in a spreadsheet to set the month. Another way is to use a dialog / sidebar to do that. Will be opt to you if you will use a plain text input field or if you use a dropdown, radio button, etc.
Solution 3:
Thanks for the help I really appreciate it, I ended up fudging the code posted by Cooper. I know this is messy but it works great for what I need. Thanks again!
functiononOpen(){
SpreadsheetApp.getUi().createMenu('MyTools')
.addItem('HideJanuary', 'hideCols1')
.addItem('HideFebruary', 'hideCols2')
.addItem('HideMarch', 'hideCols3')
.addToUi();
}
functionhideCols1(){
showhideCols('3,4,5,6,7,8,9,10,11,12,13,14,15
,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33',false);
}
functionhideCols2(){
showhideCols('35,36,37,38,39,40,41,42,43,44,45,
46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62',false);
}
functionhideCols3(){
showhideCols('64,65,66,67,68,69,70,71,72,73,74,75,
76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94',false);
}
functionshowhideCols(colscsv,show){
var ss=SpreadsheetApp.getActive();
var sh=ss.getActiveSheet();
if(!colscsv){
var resp=SpreadsheetApp.getUi().prompt('Enter Column Numbers separated by
commas.', SpreadsheetApp.getUi().Button.OK);
var cA=resp.getResponseText().split(',');
}else{
var cA=colscsv.split(',');
}
for(var i=0;i<cA.length;i++){
if(show){
sh.showColumns(cA[i]);
}else{
sh.hideColumns(cA[i]);
}
}
}
Post a Comment for "How Can I Run My Hide/show Columns Script On Multiple Ranges Of Columns In One Google Sheets Spreadsheet?"