Skip to content

Instantly share code, notes, and snippets.

@brainysmurf
Last active Jun 19, 2021
Embed
What would you like to do?
An `onEdit` trigger that ensures that only one checkbox is selected in a row. Set up once and will work even with changes to name of the sheet, or where it is in terms of rows and columns. Motivated from https://www.benlcollins.com/apps-script/radio-buttons-in-google-sheets/

This onEdit trigger implements the feature as given by Ben Collins.

I was motivated to see if the pattern as given by Ben could be abstracted away so that changes to the sheet name, or changes to the area of the checkboxes changed, and the onEdit trigger wouldn't need to be modified.

Sheet1 tab:

For example, clicking on D1 should change it to checked (X), and uncheck B1.

A B C D E
1 o X o o o
2 o o X o o
3 o o X o o

Settings tab:

A B
1 =Sheet1!A1:E3

This Settings tab just holds a range that resolves to the area where checkboxes are desired. Note that the value of A1 will be an error (#VALUE), but we do not use the value (we just use the formula) so can be ignored.

Plumbing

This works by reading in the settings tab which holds reference to the checklist area. It then turns it into a grid, so we have columnStart, rowStart etc.

Then it builds an array of r1c1 style notation, deletes the one that the user clicked, and calls .uncheck on the resulting range list.

function onEdit(e) {
const ss = e.source;
const targetSheet = ss.getActiveSheet()
const editedSheet = targetSheet.getName();
const value = ss.getSheetByName('Settings').getRange('A1').getFormula();
const [sheet, a1] = value.slice(1).split('!'); // skip '=' and split into sheet name and a1 notation
// make this range a regular grid-like object (so it's easier to output to log)
const range = Object.entries(e.range).reduce (
(acc, [key, value]) => {
acc[key] = value
return acc;
}, {}
);
if (editedSheet === sheet) {
// only applies to this sheet, you can change the name even and will work
// convert to grid
const checkboxRange = ss.getSheetByName(sheet).getRange(a1);
const grid = {
rowStart: checkboxRange.getRow(),
rowEnd: checkboxRange.getLastRow(),
columnStart: checkboxRange.getColumn(),
columnEnd: checkboxRange.getLastColumn()
}
// check that it's within range:
if (range.rowStart >= grid.rowStart
&& range.rowEnd <= grid.rowEnd
&& range.columnStart >= grid.columnStart
&& range.columnEnd <= grid.columnEnd)
{
// make list of r1c1 notation list …
const r1c1s = [];
for (let col=grid.columnStart; col <= grid.columnEnd; col++) {
r1c1s.push(`r${range.rowStart}c${col}`);
}
// … delete the one the user modified …
const index = range.columnStart - grid.columnStart; // finds relative position, and zero indexed
r1c1s.splice(index, 1);
// … and uncheck all
const rangeList = targetSheet.getRangeList(r1c1s);
rangeList.uncheck()
}
}
}
/**
* Use test function for ease in testing; change properties in range to see what happens
*/
const test = () => onEdit({
"user": {
"email": "",
"nickname": ""
},
"value": "9",
"source": SpreadsheetApp.getActiveSpreadsheet(),
"authMode": "LIMITED",
"range": {columnEnd: 3.0, rowEnd: 3.0, columnStart: 3.0, rowStart: 3.0},
"oldValue": "1.0"
});
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment