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

user_18778887 0
Viewed 110

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'

  1. Karen view:
  • Hide columns 1 and 3 in sheet 1
  • Hide columns 4 and 5 in sheet 2
  1. Patrick view:
  • Hide columns 1, 2 and 4 in sheet 1
  • Hide columns 4 and 5 in sheet 2
  1. 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

google-apps-script google-sheets javascript
1 Answers

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.

References:

user_71086530
Related