Google Sheets – How to Hide/Show a Group of Columns Easily

google sheets

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?

Best Answer

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.

Related Solutions

How to Group Data by Month in Google Sheets

Using the Data->Pivot table report... offers exactly what you are asking for. It might be possible that you have to use the "New Google Sheets".
I use that as a default setting and it was easy to achieve what you wanted, similar as in Excel.

Here in my answer I explain how to enable the new spreadsheets.

It seems I did not completly understand the problem.

First we need to have the month. For that add a new column to extract the date using =MONTH(DATE_COLUMN).

enter image description here

Then create a Pivot report:

enter image description here

Google-sheets – Google Sheets Customize View Using Create Custom Menu and Hiding/Showing Specifc Columns

Below is a rough example of how to show / hide individual columns from a Google Sheet.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
      .addItem('Show View A', 'showViewA')
      .addItem('Show View B', 'showViewB')
      .addItem('Show All', 'showAll')
      .addToUi();
}

function showViewA() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  showAll();
  sheet.hideColumn(sheet.getRange("A1"));
  sheet.hideColumn(sheet.getRange("F1"));
}

function showViewB() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  showAll();
  sheet.hideColumn(sheet.getRange("C1"));
  sheet.hideColumn(sheet.getRange("D1"));
}

function showAll() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.unhideColumn(sheet.getRange(1, 1, 1, sheet.getLastColumn()));
}

The View A menu item hides columns A and F, while View B menu item hides columns C and D. The Show All menu item does what it says on the tin. Hopefullly the example gives a starting point.

Best Answer

Related Solutions

How to Group Data by Month in Google Sheets

Google-sheets – Google Sheets Customize View Using Create Custom Menu and Hiding/Showing Specifc Columns

Related Topic