I found this code written by --Hyde that I understand half of how it works but it works perfectly. It is a trigger to create checkboxes in one cell of column A if one cell of column B is not empty. I am trying to understand it enough to make it dependent on not only one column but three columns (B, C, and D). I'm aware that the 'specs' constant would need to change to add the two other columns and the 'spec' constant would need to follow that. I'm also aware that the 'checkboxCell' constant wouldn't change. I've fiddled around with them without success.
Could someone enlighten me, please?
/**
* Simple trigger that runs each time the user hand edits the spreadsheet.
* Inserts and removes checkboxes depending on whether another column is blank.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
// version 1.1, written by --Hyde, 30 December 2022
// - see https://webapps.stackexchange.com/q/153531/269219
if (!e) throw new Error('Please do not run the onEdit(e) function in the script editor window.');
const specs = [
{ sheet: "sheet_name", column: 2, rowStart: 2, checkboxColumn: 1, },
];
let sheet;
const spec = specs.filter(spec =>
spec.column === e.range.columnStart
&& (sheet = e.range.getSheet()).getName().match(spec.sheet)
)[0];
if (!spec
|| spec.column !== e.range.columnStart
|| spec.rowStart > e.range.rowStart) {
return;
}
const checkboxCell = sheet.getRange(e.range.rowStart, spec.checkboxColumn);
if (e.value) {
checkboxCell.insertCheckboxes();
} else {
checkboxCell.clearDataValidations();
checkboxCell.clearContent();
}
}
Here's the updated code:
/**
* Simple trigger that runs each time the user hand edits the spreadsheet.
* Inserts and removes checkboxes depending on whether another column is blank.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
// version 1.1, written by --Hyde, 30 December 2022
// - see https://webapps.stackexchange.com/q/153531/269219
if (!e) throw new Error('Please do not run the onEdit(e) function in the script editor window.');
const specs = [
{ sheet: "Sheet1", column: 4, rowStart: 2, checkboxColumn: 1, },
];
console.log(specs);
let sheet;
const spec = specs.filter(spec =>
spec.column === e.range.columnStart
&& (sheet = e.range.getSheet()).getName().match(spec.sheet)
)[0];
if (!spec
|| spec.column !== e.range.columnStart
|| spec.rowStart > e.range.rowStart) {
return;
}
const checkboxCell = sheet.getRange(e.range.rowStart, spec.checkboxColumn);
if (e.value) {
checkboxCell.insertCheckboxes();
} else {
checkboxCell.clearDataValidations();
checkboxCell.clearContent();
}
}
The only thing I change here aside from Sheet1
as sheet name, is the column
from 2
to 4
.
Here's the result:
Instead of pushing through the old script, I opted to create this new one instead and ensured that it is dependent on all three columns B, C and D.
function onEdit(e) {
let sheet = e.source.getActiveSheet();
const checkboxCell = sheet.getRange(e.range.rowStart, 1);
let editedRow = sheet.getRange(e.range.rowStart, 2, 1, 3).getValues().flat();
editedRow = editedRow.filter(x => x != '');
if (editedRow.length == 3) {
checkboxCell.insertCheckboxes();
} else {
checkboxCell.clearDataValidations();
checkboxCell.clearContent();
}
}
Result: