0

I've figured out how to create a custom menu, but I'd like to be able to hide or show certain columns (not necessarily ranges), based on what menu item is selected. Not sure on the next steps / can't make it work.

function onOpen() {`
var ui = SpreadsheetApp.getUi()
    .createMenu('Custom Menu Views')
    .addItem('Financial', 'financial')
    .addItem('Schedule', 'schedule')
    .addToUi();
}

marked as duplicate by ale, Sathyajith Bhat Aug 24 '15 at 4:08

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 1
    Are you the same user who asked this question? If you plan on using this site in the future, you should register an account, so that you don't keep losing access to older posts when your browser wipes the cookies. – user79865 Aug 23 '15 at 18:26
  • 1
    @Normal: Based on the Gravatar, yes, it's the same person. – ale Aug 23 '15 at 18:49
2

Firstly, you'd have to remove the ` from the end of first line, that may have caused some errors.

Secondly, here's the sheet and code (also to be found below). Since detecting whether a column/row is hidden or not has not yet been implemented, I have made a little workaround for you. When hiding given column(s), it adds "(hidden)" to their header and that allows the script to later determine whether the column(s) are hidden and show them or not.

Should you need to change columns of the sections, you can easily customize that by editing values in the code where I have added //comments.

EDIT: Code and further description

Let's assume a scenario in which you have two columns that are to be hidden with "Financial" button and one column to be hidden with "Schedule". Because of the workaround involving adding "(hidden)" to hidden columns' headers, you will need to have cells with names for both the sections: "Financial" and "Schedule".

The sheet data in the abovementioned scenario would look like this: Google Sheet screenshot

Code:

function onOpen() {
var ui = SpreadsheetApp.getUi()
    .createMenu('Custom Menu Views')
    .addItem('Financial', 'financial')
    .addItem('Schedule', 'schedule')
    .addToUi();
}


var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(),
    sheet = spreadsheet.getActiveSheet(),
    financial_header = sheet.getRange(1,1),  // change the row,column coordinates of the financial columns header
    schedule_header = sheet.getRange(1,3);   // change the row,column coordinates of the schedule columns header


function financial(){
  if(financial_header.getValue() == "Financial (hidden)"){
    sheet.showColumns(1,2);  // change the financial columns
    financial_header.setValue("Financial");
  } else {
    sheet.hideColumns(1,2);  // here, too
    financial_header.setValue("Financial (hidden)");
  }
}

function schedule(){
  if(schedule_header.getValue() == "Schedule (hidden)"){
    sheet.showColumns(3);  // change the schedule columns
    schedule_header.setValue("Schedule");
  } else {
    sheet.hideColumns(3);  // here, too
    schedule_header.setValue("Schedule (hidden)");
  }
}
  • It would be better if the code for your solution were actually here. When that document is no longer available, this answer is virtually useless. We like answers that help the asker, but more importantly we want answers that will help future readers with the same problem. – ale Aug 22 '15 at 12:05
  • That is true, I have not thought of that. Thank you for reminding me, I have just added the script's code and an additional screenshot. – Grzegorz G. Aug 22 '15 at 12:20

Not the answer you're looking for? Browse other questions tagged or ask your own question.