Google-sheets – More Efficient Filtering Script

google sheetsgoogle-apps-script

I'm exactly one day into writing Google scripts. I've managed to write one that will filter data in a spreadsheet with 24 columns and, for now, about 900 rows.

The problem is, it's slow. Any suggestions for how to make this script more efficient? I should note that the script runs slow without the sort or setActiveSelection commands. 

function open_interventions() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getDataRange();
  var data = range.getValues();
  var numRows = range.getNumRows()-1;


  range.sort([{column: 2, ascending: false}, {column: 1, ascending: true}]);

  for (var i = 2; i <= numRows+1; i++) {
    var checkstatus = sheet.getRange(i, 15).getValue();

    if (checkstatus == "Open") {
      sheet.showRows(i);    
    }
    else {
      sheet.hideRows(i);

  }
  }

  sheet.setActiveSelection("A1");
};

Best Answer

The key approach is to minimize the API calls. Performing those in for loops will slow down the process considerably, especially with 900 rows. I've re-worked your code in such a way, that it calls for the Spreadsheet API only twice:

function open_interventions() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // start logger time  
  var start = new Date();

  // collect data and sort
  var data = sheet.getRange(2, 1, sheet.getLastRow()-1, sheet.getLastColumn())
    .sort([
      {column: 1, ascending: true}, 
      {column: 3, ascending: false},
      {column: 2, ascending: true}])
    .getValues();

  // set counters
  var cFirst = 0, cSecond = 0;

  // itterate to find number of "Open" rows
  for(var i=0, dLen=data.length; i<dLen; i++) {
    if(data[i][0] == 'Open') {
      cFirst++;
    } 
  }

  // itterate through array (backwards) to calculate the rowIndex 
  for(var j=dLen=data.length-1; j>=0; j--) {
    if(data[j][0] != 'Open') {      
      cSecond++;
    } else {
      break;
    }
  }

  // calculate rowIndex
  var rIndex = data.length - (cFirst+cSecond);

  // hide all rows
  sheet.hideRows(2, data.length); 

  // show (unhide) only "Open" rows
  sheet.showRows(rIndex+2, cFirst); 

  sheet.setActiveSelection("D1")
    .setValue("Execution time (ms): " + (new Date() - start).toString());
}

I've added a time tracker thingy to it, so that you can monitor the execution speed. See example file I created: Efficient Sorting. When the file is opened, it will add another menu item called Sorting. There you can play around and see what happens.

Related Topic