Google App Script - How to hide columns across muliple sheets?

Damien Published at Dev

Damien

I created a custom menu in Google sheets that can hides / unhides columns depending on which metrics the person is interested to view. It's working great but it only works on "active sheet". I would like the script to hide certain columns in a sheet and hide other columns in another sheet.

The custom menu should contain 3 items: 'Karen view', 'Patrick View' and 'View all'

Karen view:

Hide columns 1 and 3 in sheet 1
Hide columns 4 and 5 in sheet 2

Patrick view:

Hide columns 1, 2 and 4 in sheet 1
Hide columns 4 and 5 in sheet 2

View all:

Unhide all columns across all the sheets

Below the script I'm currently using: (I'm a bit confused on how to use getactivespreadsheet along with getactivesheet, that's where I'm stuck)

var sh = SpreadsheetApp.getActiveSheet()
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Hide metrics')
    .addItem('Patrick View', 'hideColumnsP')
    .addItem('Karen View', 'hideColumnsK')
    .addItem('View all', 'showColumns')
    .addToUi();
}
function showColumns() {
  sh.unhideColumn(sh.getRange(1, 1, 1, sh.getLastColumn()))
}
function hideColumnsK(){
  showColumns()
  sh.hideColumns(1)
  sh.hideColumns(3)
}
function hideColumnsP(){
  showColumns()
  sh.hideColumns(1,2)
  sh.hideColumns(4)
}

Thanks in advance for your help

Tanaike

In your situation, how about the following modified script?

Modified script:

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Hide metrics')
    .addItem('Patrick View', 'hideColumnsP')
    .addItem('Karen View', 'hideColumnsK')
    .addItem('View all', 'showColumns')
    .addToUi();
}

function showColumns() {
  const sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  showColumnsInAllSheets_(sheets);
}

function hideColumnsK() {
  const obj = [{ sheetName: "Sheet1", hide: [1, 3] }, { sheetName: "Sheet2", hide: [4, 5] }];
  sample_(obj);
}

function hideColumnsP() {
  const obj = [{ sheetName: "Sheet1", hide: [1, 2, 4] }, { sheetName: "Sheet2", hide: [4, 5] }];
  sample_(obj);
}

function sample_(obj) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  showColumnsInAllSheets_(sheets);
  const sheetObj = sheets.reduce((o, s) => (o[s.getSheetName()] = s, o), {});
  obj.forEach(({ sheetName, hide }) => {
    if (sheetObj[sheetName]) {
      hide.forEach(h => sheetObj[sheetName].hideColumns(h, 1));
    }
  });
}

function showColumnsInAllSheets_(sheets) {
  sheets.forEach(s => s.showColumns(1, s.getMaxColumns()));
}

In this modification, the specific columns of the specific sheet can be hidden by giving an object for the sheet and column information.
And also, in this modification, when showColumns() is run, all columns of all sheets are shown.
If you want to change the sheet names and columns, please modify obj in the functions hideColumnsK and hideColumnsP.