mardi 7 janvier 2020

function onEdit with insertCheckboxes and TRUE/FALSE messing with my head

I am struggling with a problem which is really driving me crazy!

I have a complex sheet with plenty of logic and one of the core concepts is checkboxes.

Example: in Z6 there is formula if the checkbox in E6 = true (i.e. checked) then 'X' else 'Y'.

This works fine :-)

Unfortunatly some users by mistake delete the checkbox and hence I need to implement a logic to add it back. I am attempting to implement the logic as suggested here but as onEdit: Prevent deletion of checkbox in sheet editable by many users

Below is the logic I have implemented which took me fucklong time to figure out.

Please note: I am still at the level monkey-see-monkey-do (meaning I dont really know what I'm doing), so the code above is probably shit (hint: suggestions are VERY welcome).

function onEdit(e) 
{
  var rangeList = SpreadsheetApp.getActive().getSheetByName('Sheet1').getRangeList(['E6:E7']);

  for( var i=0; i<rangeList.getRanges().length; i++ ) {
    var range = rangeList.getRanges()[i];

    for( var j=0; j<range.getValues().length; j++ ) {
      var value = range.getValue()[j];

Logger.log("i: " + i); 
Logger.log("j: " + j); 
Logger.log("value: " + value); 

      var values = range.getValues();
      for ( var val in values ) {

Logger.log("values[val]: " + values[val]); 

        if( values[val] != 'TRUE' && values[val] != 'FALSE' ) {
          range.insertCheckboxes('TRUE', 'FALSE');    

Logger.log("arrived at insert checkbox"); 

        }
      }
    }      
  }
}

Note: 'E6:E7' is just the first range and there will about 30 ranges in total at the end.

The logics seems to work fine in that it does what is it supposed to do, BUT there is a nasty side effect which I cannot get my head around.

In its original state (i.e. before the onEdit is executed for the first time) the following formula =WENN(E7=FALSE();"is false";E7) shows 'is false'

BUT once the script has been run the result of the formula is 'FALSE'

Thus, before I activate above onEdit function the formula =WENN(E7=FALSE();"is false";E7) shows 'is false'

Once I activate above onEdit function it does not matter what cell in the sheet I change, the result of the formula switches to 'FALSE'.

Here is the log protocoll for that intial run changing any cell but E6:E7

[20-01-07 15:11:33:557 CET] i: 0  
[20-01-07 15:11:33:558 CET] j: 0  
[20-01-07 15:11:33:558 CET] value: F  
[20-01-07 15:11:33:559 CET] values[val]: FALSE  
[20-01-07 15:11:33:559 CET] values[val]: TRUE  
[20-01-07 15:11:33:560 CET] i: 0  
[20-01-07 15:11:33:560 CET] j: 1  
[20-01-07 15:11:33:560 CET] value: A  
[20-01-07 15:11:33:561 CET] values[val]: FALSE  
[20-01-07 15:11:33:561 CET] values[val]: TRUE  

Similarly, the following formulas for cells H6 and H7 =WENN(H6=FALSE();"is false";H6) shows 'is false' =WENN(H7=FALSE();"is false";H7) shows 'is false'

Here is the log if I perform the action of copying H6:H7 into the cells E6:E7.

[20-01-07 06:01:50:720 PST] i: 0  
[20-01-07 06:01:50:720 PST] j: 0  
[20-01-07 06:01:50:721 PST] value: undefined  
[20-01-07 06:01:50:722 PST] values[val]: false  
[20-01-07 06:01:50:897 PST] arrived at insert checkbox  
[20-01-07 06:01:50:898 PST] values[val]: true  
[20-01-07 06:01:51:041 PST] arrived at insert checkbox  
[20-01-07 06:01:51:168 PST] i: 0  
[20-01-07 06:01:51:168 PST] j: 1  
[20-01-07 06:01:51:169 PST] value: A  
[20-01-07 06:01:51:170 PST] values[val]: FALSE  
[20-01-07 06:01:51:170 PST] values[val]: TRUE  

Again in the end the formula =WENN(E7=FALSE();"is false";E7) shows 'FALSE'

In case it is not crystal clear: what I need is that after a deleted checkbox has been re-created by the onEdit function the formula =WENN(E7=FALSE();"is false";E7) shows 'is false'.




Aucun commentaire:

Enregistrer un commentaire