0

I have onEdit script that hides or shows rows by checking/unchecking checkboxes (yes/no values).

Looks like this

if(orpscscchbx.getValue() == 'no') {
   var rowsHided = 0;
         for (var i = 0; i <= numRows - 1; i++) {
         var row = values[i];
         if (row[0] == 'ORP Sc-Sc') {
         sheet.hideRows((parseInt(i)+1) - rowsHided);
         rowsHided++;

         }} }
 else { 
 var rowsshowed = 0;
         for (var i = 0; i <= numRows - 1; i++) {
         var row = values[i];
         if (row[0] == 'ORP Sc-Sc') {
         sheet.showRows((parseInt(i)+1) - rowsshowed);
         rowsshowed++;

  }
  }}

Works fine but problem is that I have around 100 checkboxes and separate IF function for every checkbox/row. So it takes forever to get a row hided/showed after I check/uncheck a checkbox.

Is there a way to optimize it?

0

The script is iteration over something (hard to know what as code line that declares numRows isn't included) and on each iteration a hideRows / showRows methods are executed.

One way to optimize this is to add is to rather than execute them on each iteration is to execute them by sets of continuous rows to hide/show. To do this, you could had a counter of continuous rows to show/hide, then when a the continuity is broken, hide/show the set of rows and restart the counter.

Another alternative is to use the Google Sheets Advanced Service (Google Sheets API) but you will have to learn another syntax.

Related

0

ok, what I did - I separated each checkbox code to separate functions.

and then I put all functions inside onEdit(e) where each one is executed only if a relevant cell with checkbox is edited (with the help of getA1Notation()). In such a way onEdit does not execute all functions in time when one checkbox is edited but only specific function for specific checkbox.

It works very efficiently now

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.