G Sheets - testing checkBox operation

[rob wilson]

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:

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]

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]

Thanks, I'll try that. Good to know about e!

Rob

[Laurie Nason]

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