Is there a way to stop a COUNTIF function after the value changes from TRUE to FALSE in google spreadsheets?

Question

I have a row of tick boxes and I need to count the most recent successive TRUE values. So it should start counting when the first TRUE appears and stop when it changes to FALSE, ignoring anything that comes after that.

Right now I have a script doing that, but since I have a lot of entries, it takes a long time to run and stops after 6min without finishing everything.

for(var j=3;j<lastRow;j++){
    count = 0;
    for (var k=stupac-1;k>2;k--){
      if (range.getCell(j,k).getValue() == true){
        count++;
      }
      else if ((range.getCell(j,k).isChecked() == false)&&(count>0)){
        break;
      }
    }
    range.getCell(j,stupac).setValue(count);
  }

I thought the best way would be to stop the COUNTIF when the value changes, but have had no luck trying to get that working.

Does this answer your question? https://stackoverflow.com/questions/14450819/google-app-script-timeout-5-minutes — TheMaster, Oct 01 '20 at 16:03
@Calculuswhiz nope, the whole script is a bit longer and doesn't affect this part also, its a WIP, so I forgot to update that one bit, thanks for reminding me — Moomy, Oct 01 '20 at 16:05
@TheMaster not really, I don't want it to run constantly, but I might use something from that solution, thanks — Moomy, Oct 01 '20 at 16:08

Marios · Accepted Answer · 2020-10-01T16:35:52.147

I came up with this solution which seems pretty fast. Your approach iterates over each cell which is not efficient especially when the search space is large. The following approach iterates over the rows in the specified data range.

function myFunction() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1');
  
  const data = sh.getRange('A3:G'+sh.getLastRow()).getValues();
  const counts = [];
  
  data.forEach(row=>{                  
               let t_index = row.indexOf(true);             
               let cutRow = t_index > -1 ? row.slice(t_index, row.length+1) : [];        
               let f_index = cutRow.indexOf(false);
               let ct = f_index > -1 ? f_index : cutRow.length;
               counts.push([ct]);                 
               });           
                
sh.getRange(3,8,counts.length,counts[0].length).setValues(counts);
                             
}

Please adjust the data ranges according to your needs.

The solution here matches the following file:

Please make sure that you have enabled V8 Runtime.

Is there a way to stop a COUNTIF function after the value changes from TRUE to FALSE in google spreadsheets?

1 Answers1