8

I need to hide and show in a easy way a group of columns in Google Sheets. In Excel I used a Macro with a button, but in Sheets I cannot put a button.

How can I add buttons with custom functions in Google Sheets?

5

It is possible to assign a function to a button in Google Spreadsheets, but in practice it's easier to add commands to the spreadsheet menu. This way you do not have to look for them in the spreadsheet, and they do not get in the way of your data.

Suppose you want custom commands "show/hide columns B-D, G". Create and save the following script in (Tools->Script Editor):

function onOpen() {
  var menu = [{name: "Show B-D, G", functionName: "showColumns"}, {name: "Hide B-D, G", functionName: "hideColumns"}]
  SpreadsheetApp.getActiveSpreadsheet().addMenu("Custom", menu);
}

function showColumns() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  sheet.showColumns(2,3);   // B-D, three columns starting from 2nd 
  sheet.showColumns(7);      //  G, column number 7
}

function hideColumns() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  sheet.hideColumns(2,3);  // B-D, three columns starting from 2nd
  sheet.hideColumns(7);     // G, column 7
}

Next time you open this spreadsheet, it will have "Custom" menu option with two commands, for showing and hiding this range of columns.


If you still want to trigger scripts by an embedded element in the sheet, see this illustrated example.

| improve this answer | |
5

This is now a built-in feature in Google Sheets:

  • Select the rows or columns you want to group or ungroup.
  • At the top, click Data and choose what you want to do: Group rows, Group columns, Ungroup rows, Ungroup columns
  • To expand or collapse grouped rows or columns, click the Plus (+) or Minus (-) sign next to the rows or columns.
| improve this answer | |
  • This is awesome, and it is even possible to create nested groupings. – Michael Jul 12 '18 at 18:44

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