How hide columns based on cell value-CodePudding

I am looking for a solution to hide display specefic columns based on cell value.

For example: When B2=Démission, then it shows only columns: J to O AND AE to AK

When B2=Retraite, then it shows only columns: I AND P to W

When B2=Fin de mandat, then it shows only columns: H AND X to AD

CodePudding user response：

I believe your current situation and your goal are as follows.

You have a sheet in a Google Spreadsheet.
From the following sample situation,

When B2=Démission, then it shows only columns: J to O AND AE to AK

When B2=Retraite, then it shows only columns: I AND P to W

When B2=Fin de mandat, then it shows only columns: H AND X to AD
You want to achieve these situations using Google Apps Script.

In this case, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet. And, please set the value to the cell "B2". And, run the script.

function myFunction() {
  const sheetName = "Sheet1"; // Please set your sheet name.
  
  // This condition is from your question.
  const obj = {
    "Démission": [{ "from": "J", "to": "O" }, { "from": "AE", "to": "AK" }],
    "Retraite": [{ "from": "I", "to": "I" }, { "from": "P", "to": "W" }],
    "Fin de mandat": [{ "from": "H", "to": "H" }, { "from": "X", "to": "AD" }],
  };

  const columnLetterToIndex_ = letter => [...letter.toUpperCase()].reduce((c, e, i, a) => (c  = (e.charCodeAt(0) - 64) * Math.pow(26, a.length - i - 1)), -1);
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const value = sheet.getRange("B2").getValue();
  if (!obj[value]) return;
  sheet.showColumns(1, 1);
  sheet.hideColumns(2, sheet.getMaxColumns() - 1);
  obj[value].forEach(({ from, to }) => {
    const start = columnLetterToIndex_(from)   1;
    const end = columnLetterToIndex_(to)   1;
    sheet.showColumns(start, from == to ? 1 : end - start   1);
    if (start != 1) sheet.hideColumns(1);
  });
}

When this sample script is run, for example, when the value of cell "B2" is Démission, the columns from "J" to "O" and from "AE" to "AK" are shown, and all other columns are hidden.
When you change the condition, please modify obj.

Note:

In this sample, the script can be run by the script editor, custom menu, a button and so on. But, if you want to automatically run this by a trigger, as a simple modification, please modify myFunction to onEdit. By this, the script is run by editing a cell. If you want to limit the edit cell, please add the condition to the above script.

Sample script:

Note:

References: