I am attempting to create a script for a dropdown selection in cell B2, that when selected for one, will hide columns corresponding two the other choices in the list sheet.
The dropdown in cell B2 is has as its data validation criteria the following list: "NORMAL," "HARD," "MAX POINTS"
And the following column ranges in the sheet correspond to the selections in parentheses: Columns D-K ("NORMAL"), Columns L-S ("HARD"), Columns T-AA ("MAX POINTS")
I would like the script to work such that selection of one of the dropdown choices will hide the column ranges that correspond to the two other dropdown choices (i.e., if you select "HARD" it will hide Columns D-K as well as T-AA).
What am I doing wrong here? I'm sure quite a bit of course. link
MODIFIED SCRIPT: I got this to work properly for each when run separately, but it requires me to to unhide after each time, otherwise it compounds what is hidden. I have it set to trigger on edit. It's so close, is there something about the trigger or perhaps I need to somehow add something that resets it to unhide all before I can change the selection? (not sure how though)
var ss=SpreadsheetApp.getActive();
var value1 = "NORMAL";
var value2 = "HARD";
var value3 = "MAX POINTS";
var activeSheet = ss.getActiveSheet();
var cell = activeSheet.getRange("B2").getValue();
function HideColumn() {
if(cell == value1) {
activeSheet.hideColumns(12, (27-7+1));
}
else if(cell == value2) {
activeSheet.hideColumns(4, (14-7+1));
activeSheet.hideColumns(21, (14-7+1));
}
else if(cell == value3) {
activeSheet.hideColumns(4, (22-7+1));
}
}
You have a dropdown in Cell B2.
The script uses the switch statement Doc ref as an alternative to an IF statement.
To run this answer:
onEdit() trigger. (enables the script to run when the dropdown cell is edited; and enables Event objects to be used)// running as an installable onEdit() trigger
// watching cell B2
function showHideColumns(e){
var sheetName = "Today's Matchups"
// Logger.log(JSON.stringify(e)) // DEBUG
if (e.range.columnStart ==2 && e.range.rowStart ==2 && e.range.getSheet().getName() == sheetName){
// correct sheet and correct cell
// Logger.log("DEBUG: correct sheet and correct cell")
}
else{
// not the correct sheet/cell
// Logger.log("DEBUG: not the correct sheet/cell")
return
}
var ss=SpreadsheetApp.getActiveSpreadsheet()
var sheet = ss.getSheetByName(sheetName)
var value1 = "NORMAL";
var value2 = "HARD";
var value3 = "MAX POINTS";
var value1ColStart = 4
var value2ColStart = 12
var value3ColStart = 20
var valueSetNumCols = 8
// Columns D-K ("NORMAL"),
// Columns L-S ("HARD"),
// Columns T-AA ("MAX POINTS")
var cell = e.value
// Logger.log("DEBUG: dropdown value = "+cell)
switch (cell) {
case value1:
// NORMAL
// show NORMAL, hide Hard & Max Points
// show all columns (including NORMAL)
sheet.showColumns(value1ColStart,24)
// hide Hard
sheet.hideColumns(value2ColStart, valueSetNumCols)
// hide MaxPoints
sheet. hideColumns(value3ColStart, valueSetNumCols)
break
case value2:
// HARD
// show HARD, hide Normal & Max Points
// show all Columns
sheet.showColumns(value1ColStart,24)
// hide Normal
sheet.hideColumns(value1ColStart, valueSetNumCols)
// hide MaxPoints
sheet. hideColumns(value3ColStart, valueSetNumCols)
break
default:
// Max Points
// show Max Points, hide Normal & Hard
// show all Columns
sheet.showColumns(value1ColStart,24)
// hide Normal
sheet.hideColumns(value1ColStart, valueSetNumCols)
// hide Hard
sheet. hideColumns(value2ColStart, valueSetNumCols)
}
}
UPDATE: ALSO SHOW/HIDE COLUMNS ON SHEET="Battle Results Output"
// runnings as an installable onEdit() trigger
// watching cell B2
// also hide columns on "Battle Results Output"
function showHideColumns(e){
var sheetName = "Today's Matchups"
// Logger.log(JSON.stringify(e)) // DEBUG
if (e.range.columnStart ==2 && e.range.rowStart ==2 && e.range.getSheet().getName() == sheetName){
// right sheet and right cell
// Logger.log("DEBUG: right sheet and right cell")
}
else{
// not the right sheet/cell
// Logger.log("DEBUG: not the right sheet/cell")
return
}
var ss=SpreadsheetApp.getActiveSpreadsheet()
var sheet = ss.getSheetByName(sheetName)
var value1 = "NORMAL";
var value2 = "HARD";
var value3 = "MAX POINTS";
var value1ColStart = 4
var value2ColStart = 12
var value3ColStart = 20
var valueSetNumCols = 8
// Columns D-K ("NORMAL"),
// Columns L-S ("HARD"),
// Columns T-AA ("MAX POINTS")
/*
* HIDE columns on Battle Results Output
*/
var battleResultsName = "Battle Results Output"
var battleSheet = ss.getSheetByName(battleResultsName)
// Value 1 = show D&E, Hide F,G&H
var value1BattleColStart = 4 // Column D
var value1BattleSetNumCols = 2
// Value 2 = show F&G, Hide D,E&H
var value2BattleColStart = 6 // Column F & G
var value2BattleSetNumCols = 2
// Value 3 = show H, Hide D&F
var value3BattleColStart = 8 // Column D
var value3BattleSetNumCols = 1
// number of columns for all results
var valueBattleShowAllCols = value1BattleSetNumCols+value2BattleSetNumCols+value3BattleSetNumCols
var cell = e.value
// Logger.log("DEBUG: dropdown value = "+cell)
switch (cell) {
case value1:
// NORMAL
// show NORMAL, hide Hard & Max Points
// show Normal
sheet.showColumns(value1ColStart,24)
// hide Hard
sheet.hideColumns(value2ColStart, valueSetNumCols)
// hide MaxPoints
sheet.hideColumns(value3ColStart, valueSetNumCols)
/*
* HIDE columns from Battle Results
*/
// show all including Normal
battleSheet.showColumns(value1BattleColStart,valueBattleShowAllCols)
// hide Hard
battleSheet.hideColumns(value2BattleColStart,value2BattleSetNumCols)
// hide MaxPoints
battleSheet.hideColumns(value3BattleColStart,value3BattleSetNumCols)
break
case value2:
// HARD
// show HARD, hide Normal & Max Points
// show Normal
sheet.showColumns(value1ColStart,24)
// hide Normal
sheet.hideColumns(value1ColStart, valueSetNumCols)
// hide MaxPoints
sheet.hideColumns(value3ColStart, valueSetNumCols)
/*
* HIDE columns from Battle Results
*/
// show all including Normal
battleSheet.showColumns(value1BattleColStart,valueBattleShowAllCols)
// hide Normal
battleSheet.hideColumns(value1BattleColStart,value1BattleSetNumCols)
// hide MaxPoints
battleSheet.hideColumns(value3BattleColStart,value3BattleSetNumCols)
break
default:
// Max Points
// show Max Points, hide Normal & Hard
// show Normal
sheet.showColumns(value1ColStart,24)
// hide Normal
sheet.hideColumns(value1ColStart, valueSetNumCols)
// hide Hard
sheet. hideColumns(value2ColStart, valueSetNumCols)
/*
* HIDE columns from Battle Results
*/
// show all including Normal
battleSheet.showColumns(value1BattleColStart,valueBattleShowAllCols)
// hide Normal
battleSheet.hideColumns(value1BattleColStart,value1BattleSetNumCols)
// hide Hard
battleSheet.hideColumns(value2BattleColStart,value2BattleSetNumCols)
}
// Logger.log("DEBUG: Selection: "+cell+" the end")
}