I created a custom menu in App Scripts with 3 sub-menus:

1. hide a specific set of rows
2. hide another set of rows
3. unhide everything

Basically, people can click on any of them for better visibility. The issue with my current script is that if I put a filter, the rows won't be matching the ones I want to hide...

Is there any ways to hide rows based on the value of one of the cell instead of row number? So that no matter what data we are filtering, the needed rows will remain hidden.

Below the part of the code I believe I need to change:

function hideRowsC() {
  const obj = [{ sheetName: "Keyword Report", hide: [3,7,20,25,37,43,49,53,57,60,68,77 ] }];
  sample_(obj);
}

My entire script below if it helps solving my issue.

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom View')
    .addItem('Keywords Only', 'hideRowsC')
    .addItem('Categories Only', 'hideRowsK')
    .addItem('View all', 'showRows')
    .addToUi();
}

function showRows() {
  const sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  showRowsInAllSheets_(sheets);
}

function hideRowsC() {
  const obj = [{ sheetName: "Keyword Report", hide: [3,7,20,25,37,43,49,53,57,60,68,77 ] }];
  sample_(obj);
}

function hideRowsK() {
  const obj = [{ sheetName: "Keyword Report", hide: [4,5,6,8,19,24,26,27,28,50,52,58,59,61,76,80,81,82 ] }];
  sample_(obj);
}

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

function showRowsInAllSheets_(sheets) {
  sheets.forEach(s => s.showRows(1, s.getMaxRows()));
}

CodePudding user response：

I believe your goal is as follows.

• From Is there any ways to hide rows based on the value of one of the cell instead of row number?, you want to hide rows by checking the value of the specific column.

In this case, how about the following modification?

Modified script:

In this modification, the functions of hideRowsC and sample_ are modified. About const obj = [{ sheetName: "Keyword Report", checkCol: 1, checkValue: "sample" }], in this sample, it is checked whether the value of column "A" is sample. And, the rows that the value of column "A" is sample are hidden.

function hideRowsC() {
  const obj = [{ sheetName: "Keyword Report", checkCol: 1, checkValue: "sample" }];
  sample_(obj);
}

function sample_(obj) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  showRowsInAllSheets_(sheets);
  const sheetObj = sheets.reduce((o, s) => (o[s.getSheetName()] = s, o), {});
  obj.forEach(({ sheetName, checkCol, checkValue }) => {
    const s = sheetObj[sheetName];
    if (s) {
      s.getRange(1, checkCol, s.getLastRow()).getDisplayValues().forEach((e, i) => {
        if (e == checkValue) s.hideRows(i   1, 1);
      });
    }
  });
}

• In your script, please modify hideRowsK as same as the above modification.