Script for Checkboxes hidind columns

Question

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

PatrickdC · Accepted Answer · 2022-04-13 08:18:48Z

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:

Collectives™ on Stack Overflow

Script for Checkboxes hidind columns

First Column of Checkboxes

Second Column of Checkboxes

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

First Column of Checkboxes

Second Column of Checkboxes

1 Answer 1

Comments

Related