Google-sheets – Hide/Show Multiple Groups of Columns in Google Sheets

google sheetsgoogle-apps-script

I found a script (on this post: I need to hide/show a group of columns in Google Sheets in a simple way ) that adds a dropdown menu with two options: Show Range X of Columns & Hide Range X of Columns. The script works perfectly, except that I am unable to add more menu options for different column ranges.

I have tried modifying the code (see below). Currently, the "Show/Hide Columns" dropdown menu shows two options: "Show UTH" & "Hide UTH", but the "Show Viz" & "Hide Viz" options are being ignored. Any suggestions on how to modify the code so I can to add multiple show & hide buttons to the menu that affect different column ranges? I'm very new to Google Scripts, so I'm hoping there's something very simple that I'm overlooking.

function onOpen(Viz) {
    var menu = [{name: "Show Viz", functionName: "showColumns"}, {name: "Hide Viz", functionName: "hideColumns"}]
    SpreadsheetApp.getActiveSpreadsheet().addMenu("Show/Hide Columns", menu);
}

function showColumns(Viz) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    sheet.showColumns(4);  
    sheet.showColumns(12);  
    sheet.showColumns(20); 
}

function hideColumns(Viz) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    sheet.hideColumns(4);  
    sheet.hideColumns(12);  
    sheet.hideColumns(20); 
}


function onOpen(UTH) {
    var menu = [{name: "Show UTH", functionName: "showColumns"}, {name: "Hide UTH", functionName: "hideColumns"}]
    SpreadsheetApp.getActiveSpreadsheet().addMenu("Show/Hide Columns", menu);
}

function showColumns(UTH) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    sheet.showColumns(5);  
    sheet.showColumns(13);  
    sheet.showColumns(21); 
}

function hideColumns(UTH) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    sheet.hideColumns(5);  
    sheet.hideColumns(13);  
    sheet.hideColumns(21); 
}

Best Answer

You should never have two functions with the same name. It's the same ambiguity as when there are two people called David nearby, and you say "David, come here". JavaScript resolves this ambiguity by always calling the function that appears later in your program; in effect it overwrites the former.

Note that the name of function parameters (Viz, UTH, etc) is internal business of a function; the outside world doesn't care how the function names its parameters or any other local variable. This does not distinguish one function from another. That is, 

function addone(x) {
  return 2*x;
}

is identical to 

function addone(y) {
  return 2*y;
}

In either case, you get back twice the number you put it; that's all that matters.

Back to your script: you have a bunch of functions with identical names. You have to given them different names, like this 

function showColumnsViz() {
  ...
}

function showColumnsUTH() {
  ...
}

and so on.

Also, you have two functions with the name onOpen. There should be one: 

function onOpen(Viz) {
  var menu = [{name: "Show Viz", functionName: "showColumnsViz"}, {name: "Hide Viz", functionName: "hideColumnsViz"}, {name: "Show UTH", functionName: "showColumnsUTH"}, {name: "Hide UTH", functionName: "hideColumnsUTH"}];
  SpreadsheetApp.getActiveSpreadsheet().addMenu("Show/Hide Columns", menu);
}
Related Topic