Script for Checkboxes hidind columns-CodePudding

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

Checkboxes locations:

First Column of Checkboxes

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
B12 : checked = show columns X, Y, Z on 'ROAS' / Unchecked : Hide columns X, Y, Z
B13 : checked = show columns AD, AE, AF on 'ROAS' / Unchecked : Hide columns AD, AE, AF

Second Column of Checkboxes

G2 : checked = show columns B on 'ROAS' / Unchecked : Hide columns B
G3 : checked = show columns C on 'ROAS' / Unchecked : Hide columns C
G4 : checked = show columns D on 'ROAS' / Unchecked : Hide columns D
G5 : checked = show columns E on 'ROAS' / Unchecked : Hide columns E

@Tanaike here already made up this script for the first column of checkboxes, can you please help me adapt it to integrate the second column of checkboxes ?

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

Thanks a lot

CodePudding user response：

For the second column of checkboxes, you can add an additional for loop as well as two other variables (rTwo and cTwo in this example).

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

The second for loop was necessary since the second column of checkboxes had a different number of columns to hide.

I have also tested the script with the setup for Settings sheet shown below:

I got ROAS sheet to show the following: