CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Brian
Brian

Reputation: 11

How to hide Multiple columns in Google Sheet with Multiple checkboxes

I hope I can explain this well as I have absolutely no coding background. Luckily, I found a script on here that will hide a column, based on the status of a checkbox.

The code is:

    function onEdit(e) {
  if (e.range.getA1Notation() != "B5") return;

  if (e.value == "TRUE") {
    SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().hideColumns(8,4);
  }
  else if (e.value == "FALSE") {
    SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().showColumns(8,4);
  }
}

This will hide columns H through K, if B5 is checked.

My issue is that I have different checkboxes that need to hide different columns, but I can only get 1 script to work at at time.

Here's what I need:

When B5 is checked, I need Columns H, I, J, and K hidden.

When B6 is checked, I need Columns L, M, N, and O hidden.

When B7 is checked, I need Columns P, Q, R, and S hidden.

And so on... If multiple cells are checked, then multiple ranges, listed above, need to be hidden.

Since I have no clue what I am doing, I figured I would just make a new script for each scenario. I realize that there is probably an easier way, but this works for me if needed.

So, if I have one script saved as

 function onEdit(e) {
  if (e.range.getA1Notation() != "B5") return;

  if (e.value == "TRUE") {
    SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().hideColumns(8,4);
  }
  else if (e.value == "FALSE") {
    SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().showColumns(8,4);
  }
}

and another saved as

 function onEdit(e) {
  if (e.range.getA1Notation() != "B6") return;

  if (e.value == "TRUE") {
    SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().hideColumns(12,4);
  }
  else if (e.value == "FALSE") {
    SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().showColumns(12,4);
  }
}

Only the second script works. Likewise, if I add a third script, then only the 3rd one works and the first two don't.

How can I make each one work independently of each other?

Upvotes: 1

Views: 182

Answers (1)

TheWizEd
TheWizEd

Reputation: 8606

You can include multiple cells to check in you onEdit(e)

function onEdit(e) {
  let col = 0;
  switch(e.range.getA1Notation()) {
    case "B5":
      col = 8;
      break;
    case "B6":
      col = 12;
      break;
    case "B7":
      col = 16;
      break;
    default:
      return;
  }
  if( e.value === "TRUE" ) {
    e.source.getActiveSheet().hideColumns(col,4)
  }
  else {
    e.source.getActiveSheet().showColumns(col,4)
  }
}

Upvotes: 1

Related Questions