I found this google app script to add a checkbox based on a watch column in google sheets.
However I need to add a checkbox in col1, col2 and col4?
Could someone please amend code below to do this, many thanks in advance.
function onEdit(e) {
const watchSheets = /^(Sheet1|Sheet2|Sheet3)$/i;
const watchColumn = 5;
const checkboxColumn = 1;
if (e.range.columnStart !== watchColumn
|| !e.source.getActiveSheet().getName().match(watchSheets)) {
return;
}
const checkboxCell = e.source.getActiveSheet().getRange(e.range.rowStart, checkboxColumn);
if (e.value) {
checkboxCell.insertCheckboxes();
} else {
checkboxCell.clearDataValidations();
checkboxCell.clearContent();
}
}
CodePudding user response:
function onEdit(e) {
const watchSheets = /^(Sheet1|Sheet2|Sheet3)$/i;
const watchColumns = 5;
// changes this to array so you can add multiple columns
const checkboxColumns = [1, 2, 4];
// fixed the first condition here
if (checkboxColumns.indexOf(e.range.columnStart) == -1
|| !e.source.getActiveSheet().getName().match(watchSheets)) {
return;
}
// run through each watchColumns
checkboxColumns.forEach( cellCol => {
const checkboxCell = e.source.getActiveSheet().getRange(e.range.rowStart, cellCol);
if (checkboxCell.getValue()) {
checkboxCell.insertCheckboxes();
} else {
checkboxCell.clearDataValidations();
checkboxCell.clearContent();
}
})
}
CodePudding user response:
Solution:
- Change the
checkboxColumn
to an array with all the column indexes (checkboxColumns
). - Iterate through
checkboxColumns
(for example, using forEach) and, for each column, retrieve the corresponding cell and insert or clear the checkboxes (as in your current code).
Code snippet:
function onEdit(e) {
const watchSheets = /^(Sheet1|Sheet2|Sheet3)$/i;
const watchColumn = 5;
const checkboxColumns = [1, 2, 4];
if (e.range.columnStart === watchColumn && e.source.getActiveSheet().getName().match(watchSheets)) {
checkboxColumns.forEach(checkboxColumn => {
const checkboxCell = e.source.getActiveSheet().getRange(e.range.rowStart, checkboxColumn);
if (e.value) {
checkboxCell.insertCheckboxes();
} else {
checkboxCell.clearDataValidations();
checkboxCell.clearContent();
}
});
}
}