I have 8 checkboxes on a Sheet named "Platforms". Each checkbox unchecked must hide a specific group of column of the "ROAS" Sheet.

Checkboxes locations:

• B4 : checked = show columns F, G, H on 'ROAS' / Unchecked : Hide columns F, G, H
• B5 : checked = show columns I, J, K on 'ROAS' / Unchecked : Hide columns I, J, K
• B6 : checked = show columns L, M, N on 'ROAS' / Unchecked : Hide columns L, M, N
• B7 : checked = show columns O, P, Q on 'ROAS' / Unchecked : Hide columns O, P, Q
• B8 : checked = show columns R, S, T on 'ROAS' / Unchecked : Hide columns R, S, T
• B9 : checked = show columns U, V, W on 'ROAS' / Unchecked : Hide columns U, V, W
• B10 : checked = show columns X, Y, Z on 'ROAS' / Unchecked : Hide columns X, Y, Z
• B11 : checked = show columns AA, AB, AC on 'ROAS' / Unchecked : Hide columns AA, AB, AC

I have found a code that works for only one checkbox, but I can't add more checkbox :

function onEdit(e) {
  var spreadsheet = e.source;
  var range = e.range;
  var value = e.value;
  // if sheet name is 'Platforms' and edited cell is 'B4'
  if(spreadsheet.getSheetName() == 'Platforms' && range.getRow() == 4 && range.getColumn() == 2) {
    sheet = spreadsheet.getSheetByName('ROAS');
    if(value == "FALSE") {
      // Hide columns
      sheet.hideColumns(6, 3); // F-H
   
    }
    else {
      sheet.showColumns(6, 3); // F-H
    }
  }
}

CodePudding user response：

In your situation, how about the following modification? In this modification, first, an object for searching the checkbox range and the columns you want to show or hide are created. And, using the object, the columns of "ROAS" sheet are managed.

Modified script 1:

In this modification, only the checkbox of the edited cell is confirmed.

function onEdit(e) {
  const obj = {};
  let r = 4;
  let c = 6;
  for (let i = 1; i <= 8; i  ) {
    obj[`B${r  }`] = [c, 3];
    c  = 3;
  }
  var spreadsheet = e.source;
  var range = e.range;
  var a1Notation = range.getA1Notation();
  if (spreadsheet.getSheetName() == 'Platforms' && Object.keys(obj).includes(a1Notation)) {
    spreadsheet.getSheetByName('ROAS')[range.isChecked() ? "showColumns" : "hideColumns"](...obj[a1Notation]);
  }
}

Modified script 2:

In this modification, the checkboxes of cells of "B4:B11" are confirmed.

function onEdit(e) {
  const obj = {};
  let r = 4;
  let c = 6;
  for (let i = 1; i <= 8; i  ) {
    obj[`B${r  }`] = [c, 3];
    c  = 3;
  }
  var spreadsheet = e.source;
  var range = e.range;
  var a1Notation = range.getA1Notation();
  if (spreadsheet.getSheetName() == 'Platforms' && Object.keys(obj).includes(a1Notation)) {
    range.getSheet().getRange("B4:B11").getValues().forEach(([b], i) => {
      spreadsheet.getSheetByName('ROAS')[b === true ? "showColumns" : "hideColumns"](...obj[`B${i   4}`]);
    });
  }
}

References:

• isChecked()
• forEach()