G Sheets - testing checkBox operation

23 views
Skip to first unread message

rob wilson

unread,
Sep 17, 2023, 1:02:45 PM9/17/23
to Google Apps Script Community
I have a G Sheet for adding/editing jobs.
My addJob part of the script works fine - it adds a new row, inserts a checkBox in column 2, and highlights the row (the "state" is stored in a cell).

function addJob() {
setState('ADD');
let sh = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
//data range starts after headerRows
let lastRow = sh.getLastRow();
let lastDataRow = lastRow - headerRows;
if (lastDataRow > dataRows) { //add new row if needed
sh.insertRowAfter(lastRow);
}
//highlight background
sh.getRange(headerRows + lastDataRow + 1, 1, 1, dataCols + headerCols).setBackground(HIGHLIGHT);
//add checkbox
sh.getRange(headerRows + lastDataRow + 1, checkCol, 1, 1).insertCheckboxes();
}
The sheet looks like this:
Screenshot 2023-09-17 17.50.17.png

The user should add some data in the row and then click the checkBox.
My onEdit function should detect and validate this click and then remove the highlight and checkBox:

function onEdit(e) {
/* does not always exit from ADD state when checkbox is ticked - depends on how data is entered */
let state = getState();
let sh = e.range.getSheet();
//verify if this is a checkBox edit
if (sh.getName() == 'Jobs' && e.range.columnStart == checkCol && e.value == "TRUE") {
switch (state) {
case 'ADD':
setCell("case ADD"); //debug
e.range.removeCheckboxes(); //remove check box
sh.getRange(e.range.rowStart, 1, 1, headerCols + dataCols).setBackground(null); //reset highlight
break;
etc

It works as expected if the user enters a single data item and then clicks in another cell, or presses Enter key, and then clicks the checkBox.
But it fails if the user types in a single data item and then clicks the checkbox (no other clicks).
The checkbox is not removed and the highlight is not reset, and the data item is entered.

I don't understand what is happening! And not sure what to look for.
Rob

CBMServices Web

unread,
Sep 17, 2023, 1:33:46 PM9/17/23
to google-apps-sc...@googlegroups.com
Rob,

I would not use this line:

e.range.removeCheckboxes()

Instead, you should use sh.getRange(..).removeCheckboxes();

The e variable is a copy of the spreadsheet data that triggered the spreadsheet. But it is not the Spreadsheet. So you are basically modifying the copy and not original spreadsheet.


--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/9d90c4ff-c656-4bd7-bd6b-16428f817d56n%40googlegroups.com.

rob wilson

unread,
Sep 17, 2023, 2:41:57 PM9/17/23
to Google Apps Script Community
Thanks, I'll try that. Good to know about e!
Rob

Laurie Nason

unread,
Sep 18, 2023, 2:01:57 AM9/18/23
to google-apps-sc...@googlegroups.com
You could probably achieve the removal of the highlight and the checkbox using conditional formatting if you wanted to - make the highlight dependent on the checkbox being false, then when checked make the checkbox white with the white background - it disappears.


--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/9d90c4ff-c656-4bd7-bd6b-16428f817d56n%40googlegroups.com.


--

Laurie


Reply all
Reply to author
Forward
0 new messages