Performance of onEdit to replace deleted checkboxes in long rangelist

Question

The onEdit function below for adding back checkboxes which are accidentally deleted works, but is waaaay too slow (and the list so far only contains half the ranges). The further down the list of ranges the deletion takes place, the longer it takes for the code to 'react' and replace the deleted checkbox (I suppose that is obvious to everybody except myself)

function onEdit(e) {
  var rangeList = SpreadsheetApp.getActive().getSheetByName('Sheet1').getRangeList(['E6:E7','E10:E11','E15:E16','E19:E20','E24:E25','E28:E29','E33:E34','E37:E38','E42:E43','E46:E47','E51:E52','E55:E56','H6:H7','H9:H10','H12:H13','H15:H16','H18:H19','H21:H22','H24:H25','H27:H28','H30:H31','H33:H34','H36:H37','H39:H40','H42:H43','H45:H46','H48:H49','H51:H52','H54:H55','H57:H58',]);
  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];
      var values = range.getValues();
      for ( var val in values ) {
        if( values[val] != 'TRUE' && values[val] != 'FALSE' ) {
          range.insertCheckboxes();
        }
      }
    }
  }
}

Is there anyway to get the job done faster?

Please note: I really dont know what I am doing (the code above was a major headache and I got plenty of help), so please please be explicit. Thank you.

You have a lot of duplication in your script. Start by using variables where it makes sense to do so, like storing the result of `RangeList#getRanges()` or `Range#getValues()`. You would also benefit from understanding what the return type of `Range#getValues()` is, and what it semantically *means*. — tehhowch, Jan 08 '20 at 04:03
Consider also using [`Range#isChecked()`](https://developers.google.com/apps-script/reference/spreadsheet/range#ischecked) to properly determine if a Range is composed of only cells with valid checkboxes. — tehhowch, Jan 08 '20 at 04:26
I am suffering from a 'question ban' due to (I presume) poor question quality. This question has been assigned 3 down-votes which I would like to get rid off. What should I do in this particular case to fix this question? I do not dare delete it since it apparently does not help. — mortpiedra, Mar 27 '21 at 20:37

score 1 · Answer 1 · answered Jan 08 '20 at 05:31

1

How about this:

function onEdit(e) {
  var sh=e.range.getSheet();
  if(sh.getName()!='G')return;
  if(e.range.columnStart==5 && e.value==null) {
    e.range.insertCheckboxes();
  } 
}

This also seems to work:

function onEdit(e) {
  var sh=e.range.getSheet();
  if(sh.getName()!='G')return;
  if(e.range.columnStart==5 && e.value!='TRUE' && e.value!='FALSE') {
    e.range.insertCheckboxes();
  } 
}

answered Jan 08 '20 at 05:31

Cooper

59,616
6
23
54

Thanx Cooper, but I dont understand ur proposal. I mean where are the ranges to be checked? Example: 'E6:E7','E10:E11' needs to be checked for the existance of a checkbox, but 'E8:E9' cannot be checked since they contain something else. Is this considered in your logic? – mortpiedra Jan 08 '20 at 09:42

score 0 · Accepted Answer · answered Jan 08 '20 at 13:01

The best way to go would be, I think, using the event object to avoid looping through all checkbox cells in your sheet, and only check the one that was edited.

Considering that your ranges seem to be of only 2 cells each (e.g. E6:E7), one option would be to first define an array with the A1 notations of all cells that should have checkboxes, and then check if the A1 notation of the edited cell is in that array via indexOf.

For example, if you have E6:E7 and E10:E11 as checkboxes, you could do this:

function onEdit(e) {
  var checkboxCells = ['E6', 'E7', 'E10', 'E11']; // Add other checkboxes accordingly
  var range = e.range;
  var value = range.getValue();
  var a1Notation = range.getA1Notation();
  if (checkboxCells.indexOf(a1Notation) != -1 && value != 'TRUE' && value != 'FALSE') {
    range.insertCheckboxes();     
  }
}

By defining the cells this way, you are not really writing more code than if you define a RangeList instead (and you don't need to loop through the RangeList and then each Range, all of which adds to the execution time).

Also, adding value != 'TRUE' && value != 'FALSE' to the same if statement doesn't slow it down since those are not checked if the first condition is not met.

I hope this is of any help.

Performance of onEdit to replace deleted checkboxes in long rangelist

2 Answers2